-
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?
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
Hi,
put he most selektiv column first
city, country, state
Rulbased Optimzer always will take the index
Orca
-
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.
Last edited by DaPi; 02-13-2003 at 07:29 AM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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 . . .
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|