Indexes Wizard (Oracle)

Go Up to Oracle Object Wizards

Indexes are optional structures associated with tables. You can create indexes specifically to speed SQL statement execution on a table. When properly used, indexes are the primary means of reducing disk I/O.

Indexes are logically and physically independent of the data in the associated table. Unique Indexes guarantee that no two rows of a table have duplicate values in the columns that define the index.

Note:

• The Index Wizard varies slightly in content based on the version of Oracle to which you are connected.
• To create indexes in your own schema, you need INDEX privileges on the target table. To create indexes in other schema, you need CREATE ANY INDEX privileges.
• You can place a unique key constraint on an Index-Organized table.

Tip:

• Index-organized tables take up less storage space and quickly access table rows. Index-organized tables stores rows in primary key order reducing the amount of storage space needed.
• An advantage of using index-organized tables is that the tables use less memory because key columns are not duplicated in the table and index. The remaining non-key columns are stored in the index structure.

To create a new index using a wizard

1. Open a creation wizard for an index. For details, see Opening an Object Wizard.
2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
   • Properties panel - Indexes (Oracle) - Properties.
   • Columns panel - Indexes (Oracle) - Columns.
   • Storage panel - Indexes (Oracle) - Storage.
   • Partition panel - Indexes (Oracle) - Partition.
   • Definition panel - for details, see Previewing the DDL Generated to Create the New Object.
3. Finally, click Execute to create the object.

Indexes (Oracle) - Properties

When creating or editing an index, this tab/panel lets you work with the following settings:

Setting	Description
Table Owner Table Name	Choose the owner and name of the table where you are creating the index.
Owner Name	Choose the owner and provide a name for the index.
Index Type	NONUNIQUE. In a non-unique index, the ROWID is treated as part of the key. Oracle treats a constraint as deferrable. UNIQUE. Select UNIQUE if the index is a unique constraint. The values in the indexed columns must be distinct. BITMAP. Widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions.
No Sort	Enable this feature if the rows in the table already stored in ascending order. This increases the speed of the index creation process. Oracle does not sort the rows.
Logging	Enabling logs this operation to the redo file.
Reverse	Enabling this feature stores the bytes of the index block in reverse order and excludes the ROWID. The ROWID is a globally unique identifier for a row in a database. It is created at the time the row is inserted into a table, and destroyed when it is removed from a table.
Function-Based	Permits the results of known queries to be returned much more quickly. When you select this option, you are asked for the expression that governs the function-based index you are creating.
Invisible	Oracle 11g and later. Adds an INVISIBLE keyword to the DDL generated to create or edit this index. The optimizer ignores an invisible index unless the OPTIMIZER_USE_INVISIBLE_INDEXES is set to TRUE at the session or system level.
No Parallel Execution	The parallel server query option lets you process queries, using many query server processes, running against multiple CPUs. This option provides substantial performance gains such as reduction of the query completion time. After creation, when you use multiple query servers and you select the ALTER INDEX for NOPARALLEL execution option, the parallel query option remains in place, but parallel processing is removed. If, for example, multiple users on numerous nodes are modifying the same small set of data, the cost of synchronization from the parallel processing may have an unnecessarily large drag on throughput.
Parallel Degree	The value you select indicates the number of query server processes used in the operation.
Parallel Instances	The value you select indicates how you want the parallel query partitioned between the Parallel Servers.

Indexes (Oracle) - Columns

From the Column dropdown, select a column for the index, and then specify a Sort option. To add more columns, click New, and then follow the steps in the previous instruction. Click Delete to drop columns.

Indexes (Oracle) - Storage

When creating or editing an index, this tab/panel lets you work with the following settings:

Setting	Description
Data Block Storage	Select the DEFAULT Tablespace only if you are creating a local partitioned index and want the partitions in the same tablespace as the partitions in the underlying table. Each partition of a local index is associated with one partition of the table. Oracle can then keep the index partitions in synch with table partitions. A transaction entry is needed for each INSERT, UPDATE, DELETE, etc. statement that accesses one or more rows in the block. Transaction entries in many operating systems require approximately 23 bytes. Percent Free identifies how much space you want to allocate for new rows or updates to existing rows. Initial Transactions ensures that a minimum number of concurrent transactions can update an index block, avoiding the overhead of allocating a transaction entry dynamically. Maximum Transactions limits concurrency on an index block.
Extents	An extent is the unit of space allocated to an object whenever the object needs more space. Initial Extent. The initial space extent (in bytes) allocated to the object. Next Extent. The next extent (in bytes) that the object will attempt to allocate when more space for the object is required. Percentage Increase. Lets you type the percentage. Note that you should be careful when setting Percent Increase as it magnifies how an object grows and, therefore, can materially affect available free space in a tablespace. Minimum Extents. For a dictionary-managed tablespace, this is the total number of extents to be allocated when the index is first created. For a locally-managed tablespace, this is simply the initial amount of space allocated. Maximum Extents. For a dictionary-managed tablespace, this is the total number of extents that can ever be allocated to the index. In a locally-managed tablespace, the database automatically manages the extents.
Freelists	Free lists. Let you manage the allocation of data blocks when concurrent processes are issued against the index. You can potentially improve the performance of the index by identifying multiple free lists, which can reduce contention for free lists when concurrent inserts take place. The default and minimum value is 1. Increase this number if multiple processes access the same data block. Free List Groups. Parallel server option only. The number of groups of free lists.
Buffer Pool	DEFAULT. Choose this if you want to use the default bufferpool. KEEP. Use this to retain the object in memory to avoid I/O conflicts. This type of bufferpool stores frequently referenced data blocks in a separate cache. RECYCLE. Select this option to save cache space by ridding data blocks from memory as soon as they are no longer in use.

Indexes (Oracle) - Partition

Clicking Create Partition opens a wizard that lets you create a partition.