Re: Tuning INSERT INTO .. SELECT * FROM remote_table

TurkBear <noone_at_nowhere.com> wrote in message news:<cmipptkc6e7l0s6kj7d64ttkcbb5p4rkfr_at_4ax.com>...
> 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

Are you sure? I have access now to the remote DB, and below are the various forms of SQL statements and their execution plans

• Statement 1 insert into lam_t1 select * from facts_at_remote_db where datum = trunc( to_date( '0107', 'rrmm'), 'mon' )

INSERT STATEMENT Optimizer=CHOOSE (Cost=10201 Card=447578 Bytes=48338424)
  FILTER
    REMOTE*

• Statement 2 insert into lam_t1 select * from facts_at_remote_db where indikationsgruppe = 'A'

INSERT STATEMENT Optimizer=CHOOSE (Cost=90 Card=1032873 Bytes=111550284)
  REMOTE* (Cost=90 Card=1032873 Bytes=111550284)

While in statement 1 it is to be derived that the whole 2 GB table will be read
and sent across the network for the local site to evaluate the WHERE predicate,
statement 2 shows that the whole SELECT part including the WHERE clause is sent to the remote site for execution. It sounds unlogical to me that the remote Oracle server would restrain from using an index if there is a suitable one, while should the remote optimizer be aware that it is a remote query?

Because the most important for me was to prevent sending 2GB over the network, I rephrased the WHERE predicate in the initial statement to its equivalent:

• statement 3 insert into lam_t1 select * from facts_at_remote_db where to_char(datum, 'rrmm') = '0107'

INSERT STATEMENT Optimizer=CHOOSE (Cost=37646 Card=134274 Bytes=14501592)
  REMOTE* (Cost=37646 Card=134274 Bytes=14501592)

This execution is more to my liking bcos I achieved my major goal this way, just unfortunate that the remote Oracle 8.0.5 server will not be able to use an index. The difference in execution plan 1 and 3 seems to indicate that it is some NLS related issue that makes the local server to behave differently (filter or no filter locally) Received on Wed Sep 12 2001 - 02:53:19 CDT