order of index columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: order of index columns

  1. #1
    Join Date
    Jun 2000
    Posts
    295

    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?

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    put he most selektiv column first

    city, country, state

    Rulbased Optimzer always will take the index

    Orca

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 06: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

  5. #5
    Join Date
    Jun 2000
    Posts
    295
    Can anyone explain what "key compression" is?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  



Click Here to Expand Forum to Full Width