Optimizing to display any rows

I have an interface to an Oracle database that displays data in a grid.  The grid displays the first N rows returned by a view. The view cross-references 8 tables, all joins are based on primary keys. Some of the tables have a considerable amount of data (> .5M records).

Unfortunately, a SELECT * on the view takes about 30 seconds and therefore the interface is much too slow. In most cases users will only want to see a few hundred records, but in some cases will want to see all data.

Is there any way to optimize my query so that if the user only wants to see 50 records, Oracle will quickly return the first 50 results without having to wait for the entire query to execute? Using select * from my_view where rownum < 100 doesn't work.

Thanks

twofourblue Received on Mon Jan 09 2006 - 11:28:16 CST