Oracle Scratchpad

September 18, 2013

Distributed Sets

Filed under: distributed,Oracle,Performance,sorting — Jonathan Lewis @ 6:14 pm BST Sep 18,2013

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.

Here’s a little demonstration, cut-n-pasted with a little cosmetic editing from an 11gR1 SQL*Plus session:

SQL> create table t1 as select * from all_objects where rownum <= 20000;

Table created.

SQL> create table t2 as select * from all_objects where rownum <= 10000;

Table created.

SQL> create table t3 as select * from all_objects where rownum < 1;

Table created.

SQL> set autotrace traceonly explain

SQL> insert into t3
  2  select * from t1@d11g@loopback
  3  minus
  4  select * from t2@d11g@loopback
  5  ;

10000 rows created.

Execution Plan
Plan hash value: 1945526954

| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
|   0 | INSERT STATEMENT         |      | 23524 |  5331K|       |  1137  (33)| 00:00:06 |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |       |       |       |            |          |        |      |
|   2 |   MINUS                  |      |       |       |       |            |          |        |      |
|   3 |    SORT UNIQUE           |      | 23524 |  3629K|  4496K|   773   (2)| 00:00:04 |        |      |
|   4 |     REMOTE               | T1   | 23524 |  3629K|       |    40   (5)| 00:00:01 | D11G@~ | R->S |
|   5 |    SORT UNIQUE           |      | 11027 |  1701K|  2112K|   364   (2)| 00:00:02 |        |      |
|   6 |     REMOTE               | T2   | 11027 |  1701K|       |    19   (6)| 00:00:01 | D11G@~ | R->S |

Remote SQL Information (identified by operation id):

       ITION_NAME" FROM "T1" "T1" (accessing 'D11G@LOOPBACK' )

       ITION_NAME" FROM "T2" "T2" (accessing 'D11G@LOOPBACK' )

Not good – to get the 10,000 rows I wanted in my result set Oracle fetched everything from the remote t1 (I’ve used my usual “connection qualifier” trick to make the same database appear to be a second database at the end of a datababase link), sorted the result locally, fetched everything from the remote t2, sorted the result locally, then found the difference. I want the remote system to do both fetches and sorts and send only the final result of 10000 rows.

But here’s a silly little trick that makes a huge difference:

rem     Script:         distributed_minus.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sept 2013

insert into t3
select	*
from	(
	select * from t1@d11g@loopback
	select * from t2@d11g@loopback

Plan hash value: 1788691278

| Id  | Operation                | Name | Cost  | Inst   |IN-OUT|
|   0 | INSERT STATEMENT         |      |     1 |        |      |
|   1 |  LOAD TABLE CONVENTIONAL |      |       |        |      |
|   2 |   REMOTE                 |      |       | D11G@~ | R->S |

Remote SQL Information (identified by operation id):
       ( (SELECT "A4"."OWNER" "OWNER","A4"."OBJECT_NAME"
       (accessing 'D11G@LOOPBACK' )

By wrapping the required query into an inline view – which, presumably, the optimizer considers to be a non-mergeable view – we find that the entire query is sent to remote database for operation and only the minimum result set returned.

Of course this isn’t a distributed query (it’s a “fully remote” query) so I was a little surprised when Oracle didn’t execute the original form remotely, then I was pleasantly surprised when I found that a simple little trick solved the problem. Unfortunately you’ll find that the problem on OTN was a little harder – Oracle didn’t like this type of query at all if one of the subqueries involved an “as of timestamp” clause.


  1. would be nice if they would support a syntax

    Comment by Matthias Rogel — September 18, 2013 @ 7:31 pm BST Sep 18,2013 | Reply

    • Matthias,

      The closest you can get, at present, is to put the remote bits that you want operated together in an inline view with a /*+ no_merge */ hint in the view. It will guarantee that the join will be done remotely, but the technique isn’t flexible enough to handle all the cases I’d like. Your “remote query block” is a nice idea – I wonder if there’s some set of (undocumented) hints that is supposed to have the same effect.

      Comment by Jonathan Lewis — September 20, 2013 @ 9:36 am BST Sep 20,2013 | Reply

  2. SImilar experiences here. Maintaining a time consistent view of data from one db to another, so lots of variations on a theme:

    merge into TARGET
    using ( select * from SOURCE@db as of scn ... )
    insert into TARGET
    select * from SOURCE@db as of scn ...
    delete from TARGET
    where ... not in ( select * from SOURCE@db as of scn ... )

    and the like, all tend to exhibit some interesting oddities from time to time….With many of these, ultimately it required kludgy workarounds (eg getting a subset of rows from the source into the local global temp table and using that). Similarly, if the SCN number is a bind variable, there’s a number of Oracle bugs for which workarounds involve either turning off peeking, or hinting to disable bind awareness etc.


    Comment by connormcdonald — September 19, 2013 @ 12:27 am BST Sep 19,2013 | Reply

  3. The capabilities we *arguably* need can be described by a 4 table join, with 2 tables at one site (A for reference) and 2 tables at another site (B for reference) such that the join at site B with all the available filters yields a very large result set, yet if the results of a small filtered result set at site A were sent to site B, the results of the filtered join output of site A plus the tables at site B would be small. In the case were the intermediate processing to reach the small result set from the two tables at site A is voluminous, driving everything from B is burdensome.

    In lieu of anticipation of a long calendar time being required for the CBO to be capable of dithering this out, this was a wish list item from the “Oracle VLDB” group circa 1991. Various bundling syntax choices could accomplish this, but without writing things as series of queries possibly producing temporary table results (we called them “interim tables” before global temporary tables existed), I do not know how to get Oracle to routinely handle this case or the general case it implies.

    In the current case, of course you would create the small interim result set at site A and then execute the rest of the intended result set projection by pulling the interim table from site A to site B (together with any matching relevant filters of the original query). Without a read-consistent bundling time, this represents a less than pure solution. Using the various as-of syntaxes now available I suppose you can close that gap, but it remains frustrating that this is not trivially handled by syntax where many users know the texture of multiple related databases and the general case solution by the CBO seems very difficult.

    (By the way, if someone can should me the single query solution to the above stated case I’ll be happily enlightened. Remember, you only get to send something the size of the result set of the join on A between the systems, and making the interim join operation small in your test case is not a solution.)

    Nice find on the effects of the ( ) enclosure, JL!


    Comment by rsiz — September 19, 2013 @ 5:17 pm BST Sep 19,2013 | Reply

  4. Hmm, that ( ) enclosure trick might actually solve the case I described! Really nice find.

    Comment by rsiz — September 19, 2013 @ 5:39 pm BST Sep 19,2013 | Reply

    • Mark,

      At first sight it looks like you’re after a generic “bushy tree” optimisation algorithm while Oracle follows a “left-deep” strategy.
      In the simplest case it looks as if you could do something like

      select va.*, vb.*
              (select /*+ no_merge */ from join of A tables) va,
              (select /*+ no_merge */ from join of B tables) vb
              va.columns = vb.columns

      As a further enhancement (and complication) if you needed to minimise the volume of data moved from one database to the other you could use the “joinback” mechanism to select only the A columns (with rowid) needed for the join, then join back to the A tables by rowid when the join result comes back.

      Comment by Jonathan Lewis — September 19, 2013 @ 5:59 pm BST Sep 19,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: