On Jan 20, 1:38 pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
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,TABNAM E=>'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:
----
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("GET_MY_VALUE"()>1 AND NULL IS NOT NULL))
----
And the plan from the 10053 trace
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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,ti m=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=0
time=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:
--------------------------------------
+-----------------------------------+
--------------------------------------
+-----------------------------------+
--------------------------------------
+-----------------------------------+
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:
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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:
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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:
-----------------------------------------
+-----------------------------------+
-----------------------------------------
+-----------------------------------+
-----------------------------------------
+-----------------------------------+
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,t im=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 short-
circuiting of the AND condition.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 20, 1:38 pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
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,TABNAM E=>'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:
----
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("GET_MY_VALUE"()>1 AND NULL IS NOT NULL))
----
And the plan from the 10053 trace
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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,ti m=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=0
time=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:
--------------------------------------
+-----------------------------------+
--------------------------------------
+-----------------------------------+
--------------------------------------
+-----------------------------------+
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:
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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:
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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:
-----------------------------------------
+-----------------------------------+
-----------------------------------------
+-----------------------------------+
-----------------------------------------
+-----------------------------------+
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,t im=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 short-
circuiting of the AND condition.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Jan 20, 1:38 pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
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,TABNAM E=>'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:
----
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("GET_MY_VALUE"()>1 AND NULL IS NOT NULL))
----
And the plan from the 10053 trace
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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,ti m=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=0
time=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:
--------------------------------------
+-----------------------------------+
--------------------------------------
+-----------------------------------+
--------------------------------------
+-----------------------------------+
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:
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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:
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
--------------------------------------------
+-----------------------------------+
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:
-----------------------------------------
+-----------------------------------+
-----------------------------------------
+-----------------------------------+
-----------------------------------------
+-----------------------------------+
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,t im=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 short-
circuiting of the AND condition.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.