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

Jonathan Lewis wrote:

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

Response from a TAR (SR).

> 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.
>
>
Received on Sat Apr 08 2006 - 15:19:06 CDT