Re: How to speed up this query

andy_at_softap.co.uk (Andy Rigby) wrote in message news:<56c32d01.0202180206.577c31d7_at_posting.google.com>...
> 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
>
>

I think you can try the hint "FIRST_ROWS". On my 8.1.7 database, with this hint, the optimizer chose "index full scan" instead of "full table scan" + "sort". However, on a 8.1.6 database, I had to add a "Not Null" constraint on the ffcode column to make it work.

hth,
Mike  

>
>
>
>
> "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 Wed Feb 20 2002 - 15:25:13 CST