Laurent Schneider

I just learnt from a colleague a very useful hint for remote databases. Over a db link, Oracle does not have the necessary statistics to take the right decision.

Here is the test case :
DB01

create database link lsc_link_2 using 'DB02';
create table lsc_small_1 as 
  select rownum x from dual connect by level<100;
alter table lsc_small_1 add primary key(x);
create table lsc_big_1 as 
  select rownum x from dual connect by level<1000000;
alter table lsc_big_1 add primary key(x);

DB02

create table lsc_small_2 as 
  select rownum x from dual connect by level<100;
alter table lsc_small_2 add primary key(x);
create table lsc_big_2 as 
  select rownum x from dual connect by level<1000000;
alter table lsc_big_2 add primary key(x);

and here are the two test queries (run on DB01)

select count(*) from lsc_big_1 b1 
  join lsc_small_2@lsc_link_2 s2 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:00.10

select count(*) from lsc_big_2@lsc_link_2 b2 
  join lsc_small_1 s1 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:10.31

As shown above, first one is 100x faster. Anything to tune? For sure!

The trick is to execute the join remotely when the remote table is much larger than the local one!

select /*+ DRIVING_SITE(b2) */ count(*) 
  from lsc_big_2@lsc_link_2 b2 
  join lsc_small_1 s1 using (x);
  COUNT(*)
----------
        99
Elapsed: 00:00:00.06

Ref: the DRIVING_SITE hint

database link sql tuning