Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Location
    Karlsruhe, Germany
    Posts
    1

    Lightbulb Unanswered: CREATE INDEX on some FIELDS

    Hi all!
    I have an index on 3 fields i na table.
    dbtable_ora.sql
    ....
    CONSTRAINT tbl_entry_u UNIQUE ( GIP, Username, Logout)
    USING INDEX
    /*SEGMENT SPACE MANAGEMENT AUTO*/
    STORAGE (INITIAL 1500K NEXT 500K)
    ...
    So I wonder, when Oracle performs a search on only one from these fields, say GIP, does Oracle use the Index?
    Or should I create index for each field, that will be searched very frequently.


    cheers
    Dian

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    The order of the columns is important and depends on the selectivity of each column as well as how frequently you ask for one or two in particular : highly selective columns should be put first in general, as well as frequently asked columns. In general, a search on one or more of the first columns will be fast and the optimizer is very likely to use the index. On the contrary, a search on the last column, if you have several columns, will not be as quick because the index is not well sorted in respect to this particular column (it is sorted first on the first column, then the second, then...). So if you generally search on all columns of the index or on the first columns, you should not need another index on one of these columns. However, if you frequently ask questions on the last column of your index, then either consider to change the order or consider creating an index on that particular column, if it is a good candidate for an index (for a b-tree index, it should be a highly selective column, in general selectivity <= 5%, if your selectivity is lower, then you can consider a bitmap index or no index at all... it depends on your system, each situation is unique and require thought and benchmarking to know which solution is the best).

    HTH & Regards,

    RBARAER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •