February 20, 2010
A recent thread on OTN asked the following question:
Sometimes predicates can be generated due to Transitive Closure, but is it possible to see below mentioned behaviour. Is it possible to create test case for observing this behaviour?
You can even have cases like this one: if n1 > 10 and n1 < 0, then 0 > 10, which is always false, and therefore can short-circuit an entire branch of an execution plan.
In the OTN thread, Jonathan Lewis directly answered the question posted by the original poster, and I provided an answer to a similar question that involved two columns in a test table. The setup for the test case included the following SQL statements:
CREATE TABLE T1 AS SELECT ROWNUM C1, ROWNUM C2 FROM DUAL CONNECT BY LEVEL<=100000; COMMIT; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1') ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH BUFFER_CACHE;
(Note that submitting the test case using my VBS tool makes short work of temporarily changing the STATISTICS_LEVEL and enabling a 10046 trace while generating a DBMS_XPLAN for a SQL statement: Automated DBMS_XPLAN, Trace, and Send to Excel )
The SQL to generate the test case follows:
ALTER SESSION SET STATISTICS_LEVEL='ALL';
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_SHORT_CIRCUIT';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
SELECT
*
FROM
T1
WHERE
C1<=100
AND C2>=10000
AND C1>C2;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
SQL_ID 3uk9dajd5cn74, child number 0
-------------------------------------
SELECT T1.* FROM T1 WHERE C1<=100 AND C2>=10000 AND C1>C2
Plan hash value: 3332582666
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL| T1 | 0 | 1 | 0 |00:00:00.01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter(("C1">"C2" AND "C2"<100 AND "C1"<=100 AND "C1">10000 AND
"C2">=10000))
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
Notice in the execution plan that the Starts column for the TABLE ACCESS FULL operation is 0 – that line in the execution plan was never executed. By reviewing the 10046 trace file, you could further confirm that there were no physical reads when the SQL statement executed, which confirms that the full table scan operation was never performed:
PARSING IN CURSOR #2 len=68 dep=0 uid=31 oct=3 lid=31 tim=3008717127 hv=1515606244 ad='a775f1c8' SELECT T1.* FROM T1 WHERE C1<=100 AND C2>=10000 AND C1>C2 END OF STMT PARSE #2:c=0,e=2320,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3008717120 EXEC #2:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3008717834 WAIT #2: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3008717930 FETCH #2:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3008718038 WAIT #2: nam='SQL*Net message from client' ela= 1777 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3008719953 STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0 time=5 us)' STAT #2 id=2 cnt=0 pid=1 pos=1 obj=114196 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us)'
Recent Comments