For 1 use bind variables.
2. What version of Oracle?
3. Have you analyzed the tables and indexes?
4. For the 2nd query what is the execution plan? (clearly it is using more
IO)
Jim
"Andy Rigby" <andy_at_softap.co.uk> wrote in message
news:56c32d01.0202150444.1d5ae2f_at_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 - 08:32:13 CST
