Name

ALTER INDEX

Synopsis

ALTER INDEX
[schema.]index_name
   [PCTFREE  integer]
   [INITRANS integer]
   [MAXTRANS integer]
[PARALLEL ( {DEGREE {integer | DEFAULT} |
INSTANCES {integer | DEFAULT} } ) ]
   [NOPARALLEL]
   [LOGGING | NOLOGGING ]
   [UNUSABLE]
   [STORAGE (
      [NEXT integer[K | M] ]
      [MAXEXTENTS {integer | UNLIMITED} ]
      [PCTINCREASE integer]
      [FREELISTS integer]
      [FREELIST GROUPS integer]
      ) ]
   [ALLOCATE EXTENT
(SIZE integer[K | M] [DATAFILE
'filename'] [INSTANCE
integer])]
[DEALLOCATE UNUSED [KEEP integer[K | M]]
   [UNUSABLE]
   [DROP PARTITION partition_name]
[SPLIT PARTITION partition AT
(valuelist) INTO
(newpartition,newpartition)]
[RENAME PARTITION partition_name TO
newpartition]
   [RENAME TO newindex_name]

Changes characteristics of an index (index_name).

Keywords

PCTFREE

Specifies the amount of free space to leave in each data block for later updates and inserts.

INITRANS

Changes the initial number of transaction entries allocated to each block of the index.

MAXTRANS

Changes the maximum number of transaction entries allocated to each block of the index.

PARALLEL

Specifies the level of parallelism to be supported, based on the following parameters:

DEGREE

Specifies the degree of parallelism. An integer value specifies how many slave processes can be used. Specify DEFAULT to use the default value for the tablespace.

INSTANCES

Specifies the number of instances that can be used to execute slave processes. Specify DEFAULT to use the default value specified for the tablespace.

NOPARALLEL

Specifies that no parallelism is to be used.

LOGGING

Specifies that the operation is to be logged to the redo logs.

NOLOGGING

Specifies that nothing is to be written to the redo logs for this operation.

UNUSABLE

Specifies that the index is to be marked unusable.

STORAGE

Specifies the physical characteristics of the index as follows:

NEXT

Specifies the size of the next extent in bytes, kilobytes, or megabytes. If the value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.

MAXEXTENTS

Specifies the maximum number of extents that may be allocated for this index. The default will vary according to the database blocksize. Specify UNLIMITED for unlimited expansion.

PCTINCREASE

Specifies the percentage by which each extent will grow over the previous extent. The default is 50, which means that each extent will be one-and-one-half times larger than the previous extent.

FREELISTS

Specifies the number of free lists contained in each freelist group in this index. The default is 1 and the maximum depends on the database blocksize.

FREELIST GROUPS

Specifies the number of groups of free lists for this index. The default is 1. This parameter should be used only with the Parallel Server option running in parallel mode.

ALLOCATE EXTENT

Forces the immediate allocation of the next extent.

SIZE

Specifies the size of the new extent in bytes, kilobytes, or megabytes.

DATAFILE

Specifies the name of the operating system datafile, in the tablespace that holds this index, to hold the new extent. If the name is omitted, Oracle will select a datafile.

INSTANCE

Makes the new extent available to the specified instance, which is identified by the initialization parameter INSTANCE_NUMBER. This parameter can only be used when running in parallel mode.

DEALLOCATE UNUSED

Releases storage above the highwater mark.

KEEP

Specifies the amount of storage above the highwater mark to keep.

DROP PARTITION

Specifies that the specified partition is to be dropped.

SPLIT PARTITION

Specifies that the partition is to be split into two new partitions. AT specifies the new, noninclusive upper bound for the first new split partition. INTO specifies the names of the two new partitions into which partition is to be split.

RENAME PARTITION

Specifies that the partition is to be RENAMEd TO newpartition.

RENAME TO

Specifies that the index index_name is to be RENAMEd TO newindex_name.

Notes

The index to be altered must be in your schema, or you must have the ALTER ANY INDEX privilege to issue this command.

If storage options are omitted, Oracle will allocate storage for the index as follows:

  • If the indexed table has no rows, the default storage values for the tablespace will be used.

  • If the indexed table has rows and the resulting index can be contained in no more than 25 data blocks, a single extent will be allocated for this index.

  • If the indexed table has rows and the resulting index is more than 25 data blocks, 5 equal-size extents will be allocated for this index.

Example

The following example alters the index named employee owned by scott so that new extents added for this index are 4K each and will not grow, and each data block added to this index contains 5 initial transaction entries:

ALTER INDEX scott.employee
            INITRANS 5
            STORAGE (NEXT 4096 PCTINCREASE 0);
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset