Bug 9290526 - Poor plan for recursive SQL used for DML involving a UNIQUE constraint [ID 9290526.8]

…

[@more@]

A suboptimal plan may be seen for the specific recursive SQL:
select c.name, u.name
from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled =:1 and
c.owner# = u.user#;

This can cause inserts involving a unique constraint to show poor performance.

Workaround
Set optimizer_mode=rule for the affected DMLs.

plan for
2 select c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# =
3 cd.con# and cd.enabled = :v1 and c.owner# = u.user#;

Explained.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2222027377

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 336 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 7 | 336 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN | | 7 | 203 | 8 (13)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CDEF$ | 7 | 56 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_CDEF4 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CON$ | 3584 | 75264 | 5 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | USER$ | 33 | 627 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("C"."OWNER#"="U"."USER#")
2 - access("C"."CON#"="CD"."CON#")
4 - access("CD"."ENABLED"=TO_NUMBER(:V1))

20 rows selected.

plan for
2 select /*+ rule */ c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :v1 and c.owner# = u.user#;

Explained.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1027684349

-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID| CDEF$ |
|* 4 | INDEX RANGE SCAN | I_CDEF4 |
| 5 | TABLE ACCESS BY INDEX ROWID| CON$ |
|* 6 | INDEX UNIQUE SCAN | I_CON2 |
| 7 | TABLE ACCESS CLUSTER | USER$ |
|* 8 | INDEX UNIQUE SCAN | I_USER# |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("CD"."ENABLED"=TO_NUMBER(:V1))
6 - access("C"."CON#"="CD"."CON#")
8 - access("C"."OWNER#"="U"."USER#")

Note
-----
- rule based optimizer used (consider using cbo)

26 rows selected.