Re: Optimizing to display any rows

twofourblue_at_gmail.com wrote:
> 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

As Sybrand suggests the FIRST_ROWS hint ... but I'd recommend that before you go that route you provide the following:

1. Oracle platform, operating system, and version number
2. Number of simultaneously connected users accessing these resources
3. The DDL
4. The Explain Plan

Tuning is best done when facts are available to be analyzed.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)