Someone recently sent me a request about a piece of SQL they could not optimise. I don’t usually respond to private requests – it’s not an effective use of my time – but their example was something that pops up relatively frequently as a “bug” – so I thought I’d mention it here.
The SQL looked like this:
insert into tab3 select -- small result set * from tab1@dblink t1 -- large data set where tab1.col1 in ( select col1 from tab2 -- small data set )
The “obvious” execution plan is to send the small amount of data from tab2 to the remote site, perform a join at the remote site, then pull the small result set back to the local site for the insert.
If you eliminate the “insert into” bit of the code, a simple /*+ driving_site(t1) */ hint would do exactly that. But when the “insert into” is included, the join doesn’t obey the hint. The execution path for the “insert as select” pulls every row from tab1 (but only the columns it needs, of course) across the network and does the join locally, discarding most of the data as it does so.
So why is Oracle “ignoring” the hint ? Because it’s supposed to. The SQL has to operate at the site which holds the target of the DML statement. It’s not a bug, it’s defined behaviour.
I mentioned this to Joel Goodman (Oak Table member and instructor at Oracle University) recently, and he promptly dug up a bug number on Metalink for me – 5517609 – which confirms that this is expected behaviour.
This is not a bug. A distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this.
Footnote: If you want to make this example join remotely, you’ll have to create a join view at the remote site, and query the view.