Re: index unused

> An index is NOT a magic "go faster bullet".
>
> With no WHERE clause you are asking Oracle
> to return every row; which means a FTS is
> the ONLY way to satisfy your query. The use
> of an index would make the query run SLOWER,
> because it would 1st have to read the index
> and then go read tha data for EVERY row! It
> doubles the number of I/O operations in this
> case.
>
> An index can get the results faster than
> a FTS if & when only a specific subset of
> rows is being returned AND the WHERE clause
> can actually use whole column(s) in an index.
>
> P.S.
> the use of indexes is NOT restricted to XML.
> Go read the Oracle Concepts Manual at
> http://tahiti.oracle.com

Ana,

   I agree with what you say, but I'd like to add that there are situations where an index can be used, even if there is no WHERE clause at all:

• If all the columns to be fetched are part of the index, the index can be used if the CBO sees that less logical reads will be necessary than with a FTS. That can be also true even if the columns requested reside in multiple indexes (for more details, look at the doc for the hints AND_EQUAL and INDEX_COMBINE).
• An index FFS can replace a FTS for operations such as "select count(*) from mytab".

Daniel Received on Mon Jan 12 2004 - 10:39:44 CST