Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Unanswered: Index Optmization

    Hi,

    Does anybody know any documentation or tips on how to choose indexes in an optimal way.

    Thanks
    Sateesh.

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Question

    What do you mean, "choose indexes in an optimal way?"
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    Guru,

    What I mean is what is the best criteria to make a column as an Index.

    Thanks
    Sateesh.

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi,
    Generally, column(s) to be indexed is (are) the one that appear in the WHERE clause of the SQL statements. There are different types of indexes that suits to different environment (OLTP OR DSS) and different appplications.
    In some cases, one may create index on the foreign key columns to minimise locking.

    For more Informations, please read the Oracle documents.

    http://otn.oracle.com/pls/db92/db92....mark=Verb+hint
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    The best performance improvement can be seen when a column containing all unique values has an index created on it, similar performance improvements can be made on columns containing some duplicate values or NULLS also.

    The usage of indexes for searching tables for information can provide incredible performance gains over searching tables using columns that are not indexed. However, care must be taken to choose the right index. Although a completely unique column is preferable for indexing using the B-tree structured index, a nonunique column will work almost as well if only about 10 percent of its rows have the same value. "Switch" or "flag" columns, such as ones for storing the sex of a person, are a bad idea for B-tree indexes. So are columns used to store a few "valid values," or columns that store a token value representing valid or invalid, active or inactive, yes or no, or any types of values such as these. Bitmap indexes are more appropriate for these types of columns
    SATHISH .

  6. #6
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    Satish,

    I have some foriegn keys which are also indexes, but these columns have many duplicate values, but are used in where clauses. So what do you suggest me about this type of columns.

    Thanks

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    To keep from "locking" and scanning child tables, Foreign keys should be indexed... If you know how the child tables are being accessed, you can alway add additional columns in the index. You have to KNOW your data and how it is going to be accessed ...

    Indexes are GREAT for retreiving data, but hinder performance in DML statements ... You have to find the right mix of indexes that doesn't bottleneck you INSERTS, UPDATES, DELETE statements...

    I believe (maybe off somewhat on the #) that if an index returns more than 12% of the data, the Optimizer will choose to scan the table as opposed to the index.

    Keep stats updated on the indexes and periodically check for "index browning" and extends...

    HTH
    Gregg

  8. #8
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    Thanks Gregg, that gives me some idea now.

  9. #9
    Join Date
    Jan 2004
    Posts
    5

    Re:

    Originally posted by n_sateesh
    Thanks Gregg, that gives me some idea now.
    Maybe the short answer is, if you sort, select, or join on a field, it might be worth creating an index for it. The more you use it for such things, the more value the index will have.

    Jim

  10. #10
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    I hope gbrabham's answer would have solved Your problem.
    SATHISH .

Posting Permissions

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