If by PIVOT
table you mean a many-to-many
link table, then no, it will only hamper performance.
You should keep parent_id
in the child table.
The many-to-many
link table takes an extra JOIN
and therefore is less efficient.
Compare the following queries:
SELECT *
FROM child_table c
JOIN child_to_parent cp
ON cp.child = c.id
JOIN parent p
ON p.id = cp.parent
WHERE c.property = 'some_property'
and this one:
SELECT *
FROM child_table c
JOIN parent p
ON p.id = c.parent
WHERE c.property = 'some_property'
The latter one is one JOIN
shorter and more efficient.
The only possible exception to that rule is that you run these queries often:
SELECT *
FROM child_table c
JOIN parent_table p
ON p.id = c.parent
WHERE c.id IN (id1, id2, ...)
, i. e. you know the id
's of the child rows beforehand.
This may be useful if you use natural keys for your child_table
.
In this case yes, the child_to_parent
link table will be more efficient, since you can just replace it with the following query:
SELECT *
FROM child_to_parent cp
JOIN parent_table p
ON p.id = cp.parent
WHERE cp.child IN (id1, id2, ...)
and child_to_parent
will be always less or equal in size to child_table
, and hence more efficient.
However, in Oracle
you can achieve just the same result creating a composite index on child_table (id, parent_id)
.
Since Oracle
does not index NULL
's, this index will be just like your child_to_parent
table, but without the table itself and implied maintenance overhead.
In other systems (which index NULL
's), the index may be less efficient than a dedicated table, especially if you have lots of NULL
parents.