DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: recommended practice for creating custom indexes on oracle tables

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    recommended practice for creating custom indexes on oracle tables

    Hi Friends,

    Can somebody suggest recommended practice for creating custom indexes on oracle tables ??

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    You mean indexes that are not part of PK or Unique keys .. Or you want to create your own bit map indexes ?? or creating indexes based on user queries (Joins etc)??
    Raghu

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You should create indexes in response to sepcific queries that aren't performing well. The more indexes you create the more likely that Oracle is going to use and index and the higher the cost for maintaining the indexes. As a DBA/Database Programmer you need to decide which indexes will help and which indexes won't help.

  4. #4
    Join Date
    Dec 2001
    Posts
    96
    Thanks,
    To Raghud's view, i want to know about indexes based on user's queries.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The best way, IMHO, is to trace a session that is "slow", do a tkprof on it, and see where indexes would be most helpful.
    Jeff Hunter

  6. #6
    Join Date
    Aug 2001
    Posts
    267
    There are no Thumb Rules, that we just follow them as standards. As other person replied, we need to analyze SQl's. If you have SQLs that you are getting from sessions. Just make sure you have indexes on most used SQL joins. If not watch the sessions (V$session,V$SQL etc.,) for day or two . Find the SQL's that are used.

    There are so many links on metalink.oracle.com that you can search.
    Raghu

  7. #7
    Join Date
    Oct 2002
    Posts
    807

    Re: recommended practice for creating custom indexes on oracle tables

    Originally posted by aj_usa
    Hi Friends,

    Can somebody suggest recommended practice for creating custom indexes on oracle tables ??
    What tables specifically and why? If you're talking about objects owned by SYS or SYSTEM, you just don't do it. That's the best practice.

    If you're talking about Oracle "applications" - tables owned by APPLSYS - that's another story. There are some naming conventions that are recommended which help during upgrades. Haven't worked on apps for sometime now..

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by marist89
    The best way, IMHO, is to trace a session that is "slow", do a tkprof on it, and see where indexes would be most helpful.

    Explain Plan on poor queries is enough to do .. Aint it?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    When creating b-tree indexes you need to consider block selectivity e.g. the uniqueness of values within an oracle block. This paper is excellent and contains a script for determining block selectivity.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by abhaysk
    Explain Plan on poor queries is enough to do .. Aint it?
    Not for me.
    Jeff Hunter

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