Re: sql query not use index

From: <sydneypuente_at_yahoo.com>
Date: Thu, 12 Mar 2009 03:52:33 -0700 (PDT)
Message-ID: <a3430295-d991-4742-a1b1-5ca66953ff11_at_w34g2000yqm.googlegroups.com>

On 9 Mar, 19:49, ddf <orat..._at_msn.com> wrote:
> Comments embedded.
>
> 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...)
> etc.
>
> 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-sc...
>
> > 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

Thanks for that. I can see that a UPPER(colname) could not use an index cos UPPER changes the data.
Out of interest would a function REGEXP_LIKE use an index? used like so:

SELECT zip
  FROM zipcode
 WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP Received on Thu Mar 12 2009 - 05:52:33 CDT