Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Does "WHERE 0=1" require a full table scan?
"Telemachus" <Zaka_at_twibbles.99.net> wrote in message news:<flrSb.765$rb.52998_at_news.indigo.ie>...
> Interesting ...
>
> on a test table with a unique index I get
>
> SQL> select * from bungo where col1 < 10 and 1=0;
>
> no rows selected
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 FILTER
> 2 1 INDEX (RANGE SCAN) OF 'BUNGO_IDX' (UNIQUE)
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 0 consistent gets
> 0 physical reads
> 0 redo size
> 220 bytes sent via SQL*Net to client
> 368 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> SQL> select * from bungo where 1=0;
>
> no rows selected
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 FILTER
> 2 1 TABLE ACCESS (FULL) OF 'BUNGO'
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 0 consistent gets
> 0 physical reads
> 0 redo size
> 220 bytes sent via SQL*Net to client
> 368 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
> SQL> select count (*) from (select * from bungo);
>
> COUNT(*)
> ----------
> 31850
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (AGGREGATE)
> 2 1 TABLE ACCESS (FULL) OF 'BUNGO'
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 52 consistent gets
> 0 physical reads
> 0 redo size
> 381 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> which makes me believe that there's something in the optimizer that sees it
> anyway.
> "Wolfram Roesler" <wr_at_grp.de> wrote in message
> news:Xns94807C6718D20wrgrpde_at_130.133.1.4...
> > Hi,
> >
> > a simple question: For a query like "SELECT * FROM table WHERE 0=1",
> > does Oracle do a full table scan, or does it optimize the query away
> > and return nothing without accessing the table?
> >
> > The background for this question is, I'm writing a C++ function that
> > takes a column name and a list of strings and creates a WHERE clause
> > to search for these strings, like "column IN ('string 1','string 2',...).
> > In case of an empty list I want to create a WHERE clause that matches
> > nothing, and I'm pondering whether "WHERE 0=1" or "WHERE column!=column"
> > would be better.
> >
> > Thanks for your help
> > Wolfram Roesler
Telemachus, nice test. I had just tested with the same concept last week just using the "set timing on" comparison to explan plan and had observed that while the explan plan makes it look like Oracle has work to do to solve the query the actual runs when using 1 = 0 consumed only a few hundreds of a second to process simple queries and joins such as
select * from table_a, table_b, table_c where 1 = 0;
I ran my tests on both 8.1.7.4 and 9.2.0.4.