Indexes Wizard (Oracle)

From DBArtisan
Jump to: navigation, search

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.
Note: 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.
Note: 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.
Tip: 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:
  3. Finally, use the Execute button 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 and Table Name

Choose the owner and name of the table in which the index is being created.

Owner and Name

Choose the owner and provide the name of the index being created.

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 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)

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, ALTER INDEX for NOPARALLEL execution - when you use multiple query servers and you select this option, the parallel query option remains in place, but parallel processing will be 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 that should be 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 specify a Sort option. To add more columns, click the New button and then follow the steps in the last instruction. Use the Delete button 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 group

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 group

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: You should be careful when setting Percent Increase because 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 will automatically manage the extents.

Freelists group

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. You should increase this number if multiple processes access the same data block. Free List Groups is the number of groups of free lists. NOTE: This option is only applicable for the parallel server option.

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.

Retrieved from "http://docwiki.embarcadero.com/DBArtisan/170/e/index.php?title=Indexes_Wizard_(Oracle)&oldid=3096"