Re: Join with a global temp table

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 3 Mar 2009 06:26:54 -0800 (PST)
Message-ID: <b65f6f32-d314-41b9-b028-8430e3f484ce_at_o36g2000yqh.googlegroups.com>



On Mar 1, 1:08 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 01.03.2009 18:19, Chris Seidel wrote:
>
> > I want to join a global temp table (delete on commit, not more than 250.000
> > records) with a normal table (about 100 mio records).
>
> > To speed up the join - would it be better to create a primary key on the
> > join column of the temp table?
>
> > My tests show that it seems not to affect the join performance - but my test
> > system has only 200.000 records in the normal table.
>
> > I'm asking because the PK on the temp table slows down the inserts to the
> > temp table quite much (factor 2 - 3).
>
> Difficult to answer.  I suggest, you post Oracle version along with the
> table DDL and the join you are doing.  An execution plan would also be
> helpful.
>
> Cheers
>
>         robert

I agree the information Robert asked for would be helpful especially the explain plan.

The fact that the load of the temp table is slower if an index is created on it is not really the proper measurement. What you really want to measure is the total run time of the entire process. If load temp + join is greater than load temp with index + join then the index is beneficial.

Adding indexes to improve queries often slows mass insert processes but most people only measure the effect on the query. You should always strive to be aware of the total effect on your system for all changes. Not that easy a task, but it should be one of your goals.

HTH -- Mark D Powell -- Received on Tue Mar 03 2009 - 08:26:54 CST

Original text of this message