If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

What are the pros and cons, performance-wise about using the primary key of the parent table as part of the primary key in the child tables v.s. just having it as a foreign key in the child tables ?? And if the keys are all surrogate keys, how many levels down can I migrate that first primary key ??

One advantage is that it could save you having to create an additional index (in addition to surrogate key index) on the child fk columns if these are ever used in select criteria (which they often are as you typically want children that match some criteria for some particular parent).

Pushing keys down into child tables is a real help if you need to query something way down a hierachy of parent child tables when the key of the grandparent/great grand parent table is needed in the query as it saves having to join thru all the intermediate parent tables. However wide keys effect the efficiency of the index. I've seen dramatic improvements on queries with 5 tables in parent-child configuration being adapted FROM surrogate keys to cascaded primary keys where the final child table had ~10 million rows. The key was numeric and only one column was introduced per level. If the key columns are "wide" (long character strings etc.) then this will tip the balance away from cascaded keys.

ur desision may be depend from:
1 -- u had to declare FK on child table
2 -- u didn't declare FK constraint, and use "logical FK constraints" on application level.
------------------------------------------------------------------
in first case better has
-- 2 indexes on child table (PK index and FK index)
in second case may be (but not always) better has
-- composit index that has first fields are equal filelds from PK of parent table.
------------------------------------------------------------------
Problem with perfomance on FK are
-- locks on chaid table(s) when u update/delete rows from parent table.
if u haven't any indexes on FK fields of the child table(s) and declare FK constraint
oracle have to inmpement locks to all rows from child table
(see Tom Kyte "deadlocks" and "indexes on fk")

about 1 or 2 indexes on child tabe:
if u have PK constraint, that not include any fields from FK constraint
and u going to add FK fiels to composit of PK index u:
-- if u place FK fields last, then oracle probably cant use this index
with update/delete operation on a paret table.
-- if u place FK fields first, then ur PK B+ index will be not so good
balanced, as with PK fields only.

I see another aspect of using the foreign key as part of the primary, and that is utilizing key compression for the child table primary key. Is there much overhead during the key generation utilizing key compression versus not ??

3. if u place FK fields last then:
================================
* First levels of tree:
* keep information from pk field of child table only!
-----------------------------------------
* Last levels of tree
* keep information from FK field of child table (or pk fields of parent table)
================================
ORACLE CAN'T USE THIS INDEX FOR LOCK ROWS ON THE CHILD TABLE
WHEN IT UPDATE/DELETE ROW(S) IN PARENT TABLE.
ORACLE HAVE TO LOCK !!!ALL ROWS OF CHILD TABLE.

4. if u place FK fields first THEN:
================================
* First levels of tree:
* keep information from FK field of child table
-----------------------------------------
* Last levels of tree
* keep information from pk field of child table
================================
oracle can use trhis index for lock,
but ORACKE HAVE TO MAKE ADDITION READS FOR FOR GET
EACH "CLEAR" PK OF A CHILD TABLE.