Re: Oracle scans table with "WHERE (1=0)"?

From: <stevedhoward_at_gmail.com>
Date: 7 Apr 2006 18:46:02 -0700
Message-ID: <1144460762.717217.29830@g10g2000cwb.googlegroups.com>

I'm not sure for which version you received the "confirmation", but it looks like a test case shows that behind the scenes, although a full table scan is defined in the execution plan, no actual work is done. See below on 10.2.0.2 on XP for a simple test case...

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size                  1247900 bytes
Variable Size              71304548 bytes
Database Buffers           92274688 bytes
Redo Buffers                2945024 bytes

Database mounted.
Database opened.
SQL> drop table rep.objects;

Table dropped.

SQL> connect rep/rep
Connected.
SQL> create table objects as select * from dba_objects;

Table created.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select null from objects where 1 = 0;

no rows selected

SQL> select null from objects where object_name = 'T0406';

N
-

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\oracle\product\10.2.0\admin\test10g\udump>tkprof test10g_ora_4916.trc sys=no
output = 1.tkp

TKPROF: Release 10.2.0.1.0 - Production on Fri Apr 7 21:43:05 2006

C:\oracle\product\10.2.0\admin\test10g\udump>

----------------------------------------output

below----------------------------------------

select *
from
objects where 1 = 0

call count cpu elapsed disk query current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.00          0          2          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        1      0.00       0.00          0          0          0
       0

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

total        3      0.01       0.00          0          2          0
       0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 45

Rows Row Source Operation
------- ---------------------------------------------------

      0  FILTER  (cr=0 pr=0 pw=0 time=7 us)
      0   TABLE ACCESS FULL OBJECTS (cr=0 pr=0 pw=0 time=0 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00

select *
from
objects where object_name = 'T0406'

call count cpu elapsed disk query current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          1          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2      0.01       0.00          0        644          0
       1

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

total        4      0.01       0.00          0        645          0
       1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 45

As you can see above, the "1=0" does not do any I/O, but the other one does. I'm not what your customer is seeing, but it sounds like some tuning is needed? Why are they trying to lock no rows for update?

Regards,

Steve Received on Fri Apr 07 2006 - 20:46:02 CDT