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

On Fri, 07 Apr 2006 19:21:20 -0700, Joe Weinstein <joeNOSPAM_at_bea.com> wrote:

>
>
>Mark Townsend 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
>>>
>>>
>> What is this query trying to do ?
>Hi, thanks. It is apparently a hackneyed attempt to verify
>"FOR UPDATE" syntax by executing it against multiple DBMSes,
>while using the addition of " WHERE (1=0)" as a generic
>means of altering any query so it doesn't do anything.
> I won't defend this stuff per se. I am just surprised that
>(if) the Oracle optimizer would miss this opportunity to
>avoid thrashing memory.
>Joe

Before 9i the optimizer, whether CBO or RBO, didn't perform almost no arithmetic optimization. It was common to use constructs like <indexed column> + 0 = literal
to make sure the index wasn't used.
In 9i CBO has been getting 'smarter' and such constructs will be optimized away.
Also adding 'where 1=0' is a common tric in applications which fire dynamic sql.
However, if you are using CBO, and the table has a PK, CBO will perform a FAST FULL SCAN instead of a FULL TABLE SCAN. My suspicions are

a) the database is pre 9i
b) it is using RBO instead of CBO
c) there is no PK index on the affected table.

You can hardly blame Oracle for your customer not upgrading to more modern technology.  

--
Sybrand Bakker, Senior Oracle DBA