Distributed DML

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:

rem
rem     Script:         distributed_dml.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2008
rem
rem     Last tested 
rem             10.2.0.3
rem 

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.

Update (Mar 2019)

The statement about the DML executing at the database that is the target of the DML is re-inforced by Doc ID 825677.1: Limitations of DRIVING_SITE Hint For DMLS and DDLS which repeats the above and then points out that the hint always applies to the whole statement, not to a subqury.

More interestingly it also introduces an important point about distributed queries and databases that have been opened read-only. If the read-only database becomes the driving site of the query its SCN is fixed at the moment it opened – so your entire query will run as at the moment the read-only database opened.

 

[Further reading on distributed databases]