Tuning Index Usage - Expert Indexing in Oracle Database 11g - page 163

Collecting Accurate Statistics for an Index
Gathering accurate statistics is probably the single biggest factor that influences the optimizer's
selection of an index scan. You collect optimizer statistics for an index just as you do for a table. Note
that unlike in the case of a table, Oracle Database automatically collects an index's statistics when you
create the index, as shown here:
SQL> create index test_idx on mytab2(employee_id,first_name);
Index created.
SQL> select index_name,last_analyzed, num_rows, sample_size from user_indexes
where table_name='MYTAB2';
INDEX_NAME LAST_ANAL NUM_ROWS SAMPLE_SIZE
-------------------- ------------- --------- ------------------
TEST_IDX 20-AUG-11 107 107
SQL>
When dealing with optimizer statistics you must ensure that the scheduling of your statistics
gathering jobs is in tune with the amount of changes in a table's data. The frequency of statistics
gathering should depend on the frequency and amount of changes in your data. When you collect
optimizer statistics for a table with the DBMS_STATS procedure GATHER_TABLE_STATS, the cascade
parameter determines if the database gathers statistics for the indexes as well. The default value for this
parameter is the constant DBMS_STATS.AUTO_CASCADE, which means that the database will
determine if it should collect index statistics when it collects the table's statistics. Specify cascade=>true
to ensure that the database collects statistics on the indexes along with the table statistics or change the
value of the constant AUTO_CASCADE with the help of the SET_PARAM procedure.
You can also gather index statistics by themselves without collecting the table statistics by executing
the DBMS_STATS.GATHER_INDEX_STATS procedure, as shown here:
SQL> execute dbms_stats.gather_index_stats(USER,'EMPLOYEES_IDX1',
estimate_percent=>100, degree=>12);
PL/SQL procedure successfully completed.
SQL>
You can get index-related optimizer statistics information by querying the
dbms_stats.get_index_stats procedure. You can also set index-related information by using the
SET_INDEX _STATS procedure from the DBMS_STATS package.
Parallelizing Index Access
When dealing with partitioned indexes, you can direct the optimizer to use multiple concurrent parallel
servers to parallelize several types of index operations. The operations you can parallelize include index
range scans, full index scans, and fast full scans.