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 LIST (c1)
(
PARTITION p1 VALUES (0,1,2,3,4,5,6,7,8,9),
PARTITION p2 VALUES (10,11,12,13,14,15,16,17,18,19),
PARTITION p3 VALUES (20,21,22,23,24,25,26,27,28,29),
PARTITION p4 VALUES (30,31,32,33,34,35,36,37,38,39)
);
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 LIST (SUBQUERY) 5 4 TABLE ACCESS (FULL) OF 'T1'