Still (and inevitably) true.
You either do a nested loop that typically results in a large number of small
dialogues taking place across the network or you do a hash join (or merge join)
which typically requires a huge flood of redundant data to be dragged across
the network (in relatively small chunks) and mostly discarded.
Often the best optimisation strategy for distributed queries is to make sure
the data generation happens at the correct site. (driving_site hint).
Add to the mechanical problems the optimizer doesn't:
pull histograms across the network
take notice of virtual columns or extended stats
consider function-based indexes (which fits with the "virtual columns being
ignored" bit)
and it only examines 20 of the indexes on any remote table - a few tests
suggest its the 20 most recently created, but it may be more subtle than that.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
Sent: 31 October 2018 17:25
To: oracle-l-freelist; mwf@xxxxxxxx; contact@xxxxxxxx; Amir.Hameed@xxxxxxxxx
Subject: RE: IPC versus TCP performance
I agree with the optimizer question. At least a few versions ago, joining
tables across a database link had poor performance. Anybody know if still true.
--
//www.freelists.org/webpage/oracle-l
