On Nov 26, 3:32*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
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

---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------

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=0pw=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.

On Nov 25, 1:27*pm, joel garry <joel-ga... (AT) home (DOT) com> wrote:
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 --