Re: Query Help

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 20 Jan 2008 13:07:46 +0100
Message-ID: <47933992.3080806@gmail.com>

Charles Hooper schrieb:

> On Jan 17, 10:20 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:

>> First, let's create a sequence (an automatically increasing number),
>> and then verify that it works correctly:
>> CREATE SEQUENCE MYVALUE INCREMENT BY 1 START WITH 1;
>>
>> SELECT
>> MYVALUE.NEXTVAL MYVALUE
>> FROM
>> DUAL;
>>
>> MYVALUE
>> ----------
>> 1
>>
>> The above incremented the MYVALUE sequence by 1 and returned the value
>> 1.
>>
>> SELECT
>> MYVALUE.NEXTVAL MYVALUE
>> FROM
>> DUAL;
>>
>> MYVALUE
>> ----------
>> 2
>>
>> The above incremented the MYVALUE sequence by 1 and returned the value
>> 2.
>>
>> We can't include a sequence directly in a WHERE clause to determine if
>> Oracle permits short-circuiting. However, we can create a PL/SQL
>> function to select the next value from the MYVALUE sequence, and see
>> if that function is called.
>>
>> CREATE OR REPLACE FUNCTION GET_MY_VALUE
>> RETURN NUMBER
>> IS
>> MYVAL NUMBER;
>> BEGIN
>> SELECT MYVALUE.NEXTVAL INTO MYVAL FROM DUAL;
>> RETURN MYVAL;
>> END GET_MY_VALUE;
>> /
>>
>> Now, let's test to make certain that the PL/SQL function works as
>> expected:
>> SELECT
>> GET_MY_VALUE
>> FROM
>> DUAL;
>>
>> GET_MY_VALUE
>> ------------
>> 3
>>
>> The function returned 3, which is 1 greater than before, so it appears
>> to work correctly.
>>
>> Let's try again:
>> SELECT
>> GET_MY_VALUE
>> FROM
>> DUAL;
>>
>> GET_MY_VALUE
>> ------------
>> 4
>>
>> The function returned 4, which is 1 greater than before.
>>
>> Now, let's turn on a 10046 trace at level 4 to help determine what is
>> going on behind the scenes:
>> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
>>
>> Let's create a SQL statement with an OR condition in the WHERE clause,
>> such that the first half of the OR condition will always be TRUE:
>> SELECT
>> 'FOUND'
>> FROM
>> DUAL
>> WHERE
>> (1 = 1) OR (GET_MY_VALUE > 1);
>>
>> 'FOUN
>> -----
>> FOUND
>>
>> Disabling the trace so as not to confuse the situation when we later
>> check the NEXTVAL of the sequence:
>> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
>>
>> Let's see if the second half of the OR condition was evaluated or
>> not. If it was not evaluated, the value returned should be one
>> greater than the last time we checked the NEXTVAL of the sequence. If
>> it was evaluated, the value returned should be two greater than the
>> last time we checked the NEXTVAL of the sequence:
>> SELECT
>> MYVALUE.NEXTVAL MYVALUE
>> FROM
>> DUAL;
>>
>> MYVALUE
>> ----------
>> 5
>>
>> It looks like Oracle does allow short-circuiting of OR conditions, but
>> let's confirm by checking the trace file that we generated by setting
>> event 10046:
>> PARSING IN CURSOR #5 len=66 dep=0 uid=0 oct=3 lid=0 tim=179928174658
>> hv=2929544144 ad='25e04918' sqlid='36w6vf6r9ukyh'
>> SELECT
>> 'FOUND'
>> FROM
>> DUAL
>> WHERE
>> (1 = 1) OR (GET_MY_VALUE > 1)
>> END OF STMT
>> PARSE
>> #5:c=0,e=34300,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=179928174652
>> BINDS #5:
>> EXEC #5:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=179928176367
>> FETCH #5:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=179928176450
>> STAT #5 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0
>> time=0 us cost=2 size=0 card=1)'
>> FETCH #5:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=179928196143
>> =====================
>>
>> In the above, only 1 cursor was parsed, and its dep was equal to 0,
>> indicating that the function was not evaluated.
>>
>> Let's try another test, such that the first half of the OR condition
>> will always be FALSE (unless of course one decides to divide by 0):
>> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
>>
>> SELECT
>> 'FOUND'
>> FROM
>> DUAL
>> WHERE
>> (1 = 2) OR (GET_MY_VALUE > 1);
>>
>> 'FOUN
>> -----
>> FOUND
>>
>> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
>>
>> Let's check the NEXTVAL of the sequence to determine if the second
>> half of the OR condition was evaluated. If it was not evaluated, the
>> value returned should be one greater than the last time we checked the
>> NEXTVAL of the sequence. If it was evaluated, the value returned
>> should be two greater than the last time we checked the NEXTVAL of the
>> sequence:
>> SELECT
>> MYVALUE.NEXTVAL MYVALUE
>> FROM
>> DUAL;
>>
>> MYVALUE
>> ----------
>> 7
>>
>> The returned value was two higher than the last time checked, so the
>> second half was evaluated. Let's check the trace file just for
>> confirmation:
>> =====================
>> PARSING IN CURSOR #1 len=66 dep=0 uid=0 oct=3 lid=0 tim=180750448587
>> hv=3085677967 ad='25e326ac' sqlid='49m6ksavyrdcg'
>> SELECT
>> 'FOUND'
>> FROM
>> DUAL
>> WHERE
>> (1 = 2) OR (GET_MY_VALUE > 1)
>> END OF STMT
>> PARSE
>> #1:c=0,e=1973,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=180750448582
>> BINDS #1:
>> BINDS #4:
>> =====================
>> PARSING IN CURSOR #4 len=32 dep=1 uid=0 oct=3 lid=0 tim=180750449027
>> hv=3972398240 ad='25fb973c' sqlid='bzhnd9rqcbz50'
>> SELECT MYVALUE.NEXTVAL FROM DUAL
>> END OF STMT
>> EXEC #4:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=180750449023
>> FETCH #4:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=180750449131
>> EXEC #1:c=0,e=305,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=180750449186
>> FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=180750449264
>> STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0
>> time=0 us)'
>> STAT #1 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0
>> time=0 us cost=2 size=0 card=1)'
>> FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=180750449701
>>
>> Note that in the above there are two cursors parsed, with the first
>> showing a dep of 0, and the second with a dep of 1 (the function
>> call).
>>
>> Let's see if Oracle allows short-circuiting of AND conditions:
>> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';
>>
>> SELECT
>> 'FOUND'
>> FROM
>> DUAL
>> WHERE
>> (1 = 2) AND (GET_MY_VALUE > 1);
>>
>> no rows selected
>>
>> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
>>
>> SELECT
>> MYVALUE.NEXTVAL MYVALUE
>> FROM
>> DUAL;
>>
>> MYVALUE
>> ----------
>> 8
>>
>> =====================
>> PARSING IN CURSOR #5 len=67 dep=0 uid=0 oct=3 lid=0 tim=183903614533
>> hv=1150342053 ad='2f232fc0' sqlid='c13dunp291nx5'
>> SELECT
>> 'FOUND'
>> FROM
>> DUAL
>> WHERE
>> (1 = 2) AND (GET_MY_VALUE > 1)
>> END OF STMT
>> PARSE #5:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=183903614527
>> BINDS #5:
>> EXEC #5:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=183903614960
>> FETCH #5:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=183903615026
>> STAT #5 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0
>> time=0 us)'
>> STAT #5 id=2 cnt=0 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0
>> time=0 us cost=2 size=0 card=1)'
>> =====================
>>
>> It appears, at least for Oracle 11.1.0.6.0 SQL, that your second
>> assertion is correct.
>>
>> Charles Hooper
>> IT Manager/Oracle DBA
>> K&M Machine-Fabricating, Inc.

