"order by" performance behavior

From: ewong <ewong_at_mail.com>
Date: 25 Mar 2006 19:24:59 -0800
Message-ID: <1143343499.140674.323770@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'

------------------------------

Received on Sat Mar 25 2006 - 21:24:59 CST