Re: How to speed up this query

Jim

1. Bind variables. Agreed and I probably will later. However, these timings are bad just executing these statements from the SQLPLUS command prompt.
2. Version is 8.1.6. 'compatible' is set to 8.1.6
3. All tables and indexes fully analyzed.
4. Execution plan for the slower statement is:

OPERATN            OPTS                 OPTIMIZE              COST
------------------ -------------------- --------------- ----------
  SELECT STATEMENT                               CHOOSE        495
             COUNT              STOPKEY
              VIEW                                             495
              SORT     ORDER BY STOPKEY                        495
      TABLE ACCESS                 FULL        ANALYZED         44

As a further piece of information, I tried simplifying the select statement and discovered that this simpler one takes just as long:

SELECT * FROM
(
SELECT * FROM tbl19 where
(ffcode>'RLACKWFJ')
ORDER BY ffcode)
WHERE ROWNUM<=12

The problem is obviously the TABLE ACCESS FULL as shown in the explain plan. Trouble is, I've tried all the index hints I can and the performance still does not improve.

Andy

"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
> >
> > 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 Mon Feb 18 2002 - 04:06:22 CST