We have an application that in case the table has tons of rows, it will only return a relatively small set of data (say 1000 rows but it is configurable) for the user to work with.

I know we can limit the number of rows returned by taking advantage of rownum, but don't know if doing this could speed up the query execution, i.e. if CBO will take advantage of the smaller result set and choose a different execution plan. (I heard SQL Server's optimizer will do, that's why I'd like to check).

Also, can we set this option globally? (so when you write individual query, you don't have to always specify where rownum < 1000 etc)

Thanks!