I have a small Lookup table in Oracle 9i & a large Fact table in the remote database.
I would like to push-down a Select-list to the remote database for performance reasons.
I have created a PUBLIC DBLINK to the remote ODBC database.
If I issue:
"select /*+DRIVING_SITE(remote_table)*/ trans_id, trans_amt,
from remote_table@rmtdbase, scott.lookuptab
where remote_table.cust_id = lookuptab.customer_id
and lookuptab.customer_name = 'FRED';"
"SELECT T0000.TRANS_AMT AS c0001, T0000.TRANS_ID AS c0000 FROM FOO2 T0000;"
i.e. ALL Fact rows are returned and joined in the local Oracle system.
I've also tried creating a View for the remote table - no difference.
Also tried with a SubSelect against local lookuptab - no difference.
Also created Index on LookupTab.Customer_Id and have run Analyse.
I should add that the remote database system is a specialist system, optimised for performance across all columns, i.e. creating an Index for performance reasons is neither necessary nor possible. If I manually create the values to be pushed down, performance is superb.
One of the challenges with Oracle Hints seems to be that if for example it is wrongly specified, it is treated as a comment.
The Oracle docs indicate this should be possible but (after several hours!) I still can't see my error. Any suggestions gratefully received.