Re: Query Help

From: shakespeare <whatsin_at_xs4all.nl>
Date: Sun, 20 Jan 2008 12:18:37 +0100
Message-ID: <47932e0f$0$85790$e4fe514c@news.xs4all.nl>

Comments below (somehow indenting doesn't work with my client on Charles' posts ??)

"Charles Hooper" <hooperc2000_at_yahoo.com> schreef in bericht news:e6fba3c3-89b4-483f-b0c9-bfe2d4b691d3_at_d21g2000prg.googlegroups.com... 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.

--> Comments

Isn't there a way to tell the database to use or not to use lazy evaluation?

Shakespeare Received on Sun Jan 20 2008 - 05:18:37 CST

This message: [ Message body ]
Next message: Maxim Demenko: "Re: Query Help"
Previous message: Charles Hooper: "Re: Query Help"
In reply to: Charles Hooper: "Re: Query Help"
Next in thread: Maxim Demenko: "Re: Query Help"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message