Hi gurus,
I encounter a strange performance issue on a recursive query. By removing elements one by one, I figured out the query begins to run very slow when I add a OR in the CONNECT BY clause.
The simplified query is this one:
SELECT
CONNECT_BY_ROOT( a.c_individu )
,a.c_individu
,SYS_CONNECT_BY_PATH( to_char(a.c_individu,'FM099999999'), ' ') path
,LEVEL
FROM
aaa a
WHERE
CONNECT_BY_ISLEAF=1
AND LEVEL > 1
CONNECT BY a.c_individu < PRIOR a.c_individu
AND ( a.prenom_norm= PRIOR a.prenom_norm
AND a.d_naissance = PRIOR a.d_naissance
AND a.nom_famille_norm = PRIOR a.nom_famille_norm
/* OR 1=2 */ )
The AAA table contains 10.000 rows for my test purposes, and has an index on C_INDIVIDU.
If I run this query with the "OR 1=2" commented, it runs pretty fast:
Elapsed: 00:00:00.10
Execution Plan
----------------------------------------------------------
Plan hash value: 3286454403
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 292K| 578 (1)| 00:00:07 |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | | |
| 3 | TABLE ACCESS FULL | AAA | 10000 | 292K| 578 (1)| 00:00:07 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CONNECT_BY_ISLEAF=1 AND LEVEL>1)
2 - access("A"."PRENOM_NORM"=PRIOR "A"."PRENOM_NORM" AND
"A"."D_NAISSANCE"=PRIOR "A"."D_NAISSANCE" AND "A"."NOM_FAMILLE_NORM"=PRIOR
"A"."NOM_FAMILLE_NORM")
filter("A"."C_INDIVIDU"<PRIOR "A"."C_INDIVIDU")
As soon as I add the "OR 1=2" statement, the query runs very slow (19 seconds vs 0.1 seconds) and the execution plan is very different:
Elapsed: 00:00:18.84
Execution Plan
----------------------------------------------------------
Plan hash value: 3286454403
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 292K| 578 (1)| 00:00:07 |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | | |
| 3 | TABLE ACCESS FULL | AAA | 10000 | 292K| 578 (1)| 00:00:07 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CONNECT_BY_ISLEAF=1 AND LEVEL>1)
2 - access(INTERNAL_FUNCTION("A"."C_INDIVIDU")<INTERNAL_FUNCTION(PRIOR
"A"."C_INDIVIDU"))
filter("A"."PRENOM_NORM"=PRIOR "A"."PRENOM_NORM" AND
"A"."D_NAISSANCE"=PRIOR "A"."D_NAISSANCE" AND "A"."NOM_FAMILLE_NORM"=PRIOR
"A"."NOM_FAMILLE_NORM" OR 1=2)
Could someone explain me why?
Thanks !
BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production