In this query, svbarce is a synonym refering to a view vwbarce_aq using a database link. This view retrieves 590.309 records. The table srbarce on the other hand is a local table which is empty.
SELECT ... FROM svbarce WHERE defooid IN (SELECT defooid FROM srbarce) MINUS SELECT ... FROM srbarce
Which means that Oracle retrieves all the data from the remote table, and checks record by record against the index to match with the local table (which, I repeat, is empty).
OPERATION OBJECT OPTIONS COST CARD SELECT STATEMENT 3674 1 MINUS SORT UNIQUE 4 1 NESTED LOOPS 3 1 REMOTE VWBARCE_AQ 2 1 INDEX SRBARCE_1 FULL SCAN 1 1 SORT UNIQUE 3670 1 TABLE ACCESS SRBARCE FULL 3669 1
I should have mentioned that the query can not be altered...Are you saying that the query 110% can not be altered or that you don't want to alter it because alterations have wide-ranging impacts. I can understand not wanting to use HINTs, but if there is a away to write this query so that it works faster for all of your clients, are you saying that you still can't change it? Not being able to alter the query in any way really limits the possible options for tuning this.