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

/*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>