PARTITION LIST (SUBQUERY)

Description

Executes child operations for each partition specified by a subquery

Versions

This operation was introduced in Oracle 10.2

This operation is implemented in the following versions

Example

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'