Which was my point previous on the thread,,, that Susan had to use the outer
select for the rownum pull ... this was covered in depth on one of the
boards last year....
She was right. Improvement I can possibly see is analytic but as I said a
FTS is on the cards . I'll have a look.
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
news:3e394823$0$239$ed9e5944_at_reading.news.pipex.net...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3e3943f6$0$230$ed9e5944_at_reading.news.pipex.net...
> > > SELECT IDENT, A_NAME, priority FROM table_name
> > > WHERE stage = 'STAGE_1' AND state = 1
> > > AND ROWNUM <= 10
> > > ORDER BY priority ASC;
> <SNIP>
> > This one does a FTS, filters for the first ten rows and then orders
these
> by
> > priority. So what you are getting is in effect the first ten rows in the
> > heap ordered by priority.
> >
> > > SELECT IDENT, A_NAME, priority FROM
> > > (SELECT IDENT, A_NAME, priority FROM table_name
> > > WHERE stage = 'STAGE_1' AND state = 1 ORDER BY priority ASC)
> > > WHERE ROWNUM <= 10;
> <snip>
> > this one does its FTS, sorts all the data by priority, then you select
the
> > first 10 rows from this ordered set.
>
> I meant to say that you can see this from the stats
>
> <snip table creation and population>
>
> SQL>
> SQL> set autotrace on explain statistics;
> SQL>
> SQL> SELECT IDENT, A_NAME, priority FROM tab1
> 2 WHERE stage = 'STAGE_1' AND state = 1
> 3 AND ROWNUM <= 10
> 4 ORDER BY priority ASC;
>
> IDENT A_NAME PRIORITY
> ---------- ------------------------------ ----------
> 1 NAME_1 1
> 11 NAME_11 11
> 21 NAME_21 21
> 31 NAME_31 31
> 41 NAME_41 41
> 51 NAME_51 51
> 61 NAME_61 61
> 71 NAME_71 71
> 81 NAME_81 81
> 91 NAME_91 91
>
> 10 rows selected.
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=10 Bytes=130
> 000)
>
> 1 0 SORT (ORDER BY) (Cost=79 Card=10 Bytes=130000)
> 2 1 COUNT (STOPKEY)
> 3 2 TABLE ACCESS (FULL) OF 'TAB1' (Cost=49 Card=5000 Bytes
> =130000)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 4 consistent gets
> 0 physical reads
> 0 redo size
> 694 bytes sent via SQL*Net to client
> 503 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 10 rows processed
>
> SQL>
> SQL> SELECT IDENT, A_NAME, priority FROM
> 2 (SELECT IDENT, A_NAME, priority FROM tab1
> 3 WHERE stage = 'STAGE_1' AND state = 1 ORDER BY priority ASC)
> 4 WHERE ROWNUM <= 10;
>
> IDENT A_NAME PRIORITY
> ---------- ------------------------------ ----------
> 1 NAME_1 1
> 1001 NAME_1001 1
> 2001 NAME_2001 1
> 5001 NAME_5001 1
> 4001 NAME_4001 1
> 3001 NAME_3001 1
> 9001 NAME_9001 1
> 8001 NAME_8001 1
> 7001 NAME_7001 1
> 6001 NAME_6001 1
>
> 10 rows selected.
>
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=10 Bytes=215
> 000)
>
> 1 0 COUNT (STOPKEY)
> 2 1 VIEW (Cost=79 Card=5000 Bytes=215000)
> 3 2 SORT (ORDER BY STOPKEY) (Cost=79 Card=5000 Bytes=13000
> 0)
>
> 4 3 TABLE ACCESS (FULL) OF 'TAB1' (Cost=49 Card=5000 Byt
> es=130000)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 497 consistent gets
> 0 physical reads
> 0 redo size
> 694 bytes sent via SQL*Net to client
> 503 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 10 rows processed
>
> SQL>
> SQL> SELECT blocks FROM user_tables WHERE table_name='TAB1';
>
> BLOCKS
> ----------
> 493
>
> If you look at the consistent gets you will see that the first query
manages
> to only visit 4 blocks or thereabouts to see what the lowest priority
> records are whereas as the second one really does visit the whole table.
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>
Received on Thu Jan 30 2003 - 11:18:46 CST
