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

"Joe Weinstein" <joeNOSPAM_at_bea.com> wrote in message news:4436e90b_at_news.bea.com...
>
>
> 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
>

Where did the "official" confirmation come from ? Oracle has been able to detect the contradiction since at least 7.3, probably 7.2, and doesn't do the scan. The execution plan shows the strategy that would be used if the filter were true, but the run-time engine detects that the filter is false and terminates that branch of the execution plan.

The presence of the update makes no difference.

It would be useful if you could get a copy of the actual query that is demonstrating the problem, and the resulting execution plan, just in case there is something about the query that no-one has mentioned that is the true cause of excess I/O.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html