Prev: problem with remote desktop
Next: 10 Reasons Why YOU Should Join Us
From: Charles Hooper on 26 Nov 2009 21:59
On Nov 26, 3:32 pm, "Gerard H. Pille" <g...(a)skynet.be> wrote:
> Volker Borchert wrote:
> > Shakespeare wrote:
> >> Most times, these queries are generated by some tool that needs a where
> >> clause anyway, and 'AND's or 'OR's the user specified clauses to it, and
> >> they put in the 1=1 for when a user does not enter any condition..
>
> > Surprisingly, the optimizer sometimes seems to do a bad job about such
> > constant clauses. As in "where 1 = 2" resulting in a full table scan....
>
> You're kidding, right?

Volker is actually correct (OK, partially). However, it might be a
good idea to check the DBMS_XPLAN output and/or the output of a 10046
trace and/or the output of SET AUTOTRACE TRACEONLY STATISTICS. For
example, here is a test on Oracle 11.1.0.7 with a 100,000,000 row
table with a primary key column in a freshly bounced database:
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
*
FROM
T1
WHERE
1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 0
(0)| |
|* 1 | FILTER | | | |
| |
| 2 | TABLE ACCESS FULL| T1 | 100M| 5054M| 221K (1)|
00:44:14 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)

The plan indicates a full table scan, and the filter predicate?

Continuing:
SET AUTOTRACE TRACEONLY STATISTICS
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'My_Trace';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT
*
FROM
T1
WHERE
1=2;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
301 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

A full table scan on a 100,000,000 row table which performed 0
consistent gets.

SELECT SYSDATE FROM DUAL;

Now, a check of the 10046 trace file:
=====================
PARSING IN CURSOR #7 len=32 dep=0 uid=56 oct=3 lid=56 tim=327318181
hv=2373026659 ad='2775adb30' sqlid='077d50q6r30v3'
SELECT
*
FROM
T1
WHERE
1=2
END OF STMT
PARSE
#7:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3332582666,tim=327318181
EXEC
#7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3332582666,tim=327318181
WAIT #7: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=527 tim=327322648
FETCH
#7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3332582666,tim=327318181
STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0
time=0 us)'
STAT #7 id=2 cnt=0 pid=1 pos=1 obj=68961 op='TABLE ACCESS FULL T1
(cr=0 pr=0 pw=0 time=0 us cost=221088 size=5300000000 card=100000000)'
WAIT #7: nam='SQL*Net message from client' ela= 11075 driver
id=1413697536 #bytes=1 p3=0 obj#=527 tim=327343489

The STAT lines in the 10046 trace also confirm that Oracle did not
even bother to execute the full table scan.

Just for confirmation:
SELECT
COUNT(*)
FROM
T1;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
813324 consistent gets
813317 physical reads
0 redo size
342 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
From: Mark D Powell on 27 Nov 2009 19:39
On Nov 25, 1:27 pm, joel garry <joel-ga...(a)home.com> wrote:
> On Nov 25, 8:19 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
>
> > On Nov 24, 7:22 pm, m...(a)pixar.com wrote:
>
> > In you your example why do you have where 1 = 1 ?  Kind of unnecessary
> > isn't it.
>
> I have an example pinned up on my cube of an OCI generated code that
> has 48 1=1 statements in it.  It would be even more unnecessary for me
> to try to get rid of them.  :-)
>
> I'm sure there must be more somewhere in this kind of code, that one
> just happened to catch my eye in EM one day.   Doesn't seem to bother
> the optimizer at all.
>
> jg
> --
> @home.com is bogus.
> Death of wikipedia, news at 11.http://news.cnet.com/8301-1023_3-10403467-93.html

Joel, Your explantation is at least half-way logical. I would think
that you could write the code to just generate the correct where
clause as necessary and to not have one when there no comparison
conditions were provided, but I am not the one who had to write the
query generation logic.

HTH -- Mark D Powell --
First  |  Prev  | 
Pages: 1 2 3
Prev: problem with remote desktop
Next: 10 Reasons Why YOU Should Join Us