From: Sybrand Bakker Newsgroups: comp.databases.oracle.server Subject: Re: Optimzer use for an index Date: Fri, 20 Apr 2001 18:29:01 +0200 Message-ID: Reply-To: postbus@sybrandb.demon-verwijderdit.nl References: X-Newsreader: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@nl.demon.net NNTP-Posting-Host: sybrandb.demon.nl X-NNTP-Posting-Host: sybrandb.demon.nl:212.238.21.78 X-Trace: beta-news.demon.nl 987784013 beta-news:41386 NO-IDENT sybrandb.demon.nl:212.238.21.78 Lines: 45 On Fri, 20 Apr 2001 11:34:42 -0400, "Syltrem" wrote: >Hi! > >I have a table with an index on 3 fields A, B and C > >I have a SELECT ... WHERE A=1 and B=2 and Z=0); > >I would expect Oracle to use the index, regarding only for matches on fields >A and B (and then checking if Z=0 among those) but it does not. > >Now if I create a second index with only fields A and B, then Oracle does >use the new index and the query runs in 2 seconds instead of 55. > >WHY does Oracle not use the first index? I consider it is suitable to >achieve the same result we get with a second index on fields A and B only >(in other languages, it is). I don't get it. And I don't see why I should >create 2 indexes on the same fields (more overhead on row creation). > >Thanks! Please post more background - the optimizer you are using - the *full* index definition (it is important to know whether it's unique or not) - execution plans. If you are using CBO the answer would probably be: - right now, according to CBO it would take less I/O to perform a full table scan - as the second index is smaller the situation is probably reversed - the first index is not unique and finally - try creating histograms on affected columns BTW : if you have three columns on an index and all three columns in the where clause, Oracle will always use *all* three columns. The scenario you describe above will never apply. Hth, Sybrand Bakker, Oracle DBA