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

From: Joe Weinstein <joeNOSPAM_at_bea.com>
Date: Sun, 09 Apr 2006 08:13:54 -0700
Message-ID: <443924B2.8060402@bea.com>

Mark Townsend wrote:

> Joe Weinstein wrote:

>> Mark Townsend wrote:
>>
>>> Joe Weinstein wrote:
>>>
>>>> Response from a TAR (SR).
>>>
>>> TAR number ? I would enjoy shaking some trees...
>>
>> Thanks: 5313790.993 Do let me know what you find out.

>
> I note that the analyst's last update (as of yesterday) was that he
> would check with Dev as to what was actually happening. I have assisted
> in that process.
>
> For my own information - who made the comment about "Oracle won't invest
> the serious work required to change their query engine to optimize such
> queries."

Me. My interpretation of the conversation, which occurred after the last SR entry. The person I talked to did not say this. I stated my understanding of the facts that there was no user benefit to the table scan, and that the DBMS could know it did not need to do it, but that engineering had told the support person that they indeed do a table scan, and that they would not change it. I replied, understanding that changing the optimizer is a nontrivial  concern, and that I understood that Oracle would not likely invest any such effort to optimize such a query. The support person did not disagree. Mark, thank you for your diligence. I will get to the bottom of this with help like yours. I will ask the customer to run the tracing that shows the full actual query plan, and verify the DBMS version involved. They did state that their DBA had observed real serious performance problems traced to that query, and had somehow altered the indexing of that table to alleviate the problem.
Joe Received on Sun Apr 09 2006 - 10:13:54 CDT