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'