Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Question about using a pl/sql table to improve performance of a query

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

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 5 Jun 2002 15:05:35 -0700
Message-ID: <1efdad5b.0206051405.72a24a65@posting.google.com>


dyou98_at_aol.com (D.Y.) wrote in message news:<f369a0eb.0206041045.3db020e_at_posting.google.com>...
> "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
> > >

I had timing on for intersect and it was much slower than a join. Received on Wed Jun 05 2002 - 17:05:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US