Re: Tuning INSERT INTO .. SELECT * FROM remote_table

No index is used with any REMOTE database connection thru a db link..( see Oracle docs) to use an index with such a setup a snapshot ( in 8.1 terms a materialized view ) must be created for the REMOTE instance's table

( for REMOTE selecting, try building a view in the REMOTE instance that does the selection and use that view to populate your LOCAL instance..)

hth

hansmayer1962_at_hotmail.com (bonminh lam) wrote:

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

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!  Check out our new Unlimited Server. No Download or Time Limits! -----== Over 80,000 Newsgroups - 19 Different Servers! ==----- Received on Mon Sep 10 2001 - 09:18:01 CDT