Re: Oracle scans table with "WHERE (1=0)"?

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 08 Apr 2006 10:18:21 -0700
Message-ID: <1144516696.552488@yasure.drizzle.com>

Joe Weinstein wrote:
>
>
> Joe Weinstein wrote:
>

>>
>> Hi all. I had a customer report that the query below
>> scans the table. Can that be true, that Oracle doesn't
>> realize during construction of the query plan that
>> the search argument will never be satisfied?
>>
>> SELECT 1 FROM ACTIVITY_ENTRY WHERE (1=0) FOR UPDATE
>>
>> The customer said:
>>
>> "This query ends up scanning a very large table of ours and
>> exceeds a timeout value. Our DBA made some changes to an
>> index to temporarily workaround the issue, but we want to
>> understand why this is happening."
>>
>> Thanks,
>> Joe weinstein at BEA Systems

>
> Hi. I have gotten official (if only verbal so far) confirmation
> that yes, the DBMS *will* do a table scan, presumably checking
> for each row whether 1 = 0, even if the DBMS knows a priori
> that it never will. There is zero value in this behavior, but
> Oracle won't invest the serious work required to change their
> query engine to optimize such queries.
> Joe

There is a very simple solution provided by Oracle.

Take a look at the DBMS_WARNING built-in package. A demo of its capabilities can be found in Morgan's Library at www.psoug.org.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Sat Apr 08 2006 - 12:18:21 CDT