Re: "order by" performance behavior

"ewong" <ewong_at_mail.com> wrote in message news:1143343499.140674.323770_at_i40g2000cwc.googlegroups.com...
>I have a table "result" with 5M records. I am doing this query:
>
> select distinct project
> from result order by project;
>
> It returns 50 distinct projects in order. The problem is this query
> takes over 30 seconds to complete. The project column is indexed but
> explain plan shows that it's not picking up the index. 4M out of 5M of
> the project field has NULL values. I heard that NULL value are not
> indexed so I guess that's the reason oracle isn't picking up the index?
>
> So I tried this different query:
>
> select project from (
> select distinct project from result)
> order by project;
>
> And this query returns instanteously! According to explain plan oracle
> is using the index on the project column. So why is the difference?
> The query is generated by an app so I can't force it to use the 2nd
> query. Below are the two explain plans. I am using 9.2 EE on Solaris
> 10. Cost based. Table has full statistics. Thanks.
>
> -------------------------------
>
> select distinct project
> from result order by project; -- take 30 seconds --
>
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2128 Card=43
> Bytes=129)
> 1 0 SORT* (UNIQUE) (Cost=1715 Card=43 Bytes=129)
> :Q292625001
> 2 1 TABLE ACCESS* (FULL) OF 'RESULT' (Cost=504 Card=64752
> Bytes=19425675)
> :Q292625000
> 1 PARALLEL_TO_SERIAL SELECT DISTINCT C0 C0 FROM :Q292625000 ORDER BY
> C0
> 2 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */
> A1."PROJECT" C0 FROM "RESULT" PX_GR
>
> --------------------------------
>
> select project from (
> select distinct project from result)
> order by project; -- returns instanteously --
>
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3098 Card=43
> Bytes=2236)
> 1 0 VIEW (Cost=3098 Card=43 Bytes=2236)
> 2 1 SORT (UNIQUE) (Cost=3098 Card=43 Bytes=129)
> 3 2 BITMAP INDEX (FAST FULL SCAN) OF 'IDX_RESULT_PROJECT'
>
> ------------------------------
>

I think you may have to put this down to a defect in the optimizer. It is easy to reproduce your problem, and when I do, the 10053 trace for both plans shows the optimizer recognising the bitmap plan as the Best plan at one point - and then doing the parallel table scan anyway for the first query.

The problem may be related to the code path that pushes the group by into the order by (note that Oracle only needs to do one sort - when we HAVE to tell to there are two operations if we want the guarantee the correct sorted order). It may be related to the code that handles the way that in-line views are handled -= notice that the plan works when you instantiate the view.

You seem to have a parallel table, but serial index - is it safe to change the table definition so that it is not parallel-enabled ?

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html