Hi, all!
To John Kanagaraj: filter by
ROWNUM does not make any difference – I have absolutely the same speed, when
querying table with 5000 rows and filtering ‘where ROWNUM
<5000’.
To Tom Mercadante: creating of a
view, that does filtering, did not help. Absolutely the same
results!
Just remind you the
case:
declare
v_record B%rowtype;
cursor cB
is
select *
from
[EMAIL PROTECTED]
where rownum <
5000;
begin
for c in cB
loop
v_record
:=c;
end loop;
end;
My next step has been to analyze
session-level statistics.
When using link we have
physically two sessions – from a client to db_A (opened explicitly) and from
db_A to db_B (opened by Oracle automatically). After execution of a script:
- in the session to db_A we have 10000 SQL*Net roundtrips to/from
db_link
- in the session to db_B we have
10000 SQL*Net roundtrips to/from client.
But when I am running the same
script directly (from client to db_B) – there is only ONE SQL*Net roundtrip
to/from the client.
I asked Paul Dorsey to check it
out with Tom Kyte at yesterday’s NYOUG meeting.
Tom told us that Oracle 8.* does
single record fetches when using database links. In general, database links have
been tuned to support database replication. It means, that ‘create table new_A
as select … from table_A@db_B’ runs quickly but querying from new_A is quite
slow.
The remaining question: has
anybody tried to use database links in Oracle9i? Does the same single fetch
limitation apply?
Thank you,
Michael Rosenblum
Dulcian
Inc.
