Re: Join with a global temp table

From: Robert Klemme <>
Date: Tue, 03 Mar 2009 18:35:28 +0100
Message-ID: <>

On 03.03.2009 15:26, Mark D Powell wrote:
> On Mar 1, 1:08 pm, Robert Klemme <> 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.

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

Well, it all depends. If the system is batch only, then it seems reasonable to measure both with equal priority and just go for the overall fastest solution. If one of the parts is "interactive" then that probably needs higher priority in optimizing. Since often you cannot have both (insert and select) fast.

But I completely agree, the whole picture must be taken into account. It's nice how such seemingly simply examples can exhibit a whole lot of complexity if looked at a minute or two longer. :-)

Kind regards

        robert Received on Tue Mar 03 2009 - 11:35:28 CST

Original text of this message