PARTITION RANGE SUBQUERY子查询消除

来源：互联网发布：js将数组转换成json 编辑：程序博客网时间：2024/06/07 06:20

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 tSELECT 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 padFROM dualCONNECT BY level <= 10000ORDER 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_rowsFROM user_tab_partitionsWHERE 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 explainSQL>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)也可以看出使用了子查询消除。