0

I'm using Oracle 11g. I have a query that joins local table with remote tables using db links. I want the driving table to be the remote table as I primarily filter using remote table to get a few rows. I then want to join them with local table.

The problem is the optimizer ignores ORDERED and INDEX hints and does a full table scan of the local table. I am using the right indexes and have generated statistics. I run the queries individually with each table they use the correct indexes, but with the join, the local table always does a full table scan and acts as the driving table.

SELECT /*+ INDEX_RS_ASC(l) */
   *
FROM remote_table@mylink r
JOIN local_table l USING (cont_id)
WHERE r.PRIME_VENDOR_ID = '12345'

Explain Plan for Query

Vijay Jagdale
  • 2,321
  • 2
  • 18
  • 16
  • Have you tried using the `USE_NL` hint? Here's a link to a blog post where they solved a similar problem with this link - http://msutic.blogspot.co.il/2012/03/tuning-distributed-query-using-usenl.html – Yaron Idan Mar 25 '16 at 15:46
  • I tried 'USE_HASH', 'USE_NL' and 'DRIVING_SITE', and they all still give me a full table scan for the local table. I have to mention that the remote table I mentioned is actually a view which joins 2 remote tables, – Vijay Jagdale Mar 25 '16 at 16:21
  • FYI, index hints generally require you to specify the index name that you want to make use of, for there may be more than one index on the table. E.g. SELECT /*+ INDEX_RS_ASC(tbl_name idx_name) */ ... – Patrick Marchand Mar 25 '16 at 16:23

0 Answers0