/*11g只收集分区统计信息,全局信息自动统计(未收集过全局统计信息)*/
SQL> set autotrace off
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> 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 YES
P2 20 10000 YES
P3 20 10000 YES
P4 20 10000 YES
P5 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 YES
P2 20 0 YES
P3 20 0 YES
P4 20 0 YES
P5 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 YES
P2 20 10000 YES
P3 20 10000 YES
P4 20 10000 YES
P5 101 60000 YES
SQL> set wrap off
SQL> set autotrace traceonly explain
SQL> 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>