Re: Question about using a pl/sql table to improve performance of a query

"Kenny Yu" <KennyYu_at_attbi.bom> wrote in message news:<KBXK8.19339$861.6874617_at_typhoon1.we.ipsvc.net>...
> Set operations are slow, not optimized.
>

Actually set operators can be faster when the tables involved are comparable in size. If the size of the row sources are very different nested loop may be faster than anything else.

On the original question, I assume you process records through a cursor. PL/SQL table can make it faster since you read the table once and cache it in memory. This avoids repeated access to the table. But if you can write a single query using set operators or other types of joins you will achieve the same result. And it's a lot simpler.

> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3CFB8D8E.B1B1C4FA_at_exesolutions.com...
> > Ryan Gaffuri wrote:
> >
> > > Also, are there times when loading a table into a pl/sql table in
> > > memory might speed up performance of a select statement that is based
> > > on many join conditions? For example, I need to indentify duplicate
> > > data in two tables based on about 30 of the 40 columns in the two
> > > tables. Im currently doing a join on each of these 30 columns to look
> > > for the duplicates. This is really slow. Would using a pl/sql table
> > > improve performance?
> >
> > Why a join?
> >
> > Why not set operators such as INTERSECT or MINUS?
> >
> > Daniel Morgan
> >
Received on Tue Jun 04 2002 - 13:45:26 CDT