基础知识之分区表全局信息与分区信息的相互影响

来源：互联网发布：蚊子知乎编辑：程序博客网时间：2024/06/06 13:56

/*11g只收集分区统计信息，全局信息自动统计(未收集过全局统计信息)*/

SQL> set autotrace offSQL> create table p(object_id) 2  PARTITION  BY RANGE (object_id) 3  ( 4        PARTITION  p1 VALUES LESS  than (10000) , 5        PARTITION  p2 VALUES LESS  than (20000), 6        PARTITION  p3 VALUES LESS  than (30000), 7        PARTITION  p4 VALUES LESS  than (40000), 8        PARTITION  p5 VALUES LESS  than (MAXVALUE) 9  ) 10  asselect rownum from dual connect by rownum<100000; 表已创建。 SQL> set wrap off SQL> select * from p whereobject_id<1000; 执行计划----------------------------------------------------------Plan hash value: 102990640 -------------------------------------------------------------------------------- | Id | Operation              | Name |Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------- |   0| SELECT STATEMENT       |      |  999 | 12987 |     7   (0)| 00:00:01 ||   1|  PARTITION RANGE SINGLE|      |  999 | 12987 |     7   (0)| 00:00:01 ||*  2|   TABLE ACCESS FULL    | P   |   999 | 12987 |     7  (0)| 00:00:01 |--------------------------------------------------------------------------------  Predicate Information (identified byoperation id):---------------------------------------------------    2- filter("OBJECT_ID"<1000) Note-----   -dynamic sampling used for this statement (level=2)--收集分区级别统计信息SQL> execdbms_stats.gather_table_stats('YWBZ','P',granularity=>'partition'); PL/SQL 过程已成功完成。 SQL> set autotrace off--11g新增功能全局统计信息自动收集SQL> selectpartition_name,blocks,num_rows,global_stats from user_tab_partitions where table_name='P'; PARTITION_NAME                     BLOCKS   NUM_ROWS GLO------------------------------ -------------------- ---P1                                     20       9999 YESP2                                     20      10000 YESP3                                     20      10000 YESP4                                     20      10000 YESP5                                    101      60000 YES SQL> set autotrace traceonly explain--由于全局统计信息自动收集此处的统计信息比较准确SQL> select * from P whereobject_id<2001; 执行计划----------------------------------------------------------Plan hash value: 102990640 -------------------------------------------------------------------------------- | Id | Operation              | Name |Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------- |   0| SELECT STATEMENT       |      | 2000 |  8000 |     7  (0)| 00:00:01 ||   1|  PARTITION RANGE SINGLE|      | 2000 |  8000 |     7  (0)| 00:00:01 ||*  2|   TABLE ACCESS FULL    | P   |  2000 |  8000 |    7   (0)| 00:00:01 |--------------------------------------------------------------------------------  Predicate Information (identified byoperation id):---------------------------------------------------    2- filter("OBJECT_ID"<2001)

/*收集过全局统计信息后，收集分区信息后不会更新全局统计信息*/

SQL> drop table p; 表已删除。 SQL> create table p(object_id) 2  PARTITION  BY RANGE (object_id) 3  ( 4        PARTITION  p1 VALUES LESS  than (10000) , 5        PARTITION  p2 VALUES LESS  than (20000), 6        PARTITION  p3 VALUES LESS  than (30000), 7        PARTITION  p4 VALUES LESS  than (40000), 8        PARTITION  p5 VALUES LESS  than (MAXVALUE) 9  ) 10  asselect rownum from dual connect by rownum<100000; 表已创建。 SQL> delete from p; 已删除99999行。 SQL> commit; 提交完成。 SQL> execdbms_stats.gather_table_stats('YWBZ','P'); PL/SQL 过程已成功完成。 SQL> select num_rows,blocks,global_statsfrom user_tables where table_name='P';  NUM_ROWS     BLOCKS GLO---------- ---------- ---        0        181 YES SQL> selectpartition_name,blocks,num_rows,global_stats from user_tab_partitions where table_name='P'; PARTITION_NAME                     BLOCKS   NUM_ROWS GLO------------------------------ -------------------- ---P1                                     20          0 YESP2                                     20          0 YESP3                                     20          0 YESP4                                     20          0 YESP5                                    101          0 YES SQL> insert into p select rownum fromdual connect by rownum<100000; 已创建99999行。 SQL> commit; 提交完成。 SQL> execdbms_stats.gather_table_stats('YWBZ','P',granularity=>'partition'); PL/SQL 过程已成功完成。--可见收集过全局统计信息SQL> select num_rows,blocks,global_statsfrom user_tables where table_name='P';  NUM_ROWS     BLOCKS GLO---------- ---------- ---        0        181 YES SQL> selectpartition_name,blocks,num_rows,global_stats from user_tab_partitions where table_name='P'; PARTITION_NAME                     BLOCKS   NUM_ROWS GLO------------------------------ ---------- -------------P1                                     20       9999 YESP2                                     20      10000 YESP3                                     20      10000 YESP4                                     20      10000 YESP5                                   101      60000 YES SQL> set wrap offSQL> set autotrace traceonly explainSQL> select * from p whereobject_id<10000; 执行计划----------------------------------------------------------Plan hash value: 102990640 -------------------------------------------------------------------------------- | Id | Operation              | Name |Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------- |   0| SELECT STATEMENT       |      | 9999 | 39996 |     7   (0)| 00:00:01 ||   1|  PARTITION RANGE SINGLE|      | 9999 | 39996 |     7   (0)| 00:00:01 ||   2|   TABLE ACCESS FULL    | P   |  9999 | 39996 |     7  (0)| 00:00:01 |-------------------------------------------------------------------------------- --由于没有更新全局统计信息，导致执行计划信息不准确SQL> select * from p whereobject_id<10001; 执行计划----------------------------------------------------------Plan hash value: 1027262420 -------------------------------------------------------------------------------- | Id | Operation                | Name| Rows  | Bytes | Cost (%CPU)| Time-------------------------------------------------------------------------------- |   0| SELECT STATEMENT         |      |    1 |    13 |    12  (0)| 00:00:01|   1|  PARTITION RANGE ITERATOR|      |    1 |    13 |    12  (0)| 00:00:01|*  2|   TABLE ACCESS FULL      | P   |     1 |    13 |   12   (0)| 00:00:01--------------------------------------------------------------------------------  Predicate Information (identified byoperation id):---------------------------------------------------    2- filter("OBJECT_ID"<10001) SQL>