SQL & PL/SQL

Need some expertise in trying to determine why a sql statement takes longer than the same sql statement when the only difference is adding and "ABS" function. I am not an expert in explain plans so any assistance would really help


First SQL takes forever using ABS function:

drop table plan_table;

@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL

explain plan for
select
name, subtype, datestarted, extendeddata
from
llprod.dtree
where
subtype in (204,205,206)
start with
dataid = 18197135
connect by
prior dataid = ABS(parentid);

@?/rdbms/admin/utlxpls

output example:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4059K| 181M| 10625 (20)|
|* 1 | FILTER | | | | |
|* 2 | CONNECT BY WITH FILTERING | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DTREE | | | |
|* 4 | INDEX RANGE SCAN | DTREE_DATAID | 1 | 6 | 4 (25)|
| 5 | HASH JOIN | | | | |
| 6 | CONNECT BY PUMP | | | | |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| 7 | TABLE ACCESS FULL | DTREE | 4059K| 181M| 10625 (20)|
| 8 | TABLE ACCESS FULL | DTREE | 4059K| 181M| 10625 (20)|
------------------------------------------------------------------------------------

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

1 - filter("DTREE"."SUBTYPE"=204 OR "DTREE"."SUBTYPE"=205 OR
"DTREE"."SUBTYPE"=206)
2 - filter("DTREE"."DATAID"=18197135)
4 - access("DTREE"."DATAID"=18197135)

22 rows selected.

====================================

Second sql comes back immediately with no ABS function

delete from plan_table;

explain plan for
select
name, subtype, datestarted, extendeddata
from
llprod.dtree
where
subtype in (204,205,206)
start with
dataid = 18197135
connect by
prior dataid = (parentid);

@?/rdbms/admin/utlxpls

output example:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 423 | 2 (50)|
|* 1 | FILTER | | | | |
|* 2 | CONNECT BY WITH FILTERING | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | DTREE | | | |
|* 4 | INDEX RANGE SCAN | DTREE_DATAID | 1 | 6 | 4 (25)|
| 5 | NESTED LOOPS | | | | |
| 6 | BUFFER SORT | | 9 | 423 | |

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
| 7 | CONNECT BY PUMP | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| DTREE | 9 | 423 | 2 (50)|
|* 9 | INDEX RANGE SCAN | DTREE_PARENTID | 9 | | 4 (25)|
| 10 | TABLE ACCESS FULL | DTREE | | | |
---------------------------------------------------------------------------------------

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

1 - filter("DTREE"."SUBTYPE"=204 OR "DTREE"."SUBTYPE"=205 OR "DTREE"."SUBTYPE"=206)
2 - filter("DTREE"."DATAID"=18197135)

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
4 - access("DTREE"."DATAID"=18197135)
9 - access("DTREE"."PARENTID"=NULL)

Thanks
Jim