Tuning query over database link

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