Why Would Using ABS Function Cause SQL To Take A long Time
552058Oct 30 2007 — edited Nov 5 2007Need 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