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

stevedhoward_at_gmail.com wrote:

> 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
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>
>
> 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

Hi, and thanks for replying. I will try to find out the DBMS version. It's a hackneyed attempt to verify "FOR UPDATE" syntax by sending it to various DBMS types, combined with the use of " WHERE (1=0)" as a generic add-on to ensure the DBMS doesn't really do anything. I am actually suspicious because the customer reported that their DBA did something with indexes to make the problem better somehow, which doesn't make naive sense to me for a query that asks for no columns, but this same DBA did suggest the oracle-specific "WHERE ROWNUM < 0" as an alternative that would ensure no data was read... Joe Received on Fri Apr 07 2006 - 21:26:15 CDT