How to speed up this query

From: Andy Rigby <andy_at_softap.co.uk>
Date: 15 Feb 2002 04:44:28 -0800
Message-ID: <56c32d01.0202150444.1d5ae2f@posting.google.com>

I have a table of approx 50,000 rows. My Visual C/ADO app generates large numbers of requests of this type - see below (the app requires 'pagefulls' of 12 rows so I use one of the standard 'top n' tricks) :

SELECT *
FROM
(SELECT * FROM TBL19 WHERE LRN>130345 ORDER BY LRN) WHERE ROWNUM<=12

The timings and I/O for this as reported by TRACE is excellent, i.e:

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      0.00       0.00          0          5          0  
       12

------- ------ -------- ---------- ---------- ---------- ----------

total        4      0.00       0.00          0          5          0  
       12

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25

Rows Row Source Operation

-------  ---------------------------------------------------
     12  COUNT STOPKEY 
     12   VIEW 
     12    TABLE ACCESS BY INDEX ROWID TBL19 
     12     INDEX RANGE SCAN (object id 4148)

********************************************************************************

However, if my app wants to organise its 'pages' by another column, the SELECT is more complex, and the timings are not good at all:

SELECT *
FROM
(SELECT * FROM TBL19 WHERE (ffcode > 'RLACKWFJ') OR (ffcode = 'RLACKWFJ' AND (LRN>148124)) ORDER BY ffcode,LRN) WHERE ROWNUM<=12

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      0.04       0.27        238        449          5  
       12

------- ------ -------- ---------- ---------- ---------- ----------

total        4      0.04       0.27        238        449          5  
       12

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25

This results in fairly jumpy movement around the screen in my app.

The "OR" is necessary to resolve duplicates on the FFCODE column, so effectively the rows are subsorted by LRN within FFCODE.

I have an index on LRN, also one on FFCODE, and (most important, I would have thought) one on FFCODE, LRN. I have analysed all tables and indexes. No improvement. Tried making the indexes unique. No improvement.

I have tried using index hints to force any of the above 3 indexes to be used, all resulted in slower timings than with the 'choose' method. Tried using the 'first_rows' hint on the inner and/or outer SELECT...no improvement.

I have also tried creating a view consisting of the entire table ordered by FFCODE,LRN and then selecting ROWNUM<=12 from that, thus avoiding the embedded SELECT - that turns out to be no better in performance terms.

I know that Oracle has a lot of work to do here, in that it must do the inner SELECT, sort the results and then do the outer SELECT. But I can't see why the first SELECT is so much faster.

Can anyone suggest a better method for this type of SQL statement or some other tuning tip?

Andy Received on Fri Feb 15 2002 - 06:44:28 CST