Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Tuning INSERT INTO .. SELECT * FROM remote_table
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