Re: SORT ORDER BY elimination

Dear Cary,

Thank you for your reply, I do have composite index to support primary key on cols in exactly the same order. And when I supply list of values directly in the in clause optimizer does chooses to skip ORDER BY step, but when I supply list of values from subquery than I think that optimizer doesn't have enough information to choose exactly the same plan, but I still can't find what kind of information should I supply to optimizer . First thing that I have tried was the cardinality hint, but that's have no affect on optimizer's decision. Next thing that I'll try is to analyze event 10052 trace file.

On Fri, 22 Oct 2004 11:40:41 -0500, Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> I think the optimizer would skip the SORT ORDER BY step if you had a
> composite index on (time, shop_id, product_id) in that order. (It has to be
> the same order as the columns are listed in the ORDER BY.)
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com

> order by time, shop_id, product_id
> constraint sales_pk primary key (time,shop_id,product_id)

-- 
  Edgar
--
http://www.freelists.org/webpage/oracle-l