Re: sql query not use index

On Mar 9, 1:11 pm, sydneypue..._at_yahoo.com wrote:
> Hello Guys,
>
> I was asked yesterday a project manager what sort of query will not
> use an index?
> What sort of index ? I asked
> say a varchar(256) ?
> A badly written regex. I responded.
> OK thanks.
>

Also any query involving a function call using the indexed column:

UPPER(colname)
LOWER(colname)
LTRIM(colname...)
RTRIM(colname...)

ALSO a query against a nullable column which is not qualified by a where clause since B-Tree indexes don't index NULLs:

http://oratips-ddf.blogspot.com/2008/04/tale-of-two-indexes.html

ALSO a query, with a WHERE clause, returning a large part of the table data, as the optimizer may decide a full table scan is more efficient (less work) than an index scan/table fetch by rowid for that much data:

http://oratips-ddf.blogspot.com/2007/06/table-scans-histograms-and-scarlett.html

> Now I am worried I have given a misleading or wrong response!

Incomplete, possibly, but not wrong.

> Can anyone help me out with a better answer?
>
> TIA
>
> Syd

David Fitzjarrell Received on Mon Mar 09 2009 - 14:49:44 CDT