Hi Riyaj,
The case we encountered was not due to gathering statistics.
Vendor used index_desc hint to "derive" a result set.
There was a block corruption on the index and it was recreated with a
different name
while the DBA worked with Oracle Support to analyze the problem. Of
course, the index
hint was useless and the SQL produced unwanted results.
Regards,
- Kirti
--- Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx> wrote:
> 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.
>
> --
>
> Thanks
>
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA (ver 7.0 - 9i)
> Allocation & Assortment planning systems
> JCPenney
>
> 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.
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
