Re: How to speed up this query

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<MZ8b8.186$Ot2.67120_at_sccrnsc01>...
> 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
> >

Are u sure this is a "good solution"? Try different LRN, your reponse time will vary dramatically.
Assume you have an index tbl19_lrn_idx created on tbl19(lrn),

try this

select /*+ INDEX_DESC (tbl19 tbl19_lrn_idx) */ * from tab19 where rownum <12 and lrn>130345;

Try the same trick for your second case.

> > 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 - 16:24:04 CST