order of index columns

Hi,

I have a table
test (
country varchar2(32),
state varchar2(32),
city varchar2(32),
...
)

Assuming that:
1. 50% rows of test have country value 'USA' while
another 50% are all other countries.
2. My query is something like:
select * from test
where country='USA' and
state='New York' and
city='New York'

I thought I_TEST_2 on TEST(city, state, country) is better than
I_Test_1 on TEST(country, state, city)

But the explain plan shows no difference - query will use
either index if exists.

Can anyone explain why my thought is wrong?

You may try creating histogram on COUNTRY.
How about creating index on CITY only? Have you tried it?

Hi,
put he most selektiv column first

city, country, state

Rulbased Optimzer always will take the index

Orca
:cool:

Hi,

If you will ALWAYS specify the three columns like:

Code:

---

where country='USA' and
state='New York' and
city='New York'

---

then it will make practically no difference which order the columns are in the index.

HOWEVER, in versions before 9, if your condition sometimes is only on some subset of columns like:

Code:

---

where city='New York'

---

only the TEST(city, state, country) index can be used - i.e. the one where you are selecting on the first column in the index.

Providing either order allows the index to be used, Tom Kyte argues for putting the LEAST discriminating column first as it allows efficient key compression and thus smaller indexes. Not sure I would bother about that! I would put first the column most likely to be specified without any others in a condition.

Can anyone explain what "key compression" is?

It's in the doc . . . never used it . . .
Invoked by "COMPRESS n" in CREATE INDEX, multi-column keys like
"AAAAA,BBBBB,CCCCC", "AAAAA,BBBBB,DDDDD", "AAAAA,BBBBB,EEEEE"
are stored like:
"AAAAA,BBBBB,CCCCC", ",,DDDDD", ",,EEEEE"
I've no idea of the value of the space/performance trade off . . .