Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Rulebased optimizer problem

Re: Rulebased optimizer problem

From: Roger Snowden <snowden_at_NOT.com>
Date: Tue, 12 Jan 1999 04:31:43 GMT
Message-ID: <369ACF3E.2080AD5D@NOT.com>


Actually, the selection for equal-weighted predicates is based on the order of the object within the dictionary block where they are stored. Object id is not the issue. The server finds a valid object and stops searching right then.

Roger Snowden
Sr. Systems Engineering Specialist
Oracle Corporation
rsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious

JC wrote:
>
> I obviously have too much time on my hands. I created the indexes starting
> with testi3 through testi1 and verified that testi3 had the lowest object
> number. The execution plan still chose testi2. There must be something
> else going on...
>
> Peter Sharman wrote in message <36963ACA.394F7DC1_at_us.oracle.com>...
> >Uwe
> >
> >Believe it or not, it's caused by the order in which the indexes were
> >created. In a situation like this, where multiple indexes can be used, the
> >RBO (not sure about CBO) would choose the index with the lowest object_id
> >from dba_objects (in other words, the first one created). To prove this,
> >create them in the reverse order and you should see testi3 being used.
> >
> >Hope this helps
> >
> >Pete
> >
> >Uwe Hoell wrote:
> >
> >> Hello,
> >>
> >> I have a question about the optimizer
> >> (NT-Workgroupserver ver 7.3.3.4.0):
> >>
> >> create table test (a int, b int, c int, d int, f int)
> >>
> >> create index testi1 on test (a)
> >> create index testi2 on test (a, b)
> >> create index testi3 on test (a, b, c, f)
> >>
> >> select * from test where a=1 and b=2 and c=3 and d=4
> >>
> >> This select uses index testi2:
> >>
> >> Execution Plan
> >> ----------------------------------------------------------
> >> 0 SELECT STATEMENT Optimizer=RULE
> >> 1 0 TABLE ACCESS (BY ROWID) OF 'TEST'
> >> 2 1 INDEX (RANGE SCAN) OF 'TESTI2' (NON-UNIQUE)
> >>
> >> But why not index testi3 ?
> >>
> >> We have a similar problem in a large system, where are defined two
> >> indexes like testi2 and testi3. If we use an indexhint to force the
> >> optimizer to use indexi3 the select is much faster.
> >> Is this the right news-group to post such questions or does anybody know
> >> a beter one?
> >>
> >> Thans!
> >
> >--
> >
> >
> >Regards
> >
> >Pete
> >
> >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >Peter Sharman Email: psharman_at_au.oracle.com
> >WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
> >Worldwide Internal Services Education (650)607 0109 (local)
> >San Francisco
> >
> >"Controlling application developers is like herding cats."
> >Kevin Loney, ORACLE DBA Handbook
> >"Oh no it's not! It's much harder than that!"
> >Bruce Pihlamae, long term ORACLE DBA
> >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> >
Received on Mon Jan 11 1999 - 22:31:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US