| Question regarding performance impact on disabling foreign key [message #506865] |
Wed, 11 May 2011 08:48  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Kevin Meade wrote:
"Remember, indexes with the right leading columns are good enough for any constraint these days so your indexes no longer have to exactly match your constraints."
Source: http://www.orafaq.com/forum/mv/msg/170674/505512/136107#msg_505512
Want to know index on foreign key constraint but disabled foreign key constraint can have any impact on performance.
eg, What if foreign key fk_dept (dept_id) is disabled but index exists on the column dept_id.
Is there will be any difference if foreign key enabled and index exists vs.
index exists on the column but foreign key kept disabled.
Regards
Ved
|
|
|
|
|
|
|
|
| Re: Question regarding performance impact on disabling foreign key [message #506876 is a reply to message #506874] |
Wed, 11 May 2011 09:30  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Say you do:
SELECT * FROM emp e
WHERE EXISTS (SELECT 1 from dept d WHERE d.dept_id = e.dept_id)
If you have a foreign key from emp to dept on dept_id then oracle can skip the exists sub-query entirely, since the fk ensures that a matching record in dept must exist. If you disable the fk then the sub-query must be run.
|
|
|
|
| Re: Question regarding performance impact on disabling foreign key [message #507088 is a reply to message #506876] |
Thu, 12 May 2011 07:34 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
When you enable a foreign key, inserts and updates of the key column(s) have a performance impact; they have to go check the child exists. Similarly, deletes and updates of the primary key on the parent table have a performance impact as they must ensure no matching rows exist in the child table.
Also worth noting that you cannot use direct-path load on the child table, and there are some tricky restrictions on Parallel DML.
Ross Leishman
|
|
|
|
|
|
|
|
| Re: Question regarding performance impact on disabling foreign key [message #507336 is a reply to message #507223] |
Fri, 13 May 2011 14:57 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Thanks for the reply.
For the above sql, the plan is same with or without fk. So, Optimizer can't rely on the foreign key to generate the plan.
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 1015K| 18 (12)|
| 1 | HASH JOIN RIGHT SEMI| | 20000 | 1015K| 18 (12)|
| 2 | INDEX FULL SCAN | PK_DEPTID | 5 | 65 | 1 (0)|
| 3 | TABLE ACCESS FULL | EMP | 20000 | 761K| 16 (7)|
-----------------------------------------------------------------------
But how can we verify that it is not going to execute the subquery when fk is enabled? I tried to observe the performance with or without fk with 20000 rows in emp table but did not find any performance difference with or without fk.A demonstrain would help.
Regards
Ved
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
