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

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0206051405.72a24a65_at_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?

Yeah, if you have huge unused memory on a server. If you do, you Oracle Server is not tuned and in an urgent need of a good DBA.

Actually, you need to tune your query first. Starting with strange notion that there are duplicates in two tables. Why are there 2 tables first of all?

Check your execution plan. If one of the tables is quite small, you can use hash join instead of nested loops. You might need to increase session hash_area_size to use hash join efficiently.

> > > >
> > > > 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.

One more reason to suspect that server is not tuned. Try timing for intersect with increased session sort_area_size. The difference might surprise you.

One more reason not to use pl/sql tables here: you would need to debug such a program long time. Or may be this is a reason: job security, kinda. Received on Thu Jun 06 2002 - 14:13:46 CDT