Distributed query against remote partitioned table

It’s been a while, so I should fulfill my promise to discuss distributed query against remote partitioned table.
If a distributed query involves database objects(tables, indexes, PL/SQL procs, etc) from different databases, then sometimes, Oralce will do row-by-row processing.
Let’s look at a very simple example:

CREATE TABLE “DIMENSION1”

( “PK1” NUMBER(11,0) NOT NULL ENABLE,

“DIMVAL1” DATE NOT NULL ENABLE,

CONSTRAINT “DIMENSION1_PK” PRIMARY KEY (“PK1”) ENABLE

) ;

CREATE TABLE “DIMENSION2”

( “PK2” NUMBER(11,0) NOT NULL ENABLE,

“DIMVAL2” NUMBER,

CONSTRAINT “DIMENSION2_PK” PRIMARY KEY (“PK2”) ENABLE

) ;

CREATE TABLE “FACT”

( “PK1” NUMBER(11,0) NOT NULL ENABLE,

“PK2” NUMBER(11,0) NOT NULL ENABLE,

“VAL” NUMBER

)

PARTITION BY RANGE (“PK1”)

……

CREATE UNIQUE INDEX “FACT_PK” ON “NHUSER”.”FACT” (“PK1”, “PK2”) LOCAL COMPRESS 1;

CREATE BITMAP INDEX “FACT_BI1” ON “NHUSER”.”FACT” (“PK1”) LOCAL;

CREATE BITMAP INDEX “FACT_BI2” ON “NHUSER”.”FACT” (“PK2”) LOCAL;

Now create some database links, could be loopback ones, say remote1 and remote2.

 

CREATE OR REPLACE FORCE VIEW “FACT_VIEW” (“PK1”, “PK2”, “VAL”) AS

select “PK1″,”PK2″,”VAL” from fact@remote1

union all

select “PK1″,”PK2″,”VAL” from fact@remote2;

select /*+ USE_MERGE */sum(t3.val)

from dimension1 t1, dimension2 t2, fact_view t3

where t1.PK1=t3.PK1 and t2.PK2=t3.PK2

and t1.DIMVAL1 between timestamp ‘2006-06-01 00:00:00.000’

and timestamp ‘2006-06-02 00:00:00.000’

and t2.DIMVAL2 in (120001);

 

The plan is

——————————————————————————————-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Inst |IN-OUT|

——————————————————————————————-

| 0 | SELECT STATEMENT | | 1 | 62 | 1569 (3)| | |

| 1 | SORT AGGREGATE | | 1 | 62 | | | |

| 2 | NESTED LOOPS | | 2843 | 172K| 1569 (3)| | |

| 3 | MERGE JOIN CARTESIAN| | 1432 | 32936 | 129 (27)| | |

|* 4 | TABLE ACCESS FULL | DIMENSION1 | 5 | 65 | 6 (17)| | |

| 5 | BUFFER SORT | | 283 | 2830 | 123 (27)| | |

|* 6 | TABLE ACCESS FULL | DIMENSION2 | 283 | 2830 | 25 (28)| | |

| 7 | VIEW | FACT_VIEW | 2 | 78 | | | |

| 8 | UNION-ALL PARTITION| | | | | | |

| 9 | REMOTE | | 1 | 14 | | REMOTE1 | R->S |

| 10 | REMOTE | | 1 | 14 | | REMOTE2 | R->S |

——————————————————————————————-

In the remote database, the query executed is:

SELECT /*+ INDEX(“FACT”) */ “PK1″,”PK2″,”VAL”

FROM

“FACT” “FACT” WHERE “PK2″=:1 AND “PK1″=:2

 

 

call count cpu elapsed disk query current rows

——- —— ——– ———- ———- ———- ———- ———-

Parse 1 0.00 0.00 0 0 0 0

Execute 23040 13.91 13.07 0 0 0 0

Fetch 46080 4.30 4.02 0 92160 0 23040

——- —— ——– ———- ———- ———- ———- ———-

total 69121 18.21 17.09 0 92160 0 23040

 

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 23

 

Rows Row Source Operation

——- —————————————————

23040 PARTITION RANGE SINGLE PARTITION: KEY KEY

23040 TABLE ACCESS BY LOCAL INDEX ROWID OBJ#(54225) PARTITION: KEY KEY

23040 INDEX UNIQUE SCAN OBJ#(54394) PARTITION: KEY KEY (object id 54394)

Notice that this query is executed 23040 times, exactly the same number as the rows in the intermediate resultset of the Cartesian join. Oracle is doing this row-by-row processing, as Tom Kyte describes, slow-by-slow.
I guess this is the only correct and generic way of handling union-all views.

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s


%d bloggers like this: