Re: Oracle Index Question

On Jan 15, 2:49 pm, Jimbo <jamesfer..._at_hotmail.com> wrote:
> I work primarily in SQL Server and have run across a situatiion in
> Oracle that's troubling me....
>
> I have a query that contains a where clause with 2 restrictions.  Both
> these fields are in a composite index of 3 fields.  If I add the third
> field to the restriction the optimizer uses the index, however if i
> keep it with just the 2 fields it does a table scan and takes forever
> unless I put in an index hint to use the composite index.  Is this
> right?..is this a dba issue?

It could be a problem with how up-to-date the statistics are for the table and its indexes. Or it could be something else.

Very brief, random thoughts worded very loosely: Oracle determines the access path, whether to use a specific index or not, based on cost calculations. Cost calculations are the optimizer's best guess as to the amount of time a particular access path will require - the lowest cost access path wins. Creating an index on three columns, when only two are need by the majority of SQL statements potentially (and unnecessarily) increases the clustering factor calculation for the index, which describes to Oracle how ordered the rows in the table are in comparision to the index. The clustering factor is used in cost calculations. When all three columns in the index are referenced, the index access path appears to Oracle to be more selective, meaning that it should return fewer rows than it would if only two columns of the index were specified.

If the table and index statistics are not up to date, Oracle may believe, for instance, that there are only 100 rows in the table, and that the selectivity is indicating that 1% of the rows will be retrieved. If Oracle believes that the average row length is 80 bytes, a full table scan may only require Oracle to read one 8KB block, while an index lookup may require 3 or 4 8KB block reads per row to be retrieved. In such a case, Oracle would determine that a full table scan is less expensive than an index based access path. What if this table actually contains 10,000,000 rows, rather than the 100 rows indicated by the statistics? Oracle calculates the cost of the access paths too low, and may pick an inefficient/wrong access path.

Oracle initialization parameters can also control the cost of an index based access path as compared to a full table scan access path. Some of these initialization parameters change from one Oracle version to the next, so the exact Oracle version may be very important. If you really want to find the answer, pick up a copy of "Cost-Based Oracle Fundamentals" and start digging through a 10053 trace file.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jan 15 2008 - 15:06:50 CST