PARTITION RANGE SUBQUERY子查询消除

troubleshooting中的例子：

CREATE TABLE t ( id NUMBER, d1 DATE, n1 NUMBER, n2 NUMBER, n3 NUMBER, pad VARCHAR2(4000), CONSTRAINT t_pk PRIMARY KEY (id) ) PARTITION BY RANGE (n1, d1) ( PARTITION t_1_jan_2007 VALUES LESS THAN (1, to_date('2007-02-01','yyyy-mm-dd')), PARTITION t_1_feb_2007 VALUES LESS THAN (1, to_date('2007-03-01','yyyy-mm-dd')), PARTITION t_1_mar_2007 VALUES LESS THAN (1, to_date('2007-04-01','yyyy-mm-dd')), PARTITION t_1_apr_2007 VALUES LESS THAN (1, to_date('2007-05-01','yyyy-mm-dd')), PARTITION t_1_may_2007 VALUES LESS THAN (1, to_date('2007-06-01','yyyy-mm-dd')), PARTITION t_1_jun_2007 VALUES LESS THAN (1, to_date('2007-07-01','yyyy-mm-dd')), PARTITION t_1_jul_2007 VALUES LESS THAN (1, to_date('2007-08-01','yyyy-mm-dd')), PARTITION t_1_aug_2007 VALUES LESS THAN (1, to_date('2007-09-01','yyyy-mm-dd')), PARTITION t_1_sep_2007 VALUES LESS THAN (1, to_date('2007-10-01','yyyy-mm-dd')), PARTITION t_1_oct_2007 VALUES LESS THAN (1, to_date('2007-11-01','yyyy-mm-dd')), PARTITION t_1_nov_2007 VALUES LESS THAN (1, to_date('2007-12-01','yyyy-mm-dd')), PARTITION t_1_dec_2007 VALUES LESS THAN (1, to_date('2008-01-01','yyyy-mm-dd')), PARTITION t_2_jan_2007 VALUES LESS THAN (2, to_date('2007-02-01','yyyy-mm-dd')), PARTITION t_2_feb_2007 VALUES LESS THAN (2, to_date('2007-03-01','yyyy-mm-dd')), PARTITION t_2_mar_2007 VALUES LESS THAN (2, to_date('2007-04-01','yyyy-mm-dd')), PARTITION t_2_apr_2007 VALUES LESS THAN (2, to_date('2007-05-01','yyyy-mm-dd')), PARTITION t_2_may_2007 VALUES LESS THAN (2, to_date('2007-06-01','yyyy-mm-dd')), PARTITION t_2_jun_2007 VALUES LESS THAN (2, to_date('2007-07-01','yyyy-mm-dd')), PARTITION t_2_jul_2007 VALUES LESS THAN (2, to_date('2007-08-01','yyyy-mm-dd')), PARTITION t_2_aug_2007 VALUES LESS THAN (2, to_date('2007-09-01','yyyy-mm-dd')), PARTITION t_2_sep_2007 VALUES LESS THAN (2, to_date('2007-10-01','yyyy-mm-dd')), PARTITION t_2_oct_2007 VALUES LESS THAN (2, to_date('2007-11-01','yyyy-mm-dd')), PARTITION t_2_nov_2007 VALUES LESS THAN (2, to_date('2007-12-01','yyyy-mm-dd')), PARTITION t_2_dec_2007 VALUES LESS THAN (2, to_date('2008-01-01','yyyy-mm-dd')), PARTITION t_3_jan_2007 VALUES LESS THAN (3, to_date('2007-02-01','yyyy-mm-dd')), PARTITION t_3_feb_2007 VALUES LESS THAN (3, to_date('2007-03-01','yyyy-mm-dd')), PARTITION t_3_mar_2007 VALUES LESS THAN (3, to_date('2007-04-01','yyyy-mm-dd')), PARTITION t_3_apr_2007 VALUES LESS THAN (3, to_date('2007-05-01','yyyy-mm-dd')), PARTITION t_3_may_2007 VALUES LESS THAN (3, to_date('2007-06-01','yyyy-mm-dd')), PARTITION t_3_jun_2007 VALUES LESS THAN (3, to_date('2007-07-01','yyyy-mm-dd')), PARTITION t_3_jul_2007 VALUES LESS THAN (3, to_date('2007-08-01','yyyy-mm-dd')), PARTITION t_3_aug_2007 VALUES LESS THAN (3, to_date('2007-09-01','yyyy-mm-dd')), PARTITION t_3_sep_2007 VALUES LESS THAN (3, to_date('2007-10-01','yyyy-mm-dd')), PARTITION t_3_oct_2007 VALUES LESS THAN (3, to_date('2007-11-01','yyyy-mm-dd')), PARTITION t_3_nov_2007 VALUES LESS THAN (3, to_date('2007-12-01','yyyy-mm-dd')), PARTITION t_3_dec_2007 VALUES LESS THAN (3, to_date('2008-01-01','yyyy-mm-dd')), PARTITION t_4_jan_2007 VALUES LESS THAN (4, to_date('2007-02-01','yyyy-mm-dd')), PARTITION t_4_feb_2007 VALUES LESS THAN (4, to_date('2007-03-01','yyyy-mm-dd')), PARTITION t_4_mar_2007 VALUES LESS THAN (4, to_date('2007-04-01','yyyy-mm-dd')), PARTITION t_4_apr_2007 VALUES LESS THAN (4, to_date('2007-05-01','yyyy-mm-dd')), PARTITION t_4_may_2007 VALUES LESS THAN (4, to_date('2007-06-01','yyyy-mm-dd')), PARTITION t_4_jun_2007 VALUES LESS THAN (4, to_date('2007-07-01','yyyy-mm-dd')), PARTITION t_4_jul_2007 VALUES LESS THAN (4, to_date('2007-08-01','yyyy-mm-dd')), PARTITION t_4_aug_2007 VALUES LESS THAN (4, to_date('2007-09-01','yyyy-mm-dd')), PARTITION t_4_sep_2007 VALUES LESS THAN (4, to_date('2007-10-01','yyyy-mm-dd')), PARTITION t_4_oct_2007 VALUES LESS THAN (4, to_date('2007-11-01','yyyy-mm-dd')), PARTITION t_4_nov_2007 VALUES LESS THAN (4, to_date('2007-12-01','yyyy-mm-dd')), PARTITION t_4_dec_2007 VALUES LESS THAN (4, to_date('2008-01-01','yyyy-mm-dd')) ); execute dbms_random.seed(0) INSERT INTO t SELECT rownum AS id, trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1, 1+mod(rownum,4) AS n1, 255+mod(trunc(dbms_random.normal*1000),255) AS n2, round(4515+dbms_random.normal*1234) AS n3, dbms_random.string('p',255) AS pad FROM dual CONNECT BY level <= 10000 ORDER BY dbms_random.value; BEGIN dbms_stats.gather_table_stats( ownname => user, tabname => 'T', estimate_percent => 100, method_opt => 'for all columns size skewonly', cascade => TRUE ); END; / SELECT partition_name, partition_position, num_rows FROM user_tab_partitions WHERE table_name = 'T' ORDER BY partition_position; DROP TABLE tx; CREATE TABLE tx AS SELECT * FROM t; ALTER TABLE tx ADD CONSTRAINT tx_pk PRIMARY KEY (id); BEGIN dbms_stats.gather_table_stats( ownname => user, tabname => 'TX' ); END; / SQL> SELECT * FROM v$version WHERE rownum=1; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod --禁用子查询消除 ALTER SESSION SET "_subquery_pruning_enabled" = FALSE; SQL> set autotrace traceonly explain SQL> SQL> SELECT /*+ leading(tx) use_nl(t) */ * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND tx.id = 19; 执行计划 ---------------------------------------------------------- Plan hash value: 2010553102 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 3892 | 5 (0)| 00:00:01 | | | | 1 | NESTED LOOPS | | 7 | 3892 | 5 (0)| 00:00:01 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 278 | 2 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | 1 (0)| 00:00:01 | | | | 4 | PARTITION RANGE ITERATOR | | 7 | 1946 | 3 (0)| 00:00:01 | KEY | KEY | |* 5 | TABLE ACCESS FULL | T | 7 | 1946 | 3 (0)| 00:00:01 | KEY | KEY | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TX"."ID"=19) 5 - filter("TX"."D1"="T"."D1" AND "TX"."N1"="T"."N1") SQL> SELECT /*+ leading(tx) use_hash(t) */ * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND tx.id = 19; 执行计划 ---------------------------------------------------------- Plan hash value: 1797314291 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 3892 | 142 (2)| 00:00:02 | | | |* 1 | HASH JOIN | | 7 | 3892 | 142 (2)| 00:00:02 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 278 | 2 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | 1 (0)| 00:00:01 | | | | 4 | PARTITION RANGE ALL | | 10000 | 2714K| 139 (1)| 00:00:02 | 1 | 48 | | 5 | TABLE ACCESS FULL | T | 10000 | 2714K| 139 (1)| 00:00:02 | 1 | 48 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TX"."D1"="T"."D1" AND "TX"."N1"="T"."N1") 3 - access("TX"."ID"=19) SQL> SELECT /*+ leading(tx) use_merge(t) */ * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND tx.id = 19; 执行计划 ---------------------------------------------------------- Plan hash value: 3397819312 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 3892 | 744 (1)| 00:00:09 | | | | 1 | MERGE JOIN | | 7 | 3892 | 744 (1)| 00:00:09 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 278 | 2 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | 1 (0)| 00:00:01 | | | |* 4 | FILTER | | | | | | | | | 5 | PARTITION RANGE ALL | | 10000 | 2714K| 139 (1)| 00:00:02 | 1 | 48 | | 6 | TABLE ACCESS FULL | T | 10000 | 2714K| 139 (1)| 00:00:02 | 1 | 48 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TX"."ID"=19) 4 - filter("TX"."N1"="T"."N1" AND "TX"."D1"="T"."D1")在禁用子查询消除后，NESTED LOOPS 进行了分区消除，HASH JOIN与MERGE JOIN没有进行分区消除。--使用子查询消除 SQL> ALTER SESSION SET "_subquery_pruning_enabled" = TRUE; 会话已更改。 SQL> ALTER SESSION SET "_subquery_pruning_cost_factor"=1; 会话已更改。 SQL> ALTER SESSION SET "_subquery_pruning_reduction"=100; 会话已更改。 SQL> SELECT /*+ leading(tx) use_nl(t) */ * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND tx.id = 19; 执行计划 ---------------------------------------------------------- Plan hash value: 2010553102 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 3892 | 5 (0)| 00:00:01 | | | | 1 | NESTED LOOPS | | 7 | 3892 | 5 (0)| 00:00:01 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 278 | 2 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | 1 (0)| 00:00:01 | | | | 4 | PARTITION RANGE ITERATOR | | 7 | 1946 | 3 (0)| 00:00:01 | KEY | KEY | |* 5 | TABLE ACCESS FULL | T | 7 | 1946 | 3 (0)| 00:00:01 | KEY | KEY | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TX"."ID"=19) 5 - filter("TX"."D1"="T"."D1" AND "TX"."N1"="T"."N1") SQL> SELECT /*+ leading(tx) use_hash(t) */ * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND tx.id = 19; 执行计划 ---------------------------------------------------------- Plan hash value: 4162004537 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 3892 | 142 (2)| 00:00:02 | | | |* 1 | HASH JOIN | | 7 | 3892 | 142 (2)| 00:00:02 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 278 | 2 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | 1 (0)| 00:00:01 | | | | 4 | PARTITION RANGE SUBQUERY | | 10000 | 2714K| 139 (1)| 00:00:02 |KEY(SQ)|KEY(SQ)| | 5 | TABLE ACCESS FULL | T | 10000 | 2714K| 139 (1)| 00:00:02 |KEY(SQ)|KEY(SQ)| ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("TX"."D1"="T"."D1" AND "TX"."N1"="T"."N1") 3 - access("TX"."ID"=19) SQL> SELECT /*+ leading(tx) use_merge(t) */ * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND tx.id = 19; 执行计划 ---------------------------------------------------------- Plan hash value: 3857595143 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 3892 | 744 (1)| 00:00:09 | | | | 1 | MERGE JOIN | | 7 | 3892 | 744 (1)| 00:00:09 | | | | 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 278 | 2 (0)| 00:00:01 | | | |* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | 1 (0)| 00:00:01 | | | |* 4 | FILTER | | | | | | | | | 5 | PARTITION RANGE SUBQUERY | | 10000 | 2714K| 139 (1)| 00:00:02 |KEY(SQ)|KEY(SQ)| | 6 | TABLE ACCESS FULL | T | 10000 | 2714K| 139 (1)| 00:00:02 |KEY(SQ)|KEY(SQ)| ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TX"."ID"=19) 4 - filter("TX"."N1"="T"."N1" AND "TX"."D1"="T"."D1")在开启子查询消除后，HASH JOIN与MERGE JOIN选择了PARTITION RANGE SUBQUERY，从Pstart| Pstop的KEY(SQ)也可以看出使用了子查询消除。