Re: Query Help
Date: Sun, 20 Jan 2008 14:30:11 -0800 (PST)
Message-ID: <c714f8c5-81e4-4098-99ab-25bd002018b0@q39g2000hsf.googlegroups.com>
On Jan 20, 1:38 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Maxim Demenko schrieb:
>
> > it appears to be a *DUAL* magic, with regular
> > tables predicate evaluation occur to be short circuited without the hint
> > (on 10.2.0.3).
>
> Wrong assumption, just to correct myself.
>
> Best regards
>
> Maxim
Thanks for the update Maxim.
I am probably just wasting CPU cycles here as it is unreasonable to believe that someone would construct a SQL statement for production use like the one that I put together for testing here. Just for the sake of experimentation, a couple additional tests:
DROP TABLE T1;
CREATE TABLE T1(
RN NUMBER(10),
PRIMARY KEY (RN));
INSERT INTO T1
SELECT
ROWNUM RN
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT;
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE);
SELECT
MYVALUE.NEXTVAL MYVALUE
FROM
DUAL;
MYVALUE
63
SELECT
*
FROM
T1
WHERE
(1 = 2) AND (GET_MY_VALUE > 1);
no rows selected
SELECT
MYVALUE.NEXTVAL MYVALUE
FROM
DUAL;
MYVALUE
65
Now, taking a look at a DBMS_XPLAN:
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 | | 2 | INDEX FAST FULL SCAN| SYS_C008523 | 0 | 98945 | 0 |00:00:00.01 | -------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("GET_MY_VALUE"()>1 AND NULL IS NOT NULL))
And the plan from the 10053 trace
+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 1 | | | 1 | FILTER | | | | | |
| 2 | INDEX FAST FULL SCAN | SYS_C008523| 97K | 387K | 42 | 00:00:01 |
+-----------------------------------+
Predicate Information:
1 - filter(("GET_MY_VALUE"()>1 AND NULL IS NOT NULL))
From the 10046 trace file:
EXEC #4:c=0,e=300,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=167189537377 FETCH #4:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=167189537442 STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0time=241 us)'
STAT #4 id=2 cnt=0 pid=1 pos=1 obj=16397 op='INDEX FAST FULL SCAN SYS_C008523 (cr=0 pr=0 pw=0 time=0 us)'
The above shows no physical reads, no consistent mode reads, and no current mode reads - it did not actually read the blocks in the index. Notice that the filter predicates are in the reverse order of the way they were listed in the WHERE clause, and that likely triggered the call to the PL/SQL function.
On Oracle 11.1.0.6, the 10053 trace file shows the plan like this:
+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 1 | | | 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | T1 | 98K | 391K | 44 |00:00:01 |
+-----------------------------------+
Predicate Information:
1 - filter((NULL IS NOT NULL AND "GET_MY_VALUE"()>1))
In the above, the filter predicates are NOT in the reverse order of the way they were listed in the WHERE clause, so the PL/SQL function was never called. Note the cost on the SELECT statement - a 10046 trace file shows no physical reads, no consistent mode reads, and no current mode reads for the SQL statement.
Another test on 10.2.0.3, trying to wrap the function call between two
other conditions in the WHERE clause:
SELECT
*
FROM
T1
WHERE
(1 = ROUND(22/10)) AND (GET_MY_VALUE = 1) AND (2 = ROUND(19/10))
10053 plan:
+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 1 | | | 1 | FILTER | | | | | |
| 2 | INDEX FAST FULL SCAN | SYS_C008523| 97K | 387K | 42 | 00:00:01 |
+-----------------------------------+
Predicate Information:
1 - filter(("GET_MY_VALUE"()=1 AND NULL IS NOT NULL))
In the above, Oracle eliminated the (2 = ROUND(19/10)) specification, and then reversed the predicates in the WHERE clause, causing the PL/ SQL function to be called.
Another test on 10.2.0.3, this time the function was not called:
Current SQL statement for this session:
SELECT
*
FROM
T1
WHERE
(1 = ROUND(22/10)) AND (GET_MY_VALUE > 1) AND (2 = ROUND(32/10))
10053 plan:
+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | -------------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 1 | | | 1 | FILTER | | | | | |
| 2 | INDEX FAST FULL SCAN | SYS_C008523| 97K | 387K | 42 | 00:00:01 |
+-----------------------------------+
Predicate Information:
1 - filter((NULL IS NOT NULL AND "GET_MY_VALUE"()>1 AND NULL IS NOT NULL)) One final test that produced the same results on Oracle 10.2.0.3 and 11.1.0.6:
SELECT
*
FROM
T1
WHERE
(RN=-100) AND (GET_MY_VALUE < 1);
10053 plan:
+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | ----------------------------------------- +-----------------------------------+ | 0 | SELECT STATEMENT | | | | 1 | | | 1 | FILTER | | | | | | | 2 | INDEX UNIQUE SCAN | SYS_C008523| 1 | 4 | 1 |00:00:01 |
+-----------------------------------+
Predicate Information:
1 - filter("GET_MY_VALUE"()<1)
2 - access("RN"=-100)
10046 trace:
EXEC #30:c=0,e=245,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=168538001910
FETCH #30:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=168538001978
In the above, no physical reads, no consistent mode reads, and no current mode reads. The PL/SQL function was called on both versions of Oracle.
-
Shakespeare, there are roughly 1920 parameters in Oracle 11.1.0.6, compared with roughly 1440 in Oracle 10.2.0.3. Several of the default parameters have different values, but nothing seems to jump out as the source of how Oracle 11.1.0.6 was able to properly handle the shortcircuiting of the AND condition.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sun Jan 20 2008 - 16:30:11 CST