Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SORT ORDER BY elimination

Re: SORT ORDER BY elimination

From: Edgar Chupit <chupit_at_gmail.com>
Date: Fri, 22 Oct 2004 20:02:43 +0300
Message-ID: <a8f0771c041022100234a0eba7@mail.gmail.com>


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
Received on Fri Oct 22 2004 - 11:58:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US