Re: how to tune up this query? (Long post with example)

"Norman Dunbar" <Norman.Dunbar_at_lfs.co.uk> wrote in message news:E2F6A70FE45242488C865C3BC1245DA7033D51E5_at_lnewton.leeds.lfs.co.uk...
> Hi folks,
>
> Not withstanding Richard's comments on efficiency, you also get the
> wrong answers :o)

Its a bummer when that happens isn't it.

<snip>

> SELECT IDENT, A_NAME, priority FROM table_name
> WHERE stage = 'STAGE_1' AND state = 1
> AND ROWNUM <= 10
> ORDER BY priority ASC;
>
> Operation Object Name Rows Bytes Cost Object Node
> In/Out PStart PStop
> SELECT STATEMENT Hint=CHOOSE 5 K 80
>
> SORT ORDER BY 5 K 126 K 80
>
> COUNT STOPKEY
>
> TABLE ACCESS FULL TABLE_NAME 5 K 126 K 36
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;
>
> Operation Object Name Rows Bytes Cost Object Node
> In/Out PStart PStop
> SELECT STATEMENT Hint=CHOOSE 5 K 80
>
> COUNT STOPKEY
>
> VIEW 5 K 209 K 80
>
> SORT ORDER BY STOPKEY 5 K 126 K 80
>
> TABLE ACCESS FULL TABLE_NAME 5 K 126 K 36
this one does its FTS, sorts all the data by priority, then you select the first 10 rows from this ordered set. this usually is what you want, can you give me ten effectively random rows in sorted order is a kinda unusual request.

Cheers

--
Niall Litchfield
Oracle DBA
Audit Commission UK