Home » RDBMS Server » Performance Tuning » Slow Order by, Index needed? (10g)
icon5.gif  Slow Order by, Index needed? [message #401504] Tue, 05 May 2009 05:35 Go to next message
filiprus
Messages: 9
Registered: February 2009
Location: Singapore
Junior Member
Hello Dear Oracle Users and Pros,

My issue is one of performance, sorting and pagination - let me briefly explain.

The table: TMAIL

MSGID NUMBER (Primary Key)
Subject
From
Sender
Attachments
DT DATE (date contains date and time)

This table contains 12'000 records and will containd 500k+ records. I am trying to select the last 20 messages and then go back (ie the 20 before that etc - pagination) and I achieve that with a construct like this one: SELECT * FROM (SELECT [fields]
FROM TMAIL WHERE SENDER IS NOT NULL ORDER BY DT DESC) WHERE ROWNUM <=20

This works, but the issue is that it's too slow (appears to require a full table scan) and I'm sure something can be done to improve this.

My question is - what can be done to speed things up?

I'm considering:

1) Indeices, tried CREATE INDEX MAILNDX ON TMAIL (MSGID,SENDER,DT) and it doesn't change much

2) is it possible to keep this table sorted by DT in the database?

3) changing the SQL?

I will be most obliged for your help on this.

Best Regards,

Filip
Re: Slow Order by, Index needed? [message #401509 is a reply to message #401504] Tue, 05 May 2009 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the required and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel

[Updated on: Tue, 05 May 2009 05:53]

Report message to a moderator

Re: Slow Order by, Index needed? [message #401695 is a reply to message #401509] Wed, 06 May 2009 03:27 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The SENDER IS NOT NULL clause makes it difficult.

If you change that clause to
NVL2(SENDER, NULL, 1) = 1

and then create a function based index on
NVL2(SENDER, NULL, 1), DT


Then you should be able to make it do an index range scan with a NOSORT operatiion in the ORDER BY.

Ross Leishman
Previous Topic: UPDATE query with JOIN
Next Topic: Use of Btree in Oracle: Concept
Goto Forum:
  


Current Time: Sat Dec 03 22:19:13 CST 2016

Total time taken to generate the page: 0.06053 seconds