FIRST_ROW hint
819566May 3 2011 — edited May 3 2011Hi,
I have created a view by Joining two tables
select c1,c2,c3,c4,c5
from t1,t2
where t1.id=t2.id
and t1.id2=t2.id2
order by c2
If I query the view it is taking more than 10 mins to return the results, t1 is a table having more than 20 millions of records.
If I gave the FIRST_ROWS hints it is returning result with in fraction of a second, my understanding is the first_row hint is used when we want to first n number of rows faster.
If I remove the order by clause the query is taking more to execute with the first_row hint, the explain show when I use first_row hint it is using the Index but with out using first_row it is going for entire table scan.
Can you please let me know whether first_row hint really helps when I want to select the entire result set into a cursor and process the same?