The link was very helpful.
But when I used explain plan it was not clear the way Oracle handles (or converts) the "in" and "exists". Does the explain plan fails to show how the query is rewritten in these cases, or did I fail to interprete the explain plan?

Thanks

Hi Tom, I has a question
I did this remote select

SELECT
NVL( SUM( DB_TCS_CAMBIOMONEDA_SE( dFecha, EMN_MONEDA, cMon, ( HCA_PRVAL - VEP_PRVEN ) * ( VEP_NUMTITS - VEP_LIQANT ) )), 0)

FROM VENTA_PACTO@SOA.WORLD, CARTERA@SOA.WORLD, EMISION@SOA.WORLD
WHERE VEP_LIQ = 'F'
AND CAR_CODCART = VEP_CODCART
AND CAR_CODCLI = nCodCli
AND CAR_INST = EMN_CODINST
AND CAR_SERIE = EMN_SERIE
AND EMN_METODO_VALUACION IN ( 'CUP', 'CUK' );

It took about 8:00 mn

then I changed
DB_TCS_CAMBIOMONEDA_SE to DB_TCS_CAMBIOMONEDA_SE@SOA.WORLD


SELECT
NVL( SUM( DB_TCS_CAMBIOMONEDA_SE@soa.world( dFecha, EMN_MONEDA, cMon, ( HCA_PRVAL - VEP_PRVEN ) * ( VEP_NUMTITS - VEP_LIQANT ) )), 0)
.............

And it took 0.00 seconds

Why this happened

Thanks

:) Thanks

I only want to be sure, I supposed initially that the query returned the row from the remote database and once then processed locally the row, but it's not in this way.

here are the trace info.

Using remote function ( o seconds )
----------------------
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=248 Card=1
Bytes=78)

1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=248 Card=1 Bytes=78)
3 2 MERGE JOIN (CARTESIAN) (Cost=8 Card=2399 Bytes=88763)
4 3 TABLE ACCESS (FULL) OF 'VENTA_PACTO' (Cost=2 Card=1 SAFI
Bytes=21)

5 3 BUFFER (SORT) (Cost=6 Card=2399 Bytes=38384)
6 5 TABLE ACCESS (FULL) OF 'EMISION' (Cost=6 Card=2399 SAFI
Bytes=38384)

7 2 VIEW OF 'HICARTERA' SAFI
8 7 UNION-ALL (PARTITION)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'HICARTERA_RW' (C SAFI
ost=2 Card=1 Bytes=30)

10 9 INDEX (RANGE SCAN) OF 'IDX_FEINSTSERIE_RO' (NON- SAFI
UNIQUE) (Cost=1 Card=1)

11 8 TABLE ACCESS (BY INDEX ROWID) OF 'HICARTERA_RO' (C SAFI
ost=2 Card=1 Bytes=30)

12 11 INDEX (RANGE SCAN) OF 'IDX_FEINSTSERIE' (NON-UNI SAFI
QUE) (Cost=3 Card=1)





Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
510 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed




Using local function (8 mn)
--------------------


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=146)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=6 Card=1 Bytes=146)
3 2 NESTED LOOPS (Cost=4 Card=1 Bytes=83)
4 3 REMOTE* (Cost=2 Card=1 Bytes=64) SOA.WORL
D

5 3 REMOTE* SOA.WORL
D

6 2 REMOTE* (Cost=2 Card=1 Bytes=63) SOA.WORL
D



4 SERIAL_FROM_REMOTE SELECT "HCA_FECHA","HCA_PRVAL","HCA_CODCART"
,"HCA_INST","HCA_SERIE","HCA_CODCLI"

5 SERIAL_FROM_REMOTE SELECT "EMN_SERIE","EMN_CODINST","EMN_MONEDA
" FROM "EMISION" "EMISION" WHERE :1=

6 SERIAL_FROM_REMOTE SELECT "VEP_CODCART","VEP_FECHAVEN","VEP_PRV
EN","VEP_NUMTITS","VEP_LIQ","VEP_LIQ



Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
500 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

hi

I have some doubts regading queries against a remote database using db links.

If I have a query say

