Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index not being used
This is a function of the information made available to ORACLE thru "analyze"
and some assumptions it needs to make.
First of all, ORACLE does not know anything about the distribution of data
values.
When you analyze the table, there is an column in dba_tab_columns for this
table called "num_distinct". The cost-based optimizer, in deciding whether to
use the index or do a full table scan needs to estimate what percent of the
rows it expects to satisify its query (i.e.CLT_TYPE= 'HAC').
It knows the number of rows for this table (dba_tables.num_rows and number of
distinct entries for this column: dba_tab_columns.num_distinct).
It estimates the percent of records satisfying this query by computing the
ratio: 1/num_distinct.
If the percent of projected records would be too high, then the full table scan
would be used. High ratios have been said to be 20%
(i.e. 1/5 but this is not a hard and fast rule).
If a full table scan is being done, check this value and compute the percent.
You happen to have more information than ORACLE has in that you expect to return 9 rows out of 1/2 million which is a very low percent.
If you want ORACLE to have this information available to the cost-based
optimizer, then you must use histograms
(analyze table $table compute statistics
for columns CLT_TYPE).
See histograms or dba_histograms in the docs Received on Thu Nov 19 1998 - 00:16:32 CST