| Order by with Nulls -- Performance [message #312648] |
Wed, 09 April 2008 09:24  |
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
|
Senior Member |
|
|
I have a table with about 100,000 rows.
I am having an issue with one query which contains an order by with 4 columns.
select blah,blah,blah,a,b,c,d from table_1 order by
a,b,c,d
Columns a,b will never be null.
Column c will be null in about 50% of the rows
Column d will be about 75% of the time.
I have played with creating the index in almost all variations I could think of, including function based indexes, all to no avail.
If c,d are removed from the order by clause and an index created with a,b the query completes in .2 seconds.
With c,d in the order by the query completes in 9 seconds.
Any ideas how to optimize this ?
Thanks.
Marc
|
|
|
|
|
|
| Re: Order by with Nulls -- Performance [message #312663 is a reply to message #312650] |
Wed, 09 April 2008 10:05  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I thought the null values were held in composite indexes.
Regardless - try creating a function based index on A,B,NVL(C,' '),NVL(D,' ') and then changing your query to ORDER BY A,B,NVL(C,' '),NVL(D,' ')
|
|
|
|
| Re: Order by with Nulls -- Performance [message #312844 is a reply to message #312663] |
Wed, 09 April 2008 22:06 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Just ran a test locally. JR's technique does work, but I needed to add a hint to encourage the index use.
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2534 | 63350 | 859 (1)| 00:00:11 |
| 1 | TABLE ACCESS BY INDEX ROWID| ROSS | 2534 | 63350 | 859 (1)| 00:00:11 |
| 2 | INDEX FULL SCAN | ROSS2 | 2534 | | 14 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
9 rows selected.
1 select /*+index(ross)*/ *
2 from ross
3* order by table_name, nvl(num_rows,0)
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