> 
> Just a quick note.  I repeated the above test on Oracle 10.2.0.3 and
> received slightly different results for the short-circuiting tests
> with the AND condition in the WHERE clause.  I found that if the
> (GET_MY_VALUE > 1) condition is on the right of the AND condition, as
> it was in the above example, the GET_MY_VALUE function is called, and
> the sequence value is incremented.  If the (GET_MY_VALUE > 1)
> condition in on the left of the AND condition, the GET_MY_VALUE
> function is NOT called.  On Oracle 11.1.0.6, it made no difference
> whether the GET_MY_VALUE function is on the left or the right of the
> AND condition.
> 
> SELECT
>   'FOUND'
> FROM
>   DUAL
> WHERE
>   (1 = 2) AND (GET_MY_VALUE > 1);
> 
> Above did not short-circuit on Oracle 10.2.0.3 - the function was
> called.  From the trace file:
> =====================
> PARSING IN CURSOR #2 len=67 dep=0 uid=32 oct=3 lid=32 tim=94824205757
> hv=1150342053 ad='506fed74'
> SELECT
>   'FOUND'
> FROM
>   DUAL
> WHERE
>   (1 = 2) AND (GET_MY_VALUE > 1)
> END OF STMT
> PARSE #2:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=94824205750
> BINDS #2:
> =====================
> PARSING IN CURSOR #1 len=32 dep=1 uid=32 oct=3 lid=32 tim=94824212466
> hv=3972398240 ad='5078c710'
> SELECT MYVALUE.NEXTVAL FROM DUAL
> END OF STMT
> PARSE
> #1:c=15600,e=641,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=94824212462
> BINDS #1:
> EXEC #1:c=0,e=208,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=94824213596
> FETCH #1:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=94824213817
> EXEC
> #2:c=15600,e=2537,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=94824214041
> FETCH #2:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=94824214262
> STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0
> time=2326 us)'
> STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0
> time=0 us)'
> =====================
> 
> Now, placing the GET_MY_VALUE function on the left of the AND:
> SELECT
>   'FOUND'
> FROM
>   DUAL
> WHERE
>   (GET_MY_VALUE > 1) AND (1 = 2);
> 
> Above did short-circuit on Oracle 10.2.0.3 and 11.1.0.6.  From the
> trace file:
> =====================
> PARSING IN CURSOR #2 len=67 dep=0 uid=32 oct=3 lid=32 tim=95036130064
> hv=1147969934 ad='506f96f0'
> SELECT
>   'FOUND'
> FROM
>   DUAL
> WHERE
>   (GET_MY_VALUE > 1) AND (1 = 2)
> END OF STMT
> PARSE #2:c=0,e=1916,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=95036130059
> BINDS #2:
> EXEC #2:c=0,e=232,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=95036219119
> FETCH #2:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=95036219355
> STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0
> time=9 us)'
> STAT #2 id=2 cnt=0 pid=1 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0
> time=0 us)'
> =====================
> 
> For the OR condition, it made no difference whether the GET_MY_VALUE
> function is on the left or the right of the AND on either Oracle
> 10.2.0.3 or 11.1.0.6.
> 
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

