Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambrium.nl!feed.tweaknews.nl!postnews.google.com!o36g2000yqh.googlegroups.com!not-for-mail From: Mark D Powell Newsgroups: comp.databases.oracle.server Subject: Re: Join with a global temp table Date: Tue, 3 Mar 2009 06:26:54 -0800 (PST) Organization: http://groups.google.com Lines: 47 Message-ID: References: <49aac3b4$0$31344$9b4e6d93@newsspool4.arcor-online.net> <71019tFhd7miU1@mid.individual.net> NNTP-Posting-Host: 192.85.50.11 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1236090415 5415 127.0.0.1 (3 Mar 2009 14:26:55 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 3 Mar 2009 14:26:55 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: o36g2000yqh.googlegroups.com; posting-host=192.85.50.11; posting-account=qJFqbQkAAACYQSLN0-cvP6ydkRfuOu6u User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Mar 1, 1:08=A0pm, 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 th= e > > 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 t= he > > temp table quite much (factor 2 - 3). > > Difficult to answer. =A0I suggest, you post Oracle version along with the > table DDL and the join you are doing. =A0An execution plan would also be > helpful. > > Cheers > > =A0 =A0 =A0 =A0 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 --