Are primary keys automatically indexed?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Are primary keys automatically indexed?

  1. #1
    Join Date
    Jan 2001
    Posts
    59

    Are primary keys automatically indexed?

    Are primary keys in Oracle database automatically indexed?

    If I have a million row of data and I use a primary key to perform the query, how long would it take? Would it take much longer than in a smaller table?


    thanks!

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Primary and Unique keys are enforced using indexes, so the answer is yes.

    Many things determine the time it takes to return values. During the search the size of the index, the size of the key fields, the distribution of the data and the current statistics etc all have an affect. Once the relevant rows are found the size of the row to be fetched has an impact also. It's impossible to give you a figure on retrieval time as we would be talking different tables and different servers.

    Make sure your stats are up to date or the optimizer may not use the index.

    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('schema-name', cascade => TRUE);

    Better still, only gather stale stats:

    http://www.oracle-base.com/Articles/...atistics8i.asp

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

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