Re: how to create an segmented index

Why not create an index on column a and a different index on column b? Or an index on (a,b) and an index on b? If you choose to create an index on (a,b) and an index on (b,a) the optimizer might be confused, but that's what the hints are for.

Hope that helps,
Brian Peasland
Raytheon Systems
peasland_at_edcmail.cr.usgs.gov

Uwe Hoell wrote:

> I have two statements:
>
> a: select * from myTable where colA=valA and colB=valB
>
> b: select * from myTable
> where colB=valB
> order by colA desc, colB desc
>
> colA is the most selective (every valA is a different one).
> colB hase only 2 - 3 different values, each with a equal count.
>
> I've learned in an Oracleperformance-course, that the first column
> should be the
> most selective - so in my example the index should be (a, b).
> This is good for statements like a:.
>
> But since myTable is quite big - we encounted performanceproblems with
> select b:.
> It seems, that in this case Oracle hast to scan all rows in the index
> sequentially.
> This is a problem, if for example the valuerange for
>
> colA and colB=valB1 is from 1.000.000 up to 500.000 and for
> colA and colB=valB2 is from 500.000
>
> In this case it should be better, if the index was created like (b, a),
> so Oracle can
> directly 'start' at the right valB in the index.
>
> What to do?
> create two indexes (a, b) and (b, a) - this could be a problem for the
> rulebased optimizer:
> he does'nt know whitch is the best for statement a:
>
> or:
> create only index (b, a)
> and change the order in statement b:
>
> and is the rule: first segment = most selective segment not allways the
> best?
>
> thanx!
Received on Fri Aug 20 1999 - 07:58:58 CDT