I have a question on fetch first x rows. Does this function execute after all the joins have been completed for all the rows in a table or does it return the result as soon as it hits the number ? eg: Table a has 100k rows table b has 72k rows
query is a inner join b fetch first 1000 rows.
will it show the result as soon as it hits 1000 rows after join or does it join all the rows before it only shows a portion of the results?
It's not really possible to answer with any detail without an execution plan.
I think to answer the core of your question, the optimizer will try to end early. Whether it can do so without reading every row in the result set or not depends on your query.
select id from mytable fetch first 1000 rows only
Will likely give you 1000 rows from mytable in no particular order with no preference as to which ones were chosen.
select id, col1 from mytable order by col1 fetch first 1000 rows only
Whether or not the optimizer has to do a full table scan and touch every row or not will depend. Is there an index on col1? If so, it may be able to scan that and get only the top 1000 rows and stop.
