Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Tuning INSERT INTO .. SELECT * FROM remote_table

Tuning INSERT INTO .. SELECT * FROM remote_table

From: bonminh lam <hansmayer1962_at_hotmail.com>
Date: 8 Sep 2001 08:45:19 -0700
Message-ID: <3c6b1bcf.0109080745.2fbe3146@posting.google.com>


The query in question looks like this

INSERT INTO lam_t1
SELECT *
FROM facts_at_db_link
WHERE month = TRUNC('01 07', 'rrmm')
;

The execution plan shown by T.O.A.D (I am trying to put it togehter from memory now as I am not in the office now) looks like this:

INSERT
  FILTER
    REMOTE* Apparently, the whole table will be transferred across the network and the local database will eliminate the rows of the unwanted months. On the remote database, I could see that Oracle was doing a full table scan
although the column MONTH is NOT NULLABLE and there is an index on it as first column. I suspect there is some NLS issue that prevents Oracle from using the
index. When I tried it with a WHERE predicate on another column, e.g like this:

SELECT *
FROM facts_at_db_link
WHERE prod_id = 'A'
;

The local execution plan becomes:

INSERT
  REMOTE* That has to prove my suspicion regarding NLS. In fact, when I rephrase the where predicate to

WHERE TO_CHAR(month, 'rrmm') = '0107'

the execution plan changes to

INSERT
  REMOTE* Although the remote database will not be able to use an index neither (it has not been upgraded to 8i yet so function based index is not an option) it is still preferable to the first one because the table is close to 2 GB and I just want to copy about one 30th of the data rows. The first query with the whole table being transferred across the network did not finish after over one hour, then I decided to abort the session. The rephrased query took about 10 minutes.

One interesting fact is that in this particular situation the depricated where condition

TO_CHAR(month, 'rrmm') = '0107' -- a

performs superior to the mostly recommended

month = TRUNC('01 07', 'rrmm') -- b

because in the case of a, the local instance is passing the WHERE predicate to the remote database for evaluation and in b it is evaluated locally. But why exactly does the local database decide to do that? Is there anyway to encourage Oracle to pass the whole SELECT statement to the remote site?

I tried with the DRIVING_SITE hint, but that only works for joins. FYI, the local database runs on 8.1.7.

To reply to me by mail, please do not use the reply address, but reply to "bmlam ATonlineDOTde" - replace the capital letters properly. Received on Sat Sep 08 2001 - 10:45:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US