 Slow Order by, Index needed? [message #401504] |
Tue, 05 May 2009 05:35  |
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 #401695 is a reply to message #401509] |
Wed, 06 May 2009 03:27  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
