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

From: Joe Weinstein <joeNOSPAM_at_bea.com>
Date: Fri, 30 Jan 2004 15:05:05 -0800
Message-ID: <401ae318$1@news.beasys.com>

Hari Vetsa wrote:

> Looks like you are already aware in your C++ program that you have an
> empty list. By definition no records will be retrieved from RDBMS if
> you have an empty list on the right hand side of "IN" operator.
> 
> why bother asking Oracle RDBMS what you already know. just skip DB
> query and progress along.
> 
> Makes sense?

I've seen more than one application (including some DBMS vendor's JDBC driver) do this with SQL queries they do not control, to get the result set metadata. (I am not necessarily defending this, just reporting that it is done). Also the general question is still apt, because the application/querier may not know that an assembled query may include a qualifier which is easily detectable in the DBMS at compiletime as like "where 1 = 0". All DBMSes I know do have at least partially applicable capability of not searching/touching the real data when the query search arguments are detectably unsatisfiable a priori. The query will still be answered, and will include the metadata, even when no rows satisfy.
Joe Weinstein at BEA

> 
> 
> 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 - 17:05:05 CST