how to create an segmented index

I have two statements:

1. select * from myTable where colA=valA and colB=valB
2. 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 Wed Aug 18 1999 - 15:40:13 CDT