Re: Query optimization on large table - Temporary tables ?

From: Robert Fazio <dbabob_at_hotmail.nospam.com>
Date: Sun, 16 Mar 2003 04:49:22 GMT
Message-ID: <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 - 05:49:22 CET