Executes child operations for each partition specified by a subquery
This operation was introduced in Oracle 10.2
This operation is implemented in the following versions
|
This example was developed using Oracle 10.2.0.1 on Linux
This example requires the following table definition
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40)
);
CREATE TABLE t2
(
c1 NUMBER,
c2 NUMBER,
c3 NUMBER,
c4 CHAR(100)
);
CREATE INDEX i2 ON t2 (c1);
Dynamic sampling must be disabled using
ALTER SESSION SET optimizer_dynamic_sampling = 0;
Table statistics must be set for the number of blocks on each table
EXECUTE DBMS_STATS.SET_TABLE_STATS (USER,'T1',numblks=>100000);
EXECUTE DBMS_STATS.SET_TABLE_STATS (USER,'T2',numblks=>100);
The statement
SELECT t2.c2,SUM(t2.c3)
FROM t1,t2
WHERE t1.c1 = t2.c1
AND t2.c3 = 42
GROUP BY t2.c2;
generates the following execution plan
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 HASH (GROUP BY) 2 1 HASH JOIN 3 2 TABLE ACCESS (FULL) OF 'T2' 4 3 PARTITION RANGE (SUBQUERY) 5 4 TABLE ACCESS (FULL) OF 'T1'