Re: Optimizing union based view

Hi Charles,

> > > SELECT
> > >   *
> > > FROM
> > > (SELECT /*+ INDEX(T1) */
> > >   C1,
> > >   RECNO
> > > FROM
> > >   T1
> > > UNION ALL
> > > SELECT /*+ INDEX(T1OLD) */
> > >   C1,
> > >   RECNO
> > > FROM
> > >   T1OLD
> > > ORDER BY
> > >   RECNO)
> > > WHERE
> > >   RECNO>=250000
> > >   AND ROWNUM<=1;

> I noticed that you dropped the AND ROWNUM<=1 restriction.  That
> restriction does help reduce the amount of time, if you know that the
> user is only interested in one row.  But, without the RECNO
> retriction, Oracle still needs to perform the UNION ALL on the two
> tables in order to sort the rows by RECNO to determine the first row.

I had to remove the ROWNUM restriction because the application sometimes fetches more records. But after understanding your comments about the possible optimization I've finally managed it to get everything running with a passable performance.

Thank you!

Mathias Received on Mon Mar 10 2008 - 08:17:59 CDT