According to docs, Oracle does short circuit evaluating IF and CASE in PL SQL, some caselike sql functions are as well documented to be doing short circuit evaluation, i didn't see however in documentation, that the same applies to predicate evaluation (wherease it seems to make sense). One can try to force short circuit evaluation with the hint /*+ ORDERED_PREDICATES */, however in your testcase it doesn't seeem to work. On the other side, 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).

SQL> set serveroutput on
SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production

SQL> create or replace function get_my_value

   2 return number
   3 is
   4 begin
   5 dbms_output.put_line('Function called');    6 return 10;
   7 end;
   8 /

Function created.

SQL> select

   2 'FOUND'
   3 FROM dual
   4 WHERE (1 = 2) and (GET_MY_VALUE > 1);

no rows selected

Function called
SQL> select

   2 --+ ORDERED_PREDICATES
   3 'FOUND'
   4 FROM dual
   5 WHERE (1 = 2) and (GET_MY_VALUE > 1);

no rows selected

Function called
SQL> select

   2 'FOUND'
   3 FROM emp
   4 WHERE (1 = 2) and (GET_MY_VALUE > 1);

no rows selected

SQL> select

   2 --+ ORDERED_PREDICATES
   3 'FOUND'
   4 FROM emp
   5 WHERE (1 = 2) and (GET_MY_VALUE > 1);

no rows selected

SQL> Best regards

Maxim Received on Sun Jan 20 2008 - 06:07:46 CST