select /*+ USE_MERGE(emp v) */ ename, dname
from emp, dept@db1 v
where emp.deptno = v.deptno

I understand the sort will take place in the local database

If I have a query says

select count(*)
from dept@db1
group by deptno, location;

in the remote v$sql I can see the query is rewritten to

select count(*)
from dept
group by deptno, location;

however is that group by using remote temporary tablespace or the whole table is downloaded to my local database and doing the sort in my local temp tablespace?

Hi

doesnt count(*) sort?

also group by?
from concepts guide

All operations that use sorts, including joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics, benefit from temporary tablespaces. The performance gains are significant with Real Application Clusters.

:-0

Hi

in this query

select /*+ USE_MERGE(emp v) */ ename, dname
from emp, dept@db1 v
where emp.deptno = v.deptno;

Entire table dept is transferred to local database right? Is it stored in local temp tablespace? Or where is it stored?

Hi,

I want known the codition for which the the remote table will be brought to local database. if the remote table is brought to local database. then what happen to blocks of remote table. is it copied to log_buffer of both the database.

Thanks

Hi
My req is slightly complicated.
I am accessing columns from the remote table in the select clause.
SO it is not possible for me to move the table to the subquery.
also replcation of the remote table is not feasible ( 2 milion records) and also not allowed.
is there any was i can access the column.

select c.co1l
c.col2,
c1.col1,
c1.col1
where
c1.col1 in ( select col1 from c@dblink where..)

this cannot be done as c is in x database.
i need c.col1 and col2 in select query
and i am returning this output as a ref cursor.

I have the above requirement - I can not use a dblink
unfortunately.
This is being done using JDBC.
One way I could think was to
1. select the remote table result set in Java
and put it into a temporary table
insert into temp_table
<select from remote table here>;
2. Join the temp table with local tables
to get the final results.
3. delete the temp table data.

Can you think of a better way?

btw, are there any "issues" with using dblink
that you are aware of? My hunch is that any performance
issues related to queries using db link are inherent
in the query itself and it certainly can not be
overcome using two queries (one remote and the
other joining local with it - in fact the latter
would be slower since you are doing more work)
Thanx a lot!:)

I could use a collection( a nested table) instead of
the temporary table to insert my records but I think
the temporary table method is better because:
1. effectively they are doing the same thing - the overhead
of inserts/deletes in temporary table is quite tolerable.
2. you can create indexes, use statistics (as you show
in your book) on temporary tables.

Sorry
in the solution proposed you can not use "insert into
select" unless you use dblink.
So I guess you will have to a row by row insert in JDBC.

You run the select from remote table
in the JDBC and then for each entry insert into
the temporary table.

"funny...

read:
"
Yeah I actually had read that link before posting -
I do think that db links are the way to go. I am not
in a position to find out why these have been ruled
out though. Assuming this is not an option, would you
go with the GTT solution I suggested earlier?:)

Thanx!

Hi Tom,

Our application has a need to access the remote table (reference table in remote oracle database, which is static and medium sized table).
Both source and destination databases are in oracle9i.

Following are the choices we have in accessing the data from the remote database :

Option 1 :
***********

a) ask external system to send us the daily feed/weekly feed in the form of "pipe delimited text file or export dump file" based on the frequency of table changes. Load daily feed into local database.


Option 2 :
**********

a) Directly access the remote database via JDBC queries
b) Create a view for the remote table in local database using database link.

External system recommends us to use Option 2 mentioned above by saying they can save money in code development.


Appreciate your suggestions/comments on this approach, which is one is the right approach.


Option 3 :
**********

For high availablity and performance reasons/better control on table maintenance, as a last resort (if at all external system cannot send us daily feed), we are planning to use this option of creating materialized views on local database against remote database for periodic sync up.


Appreciate your valuable suggestions/feedback on pros/cons with each approach given above from performance/availability/tuning perspective. Particulary option 2 and 3.

Tom,

Thank you.

If you are given Option 2 (from above options), which of the following approach you would take and the reasons why ?

a) Application directly access the remote database

b) Create a view for the remote table in local database using database link and application will query the remote table via local database view.

Appreciate your suggestions/comments on this approach, which is one is the right approach.

Tom,

Thank you .