Xref: alice comp.databases.oracle.server:75622
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!nntp2.deja.com!nnrp1.deja.com!not-for-mail
From: karsten_schmidt8891@my-deja.com
Newsgroups: comp.databases.oracle.server
Subject: Re: The LIKE Clause and the optimizer!
Date: Fri, 26 Nov 1999 10:45:17 GMT
Organization: Deja.com - Before you buy.
Lines: 30
Message-ID: <81lobs$4lk$1@nnrp1.deja.com>
References: <81iajg$27n$1@perki.connect.com.au> <81j5qb$5ur$1@ctb-nnrp1.saix.net>
X-Article-Creation-Date: Fri Nov 26 10:45:17 1999 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 4.01; Windows NT)
X-Http-Proxy: 1.0 proxy.damp.de:8080 (Squid/2.0.PATCH2), 1.0 x32.deja.com:80 (Squid/1.1.22) for client unknown, 194.163.28.186
X-MyDeja-Info: XMYDJUIDkarsten_schmidt8891

Hi,

 is the optimiser smart enough to use reverse-key indexes for
 that kind of query ?
 I personally doubt it, but theoretically, it could - right ?

Karsten

In article <81j5qb$5ur$1@ctb-nnrp1.saix.net>,
  "Billy Verreynne" <vslabs@onwe.co.za> wrote:
> George wrote in message <81iajg$27n$1@perki.connect.com.au>...
> >I have a query:
> >    SELECT /* + INDEX(invoices inv_num) */
> >    FROM  invoices
> >    where inv_num LIKE '%xxxx'
> >
> >inv_num is of VARCHAR2(10) type and has an index on it.
> >
> >I am aware that Oracle will choose to do a FULL TABLE SCAN because
> >the character string contains a '%' as the first letter, however I
find it
> >hard to believe that
> >Oracle would rather use a full table scan rather than use the index
in this
> >case.
>


Sent via Deja.com http://www.deja.com/
Before you buy.