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

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 Received on Fri Apr 07 2006 - 17:34:08 CDT