Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

General question about tuning composite primary keys

497683Sep 7 2007 — edited Sep 7 2007
I have maybe a simple general question about composite primary keys.

For example the following table with 3 columns (COL01, COL02, COL03, not sorted, only sorted for this example to make it easier to understand) is given, which will be used for the Primary key (all VARCHAR):

COL01 COL02 COL03 COL04 COL05 COL06 .......
AAA1 BBB1 CCC1
AAA1 BBB1 CCC2
AAA1 BBB2 CCC3
AAA1 BBB3 CCC4
AAA2 LLL1 YYY1
AAA2 LLL2 XXX2
AAA2 LLL2 XXX3

Now I can create a composite Primary Key with the following column order (COL01, COL02, COL03) or e.g. with another column order (COL03, COL02, COL01). I have noticed that there are differences in the performance when changing the column order in the composite key.

Is there any rule of thumb how to select the column order in a composite primary key? What will be the best order in this example under the aspect of performance? Or does it only depends on the select statements, that will use the primary key (or rather the index of the PK)

I found only examples that describes the DB handling of B*tree index for a single column. But how does it works on a composit index?

Does anyone know a good book or tutorial, wich handles SQL Tuning and Index Tuning aspects? Is there any posibility to see in the database itself, which combination of columns produce the best performance (analyze index...) ??
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 5 2007
Added on Sep 7 2007
1 comment
382 views