Re: Does "WHERE 0=1" require a full table scan?

From: Telemachus <Zaka_at_twibbles.99.net>
Date: Fri, 30 Jan 2004 11:36:43 -0000
Message-ID: <flrSb.765$rb.52998@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
Received on Fri Jan 30 2004 - 05:36:43 CST