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

From: Joe Weinstein <joeNOSPAM_at_bea.com>
Date: Sat, 08 Apr 2006 13:21:12 -0700
Message-ID: <44381B38.1090801@bea.com>

Sybrand Bakker wrote:

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

No blame, just seeking information. I'll verify your suspicions, and thanks for helping.
Joe

>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Sat Apr 08 2006 - 15:21:12 CDT