Distributed Sets

In an earlier post I’ve described how a distributed query can operate at a remote site if it’s a simple select but has to operate at the local site if it’s a CTAS (create as select) or insert as select. There’s (at least) one special case where this turns out to be untrue … provided you write the query in the correct fashion. I discovered this only as a result of doing a few experiments in response to a question on the OTN database forum.

Distributed Queries – 3

A comment I’ve made many times in the past about distributed queries is that Oracle doesn’t try to retrieve histogram information from remote databases when optimizing a query. Checking back through previous posts, though, I don’t think I’ve ever mentioned it on the blog – so here’s an example demonstrating the point.

Distributed Queries – 2

I have often said that the optimizer “forgets” that it is dealing with a distributed query once it has collected the stats that it can about the objects in the query, and that as a consequence the driving site for a distributed query will be the local database unless you use the /*+ driving_site */ hint to change it.

While investigating an oddity with a distributed query between two 11.1.0.7 databases a few days, I noticed something in the 10053 trace file that made me change my mind, and go back to look at earlier versions of Oracle.

Distributed Objects

I recently came across a tidy solution to a common problem – how to minimise code maintenance in a procedure while maximising flexibility of the procedure. The task was fairly simple – create a ref cursor for a calling program to return data that (a) followed complex selection rules and (b) allowed the user to specify numerous types of input.

The principle was simple – the final ref cursor was driven by a list of (say) order ids – and the details to be returned about those orders required some fairly complex SQL to execute. To separate the complexity of constructing the list of columns from the complexity of identifying the required rows the developers had split the procedure into two stages. First, select the list of relevant order ids using one of several possible statements – the appropriate statement being derived from analysis of the inputs to the procedure; secondly open a ref cursor using that list of order ids. In this way if a new set of rules for selection appeared the only new code needed was a new query to select the ids – the main body of code didn’t need to be modified and re-optimised.

Distributed Pipelines

In an article that I wrote about the /*+ driving_site */ hint a few months ago I pointed out that the hint was not supposed to work with “create table as select” (CTAS) and “insert as select”. One of the people commenting on the note mentioned pipelined function as a workaround to this limitation – and I’ve finally got around to writing a note about the method.

The idea is simple. If you can write a distributed select statement that takes advantage of the /*+ driving_site */ hint to work efficiently, you can wrap the statement in a pl/sql cursor loop and stick that loop into a pipelined function to maximise the efficiency of create or insert as select. Here’s some sample code (tested on 11.1.0.6) to demonstrate the principle:

Ignoring Hints

I’ve previously published a couple of notes (hereand here) about the driving_site() hint. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a remote query. I’ve just found another case where the hint is ignored – this time in a simple SELECT statement.

Try running an ordinary distributed query from the SYS account, and then try using the driving_site()hint to make it run at the remote site. When I tried this a few days ago I ended up wasting half an hour translating some SQL from ANSI to Oracle dialect because I thought that the ANSI was making Oracle transform the query in a way that lost the hint – then I discovered that both versions of the code worked correctly if I logged in as a different user.

I was running my queries between two databases using 11.1.0.7 – I won’t guarantee you get the same results on other versions, but it looks like SYS doesn’t honour the driving_site() hint. I can’t think of a robust argument why this should be the case, but if I were forced to do some vague hand-waving I’d probably mumble something about potential security loopholes.

Footnote: I should, of course, have mentioned that there are all sorts of things that behave in unexpected ways if you are logged on as SYS, and that you shouldn’t be logged on as SYS – especially in a production system.

[Further reading on “ignoring hints”]

Distributed Queries

Some time ago I wrote a note about distributed DML, pointing out that the driving_site() hint works with distributed queries but not with distributed DML; so insert as select, or create as select and so on will “ignore” the hint.

This is just a little follow-up to give you an  idea of what execution plans for distributed queries look like so that you can tell whether your query is going to work locally or remotely.

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:

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
	)

 

Distributed Oddity

An interesting side-effect of blogging (rather than writing up a more formal document for my web site) is that it feels perfectly reasonable to throw out some odd observations without necessarily having to supply an explanation or solution.

So here’s the bare bones of an oddity relating to distributed queries that someone emailed me a couple of days ago:

INST1 -  9.2.0.6:  Insert data into table X and commit 
INST2 - 10.2.0.1:  select from X@DB1,dual;