Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index not being used

Re: Index not being used

From: Mlevison1 <mlevison1_at_aol.com>
Date: 19 Nov 1998 06:16:32 GMT
Message-ID: <19981119011632.11437.00003452@ng95.aol.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US