Administration Workshop II. 4-14. re-creating indexes

Use options to reduce the time it takes to create the index:

  • PARALLEL
  • NOLOGGING

SQL> CREATE INDEX rname_idx ON hr.regions (region_name) PARALLEL 4;

WARNING: if you created the index with PARALLEL option 4, then you’ll need to change it back to PARALLEL 1 after the index is created so that it doesn’t use 4 CPUs every time it access the index later!

May 15, 2008. catcha, index. Leave a comment.

Function-based index

A function-based index indexes a funciton’s return value.
It can be a:

  • Built-in SQL function
  • PL/SQL function
  • A user-written function

It relieves the server from having to invoke the function for every key value as it performs a search on the indxed expression.

For example:

CREATE INDEX table1_ix ON TABLE1(my_function(column1, column2));

Now any query that contains the expression my_function(column1, column2) in the WHERE clause may be able to take advantage of this index (otherwise, it would force a full table scan)

March 31, 2008. index, performance. Leave a comment.

Reversed-key indexes

From: Oracle reverse key index tips

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table). For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.

Note: An Oracle reverse key index DOES NOT change the functionality of the index, and it’s not the same as reversing the index key values.

In general, an Oracle reverse key index relieve data block contention (buffer busy waits) when inserting into any index where the index key is a monotonically increasing value which must be duplicated in the higher-level index nodes.

With the index key reversal, only the lowest-level index node is changed, and not all of the high-order index nodes, resulting in far faster insert speed. For updates, Oracle updates the index nodes with each update statement.

Depending on the size of your update batch, it’s often faster to drop, update and then re-build the index. If you have more than one CPU, you might consider doing a parallel index rebuild for faster speed. It splits-up the full-table scan.

March 31, 2008. index. Leave a comment.

7-29. B-Tree index leaf entry characteristics

In a B-tree index on a nonpartitioned table:

  • Key value are repeated if there are multiple rows that have the same key value unless the index is compressed
  • There is no index entry corresponding to a row that has all key columns that are NULL. Therefore, a WHERE clause specifying NULL will always result in a full table scan.
  • Restricted ROWID is used to point to the rows of the table because all rows belong to the same segment.

March 31, 2008. index, performance. Leave a comment.