| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: obtain primary keys
Sybrand Bakker wrote:
> 1 Concatenated primary keys are best avoided at all > 2 For non-concatenated primary keys usually the number(10) definition > is more than sufficient. > 3 There is no special datatype and there is no need for it as there > are sequences. The advantage of a sequence is, it is not bound to a > table. > I don't think you will even notice changing a single key column from > number(6) to number(13). However, you'll probably will notice the use > of a composite primary key.
Thanks for your answer.
This is of topic, but from what I remember from the db classes I once took: If I have two entities with a many to many relationship, I have to split the n-n with a new entity in between. And there it is, concat PK. Or do you mean that it's better to have the keys from the two first entities just as foreign keys, not part of the primary key, and add a new column as PK in the third entity?
select *
from a, ab, b
where a.aix = 1 and ab.aix = a.aix and b.bix = ab.bix;
Queries would be the same in both cases as I understand it. Do you mean that Oracle is performing better if table ab don't have concat PK?
Hope I've made myself clear ($NATIVE_LANG != "en")
[later]
I've now done some tests. This is far out of my league to understand, but if you have the time would you care to explain whats going on here.
I first made tree tables a, b, and ab where ab have a concatenated primary key composed of the primary keys from a and b. Then I added some data in all tables and explained the select above. After that I changed table ab to have a primary key of its own and the PK's from table a and b as foreign keys and explained the same select.
This is the explain plan from first test:
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 152 | 5
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 152 | 5
(0)| 00:00:01 |
| 2 | NESTED LOOPS | | 5 | 250 | 5
(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| A | 1 | 25 | 2
(0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_A | 1 | | 1
(0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | B | 5 | 125 | 3
(0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_AB | 1 | 26 | 0
Predicate Information (identified by operation id):
4 - access("A"."AIX"=3)
6 - access("B"."BIX"="AB"."BIX" AND "AB"."AIX"=3)
And this is from the second test:
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 152 | 6
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 152 | 6
(0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 102 | 4
(0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| A | 1 | 25 | 1
(0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_A | 1 | | 1
(0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | AB | 2 | 52 | 3
(0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | B | 1 | 25 | 1
(0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_B | 1 | | 0
Predicate Information (identified by operation id):
4 - access("A"."AIX"=3)
5 - filter("AB"."AIX"=3)
7 - access("B"."BIX"="AB"."BIX")
Note
To me it seems that the first test with concat PK is better when it comes to CPU usage.
I admit that I still have to read more about how to interpret explain plans and perhaps the little data might make those examples worthless. It would be nice to here your opinion.
Thanks for your time,
ph
Received on Fri Feb 11 2005 - 11:57:02 CST
 |
 |