Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer issue

Re: Optimizer issue

From: Riyaj Shamsudeen <>
Date: Fri, 19 May 2006 10:32:48 -0500
Message-ID: <>

Quoting Manjula:

 >>and some data would not be pulled up in the application. Is that possible?

Huh ? Generally not possible. But, I know of one exception. I think Kirti Deshpande encountered this: One of the application was relying upon an index order to return the rows in a specific order. Optimizer selected a different index, after collecting statistics, affecting the functionality. Specifically, if the code is also interested in top N rows with this strategy, then incorrect (from the application point of view) will be pulled in to the application, causing user irritation.

But, the problem here is that incorrect and potentially treacherous strategy. Unless 'order by' clause is used, RDBMS will return rows anyway it sees fit. So, you might want to ask them to provide more details as to exactly why collecting statistics will not work and ask for code/strategy that will break.



Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA (ver 7.0 - 9i)
Allocation & Assortment planning systems

Manjula Krishnan wrote:

> I have an application on Oracle 9i. The database (designed by the
> vendor) was set with optimizer_mode=CHOOSE. A week after we went live
> with the application, performance started degrading. I discovered that
> statistics were missing and built them. Immediately the application
> problems were resolved.
> The vendor claims that with the statistics, it would not use the right
> indexes and some data would not be pulled up in the application. Is
> that possible?
> Thanks,
> Manjula

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. --

Received on Fri May 19 2006 - 10:32:48 CDT

Original text of this message