Re: Query optimization on large table - Temporary tables ?

test
"Robert Fazio" <dbabob_at_hotmail.nospam.com> ¼¶¼g©ó¶l¥ó·s»D :Xns933FF2567D5A2dbabobhotmailcom_at_204.127.204.17...
> afilonov_at_yahoo.com (Alex Filonov) wrote in
> news:336da121.0303040901.2235a165_at_posting.google.com:
>
> > marcosc_at_gmx.de (Marcos Pinto) wrote in message
> > news:<2ed49883.0303030414.3f71d2a4_at_posting.google.com>...
> >> Hi all,
> >>
> >> I happened to receive a big PL/SQL code to mantain, that does
> >> something like the following:
> >>
> >> - Loop through a long list of customer records
> >> -- For each customer record, copy a few records from a huge table
> >> (100 million records aprox.) to a temporary table (not Oracle 8i's
> >> temp table, just a regular table)
> >> -- Run two heavy queries on these records
> >> -- Write some stuff to another table, if necessary
> >>
> >> Therefore, my questions are:
> >>
> >> - Is there a point in copying the records from the big table to a
> >> temporary one ? These records are accessed through primary key.
>
> I would suggest trying both, but I suspect that the copy to temp table
> will be slower. Depending on the query, it might be more efficient to
> do a hash join against the temp table over access against the larger
> table via PK. Accessing the records via the PK is extremely efficient,
> and I am guessing that you are just wasting cycles in the copy.
>
> If on the other hand you have changes that need to be made to this
> temporary data, there may, and I stress may, be useful reasons to do
> this.
>
> >
> > There is no point. This is a clear case for PL/SQL tables. That is,
> > if you are sure that only a few records are used.
>
> Not sure this is a good idea either, PL/SQL tables can use a lot of
> memory. If this is just a single maintanence job, then that probably
> isn't an issue, but be careful with this. If this session lingers after
> this large copy make sure that you use the
> dbms_session.free_unused_user_memory procedure.
>
> >
> >> - Would using Oracle 8i temporary tables instead of regular tables
> >> improve the performance somehow ?
>
> If there was a need at all to copy them to another table, yes temp
> tables would help since they don't generate any redo.
>
> >>
> >> Thanks in advance,
> >>
> >> Marcos Pinto
> >> marcosc_at_gmx.de
> >
>
> I would suggest running tests against the following three scenarios:
>
> 1) No temp tables at all.
> 2) PL/SQL tables.
> 3) Using Oracle Temp tables.
>
> I placed them in my suspected order of performance, but since I have no
> knowledge at all of what you are doing, it is difficult to say.
Received on Sun Mar 16 2003 - 06:17:03 CET