Hi there Ray,

Haven't "seen" you in a while; how are you doing? The answer to your trailing question is "yes", a whole table scan will be executed to fetch the first n rows because of the order by. Without the order by, an entire table scan will not execute, and the query will indeed return the first n rows and then stop. Query #2 is much more cheap and will run noticeably faster, assuming a lot of data in the table. For a small table, I doubt you'd see much of a difference.

Have a great weekend!

Nick

-------------- Original message --------------
From: "RLam" <db2udbdba-ezmlmshield-x21055232.[Email address protected]

> hi gurus
>
> Is query 1 more expensive?
>
> 1. select * from TABLE1 order by COL1 desc, COL2 FETCH FIRST n ROWS ONLY
>
> 2. select * from TABLE1 FETCH FIRST n ROWS ONLY
>
> Let's say COL1 is not indexed, does it mean DB2 will do a whole table scan
> and sort the results only to return the first "n" rows?
>
> Cheers
> Ray
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>

DB2 & UDB email list listserv db2-l LazyDBA home page