RE: Index Full Scan -- Strange Issue

> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonathan
> Lewis
> Sent: Thursday, March 14, 2002 5:26 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Index Full Scan -- Strange Issue
>
> This leads to another random thought - if there is
> a condition in the WHERE clause, which covers
> just one column in the index, though not the leading
> column, is it possible that 8.1.7 has some early
> code relating to the path 'index skip scan' - which
> nevertheless gets reported as 'index full scan' ?

Good question, but this wasn't the nature of the query. Very similar, but makes perfect sense and we see all the time, is for the optimizer to consider index access when (1) only the trailing column of a concatenated index is specified in the criteria (or no criteria at all, or, function/operation on the column), and, (2) the query can be resolved in the index without requiring accessing the table.

Ok, I know, a bit obvious. This does tie back, though, into the myths discussion. For example, one might be tempted to say "an index cannot be used if the leading column is not specified". Or even more so someone might make a statement slightly different "if only the trailing column is specified, an index will not be used". We all know that it is "generally" true in 8i and below *if* the query cannot be resolved using only the index. But people will often times leave that point out.

And as you pointed out, the concept of index skip scans in 9i changes all of that. And I'm sure you and others (I know I have) have had situations in 8i or below where the query couldn't be resolved using just an index(s), and we specified just the trailing column, or a "like '%ABCDE%' on a single column index, but we forced the use of an index anyway since it was more efficient (reported as range scan in the plan but we know it is full scan since it is unbounded).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).