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

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

a simple answer: no, it doesn't do any access to 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.

But, you'll kill your production database with SQL like that. As soon as several thousand of these non-shareable (unique) queries hit the shared pool, you will start getting angry calls from your customer or end users. What you need is a way to make all these queries look alike.
Now, there's basically two ways to do it, and both will also remove the need for 'or 1=0':
1. create a PL/SQL function that gets a varray of strings as parameter and returns it. Then your query will look like this: select ... from ...
where column in (

   select column_value from table(your_function(:your_array)));

Now, that requires a bit of reading, and I'm not sure whether you can pass bind varrays from C. Try searching asktom.oracle.com for 'selecting from a function'

2. Create an Oracle Text index on "column". Your query will look like this:
select ... from ...
where contains(column,:search_criteria); search_criteria is a normal string that concatenates your strings with 'OR' or '|' between them.
Oracle Text is shipped with Oracle 9i, so you (or your customer) won't have any additional license cost.
This is not exactly equivalent to your original query, as it does a case- and umlaut-insensitive search, but allows you to search much like in Google, plus some extras like 'near', 'about', 'sounds like' (soundex), and much more, AND it will outperform any other solution any given day.

HTH
> Thanks for your help
> Wolfram Roesler

You are welcome,
Vladimir Andreev
Semantec GmbH Received on Fri Jan 30 2004 - 09:51:38 CST