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

"Wolfram Roesler" <wr_at_grp.de> wrote in message news:Xns94807C6718D20wrgrpde_at_130.133.1.4...

> 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?

It says it does a full table scan, but in some of the latest versions I suss it just fails the darn thing with 0 rows: smart enough to figure it will always fail. Like Telemachus' example shows.

Which wouldn't be difficult: in the absence of a column in a predicate, all you can do is compare two constants. That results in true or false and can be evaluated at parsing time.

So basically, the predicate in this case reduces to "always false". Which means "don't bother looking for it, you will NOT find it". In other cases (1=1) it maps to always true, in which case we need a FTS.

I'm assuming of course only ONE predicate. Not given any thought to multiples, but it shouldn't be much different except for the final FTS.

> 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.

I'd say 0=1. "column !=" anything is the same as "column NOT =" anything and will most likely cause a FTS: NOT almost always causes one. Indexes work by finding equality of a key to a value. Not inequality!

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam