Home » RDBMS Server » Performance Tuning » Order by with Nulls -- Performance (Oracle 9i)
Order by with Nulls -- Performance [message #312648] Wed, 09 April 2008 09:24 Go to next message
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 #312650 is a reply to message #312648] Wed, 09 April 2008 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Because NULL are not held in indexes, selecting against C or D forces Full Table Scan & I don't think there is any way around it.
Re: Order by with Nulls -- Performance [message #312663 is a reply to message #312650] Wed, 09 April 2008 10:05 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Cursors not closed at the database level
Next Topic: How to reduce the execution timing of SQL Query
Goto Forum:
  


Current Time: Tue Dec 06 14:03:05 CST 2016

Total time taken to generate the page: 0.12159 seconds