Are primary keys automatically indexed?

[thg]

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!

[TimHall]

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...