| Indexed columns and order by [message #372514] |
Fri, 16 February 2001 18:01  |
Hui
Messages: 1
Registered: February 2001
|
Junior Member |
|
|
I have an query returns 10K rows on a 200K rows table. select * from Table a where a.a=a a.b=b order by a.c desc. Data are uniformly distributed. An index is created on (a, b, c);
The problem is the query above took 3~4 seconds to return, whereas it will just take 10 ms if I change to order by a.c asc. Oracle would not let me to create an index with desc order, and it doesn't know to read index backward on desc!
Are any of you guys has a solution for this?
Thanks in advance.
PS, I have the optimizer mode set to first_rows.
|
|
|
|
| Re: Indexed columns and order by [message #372533 is a reply to message #372514] |
Tue, 20 February 2001 17:31  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Best to do an explain plan with both queries. In sqlplus (8i), set autotrace on; I have found that sometimes the presence of the order by causes an alternative index not to be used. Are there other indexes present. Ordering on C should not use your index on A,B,C for sorting if that is the order of the columns in the index. Also the setting "first rows" won't help to speed up anything, because the order by must be completed before you get a single row back.
Also make sure that the tables are freshly analyzed. If you analyzed the tables when they were empty, Oracle may opt not to use the indexes because it thinks the quantity of data is 0 or close to 0.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
