Oracle 分区表收集分区统计信息、索引信息
--删除表
DROP TABLE TEST3;
--1.创建按日分区测试表
SQL> CREATE TABLE TEST3(ID INT, PARDATE DATE)
2 PARTITION BY RANGE (PARDATE) INTERVAL (NUMTODSINTERVAL (1,'DAY'))
3 (
4 PARTITION P_20180101 VALUES LESS THAN (TO_DATE('2018-01-02', 'YYYY-MM-DD'))
5 );
表已创建。
--2.插入2天的数据，每天100条数据
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210324','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210325','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT;
--3.模拟生产，先收集下全局统计信息，避免动态采集
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST3');
--4.继续插入3天的数据，每天100条数据
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210326','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210327','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210328','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT;
--3.仅收集新增partition的统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'AML',tabname => 'T2A_TRANS',partname => 'PT_20220214',granularity => 'PARTITION',estimate_percent => 0.001,degree =>10);
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P261',granularity => 'PARTITION');
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P262',granularity => 'PARTITION');
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P263',granularity => 'PARTITION');
--4.查看分区的统计信息
SQL> set linesize 200
SQL> SELECT P.PARTITION_NAME, P.NUM_ROWS, P.LAST_ANALYZED
2 FROM DBA_TAB_PARTITIONS P
3 WHERE TABLE_NAME = 'TEST3';
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------
P_20180101 0 26-3月 -21
SYS_P259 100 26-3月 -21
SYS_P260 100 26-3月 -21
SYS_P261 100 26-3月 -21
SYS_P262 100 26-3月 -21
SYS_P263 100 26-3月 -21
--5.查看全局的统计信息, 发现全局统计信息仍陈旧(200条)
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
2 FROM DBA_TABLES P
3 WHERE TABLE_NAME = 'TEST3';
NUM_ROWS LAST_ANALYZED
---------- --------------
200 26-3月 -21
--6.查看oracle预估返回行数是否正确,单分区准确，跨分区就不准确
SQL> set autotrace traceonly
SQL> SELECT COUNT(*)
2 FROM TEST3
3 WHERE PARDATE = TO_DATE('20210326', 'YYYYMMDD');
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE| | 100 | 800 | 3 (0)| 00:00:01 | 1181 | 1181 |
|* 3 | TABLE ACCESS FULL | TEST3 | 100 | 800 | 3 (0)| 00:00:01 | 1181 | 1181 |
-------------------------------------------------------------------------------------------------
SQL> SELECT COUNT(*)
2 FROM TEST3
3 WHERE PARDATE = TO_DATE('20210327', 'YYYYMMDD');
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE| | 100 | 800 | 3 (0)| 00:00:01 | 1182 | 1182 |
|* 3 | TABLE ACCESS FULL | TEST3 | 100 | 800 | 3 (0)| 00:00:01 | 1182 | 1182 |
-------------------------------------------------------------------------------------------------
--跨分区统计信息就不准确
SQL> SELECT COUNT(*)
2 FROM TEST3
3 WHERE PARDATE IN
4 (TO_DATE('20210326', 'YYYYMMDD'), TO_DATE('20210327', 'YYYYMMDD'));
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE INLIST| | 1 | 8 | 4 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 3 | TABLE ACCESS FULL | TEST3 | 1 | 8 | 4 (0)| 00:00:01 |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------
--7.新插入两个分区数据，
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210329','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210330','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT
--8.采用AUTO收集，仅收集增加的一个分区统计信息，另外一个不收集
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P264',granularity => 'AUTO');
--9.查看分区的统计信息(SYS_P265为空)
SQL> SELECT P.PARTITION_NAME, P.NUM_ROWS, P.LAST_ANALYZED
2 FROM DBA_TAB_PARTITIONS P
3 WHERE TABLE_NAME = 'TEST3';
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------
P_20180101 0 26-3月 -21
SYS_P259 100 26-3月 -21
SYS_P260 100 26-3月 -21
SYS_P261 100 26-3月 -21
SYS_P262 100 26-3月 -21
SYS_P263 100 26-3月 -21
SYS_P264 100 26-3月 -21
SYS_P265
--10.查看全局统计信息，发现条数为700条。意味着全局统计信息不仅仅是将新收集分区的统计
--信息直接加到全局上去，而是重新全部收集了。
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
2 FROM DBA_TABLES P
3 WHERE TABLE_NAME = 'TEST3';
NUM_ROWS LAST_ANALYZED
---------- --------------
700 26-3月 -21
--收集掉另外一个分区再继续测试
exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P265',granularity => 'AUTO');
--11.再插入两个分区数据
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210331','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
INSERT INTO TEST3
SELECT ROWNUM,TO_DATE('20210401','YYYYMMDD') FROM DBA_OBJECTS WHERE ROWNUM <= 100;
COMMIT
--11.通过测试可以通过指定granularity => 'APPROX_GLOBAL AND PARTITION'，机制是
--仅将指定分区的统计信息增加到全局信息中。
SQL> exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P266',granularity => 'APPROX_GLOBAL AND PARTITION');
--可以看到本次全局统计信息仅将SYS_P266的100条增加到全局统计信息中，而不是全部收集
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
2 FROM DBA_TABLES P
3 WHERE TABLE_NAME = 'TEST3';
NUM_ROWS LAST_ANALYZED
---------- --------------
800 26-3月 -21
SQL> exec dbms_stats.gather_table_stats(user,'TEST3',partname => 'SYS_P267',granularity => 'APPROX_GLOBAL AND PARTITION');
--查看分区统计信息
SQL> SELECT P.PARTITION_NAME, P.NUM_ROWS, P.LAST_ANALYZED
2 FROM DBA_TAB_PARTITIONS P
3 WHERE TABLE_NAME = 'TEST3';
PARTITION_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- --------------
P_20180101 0 26-3月 -21
SYS_P259 100 26-3月 -21
SYS_P260 100 26-3月 -21
SYS_P261 100 26-3月 -21
SYS_P262 100 26-3月 -21
SYS_P263 100 26-3月 -21
SYS_P264 100 26-3月 -21
SYS_P265 100 26-3月 -21
SYS_P266 100 26-3月 -21
SYS_P267 100 26-3月 -21
--查看全局统计信息
SQL> SELECT P.NUM_ROWS, P.LAST_ANALYZED
2 FROM DBA_TABLES P
3 WHERE TABLE_NAME = 'TEST3';
NUM_ROWS LAST_ANALYZED
---------- --------------
900 26-3月 -21
--12.再测试下跨分区的准确性,答案是准确的
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT * FROM TEST3 WHERE PARDATE = TO_DATE('20210331', 'YYYYMMDD');
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1100 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 100 | 1100 | 3 (0)| 00:00:01 | 1186 | 1186 |
|* 2 | TABLE ACCESS FULL | TEST3 | 100 | 1100 | 3 (0)| 00:00:01 | 1186 | 1186 |
------------------------------------------------------------------------------------------------
SQL> SELECT * FROM TEST3 WHERE PARDATE = TO_DATE('20210401', 'YYYYMMDD');
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1100 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 100 | 1100 | 3 (0)| 00:00:01 | 1187 | 1187 |
|* 2 | TABLE ACCESS FULL | TEST3 | 100 | 1100 | 3 (0)| 00:00:01 | 1187 | 1187 |
------------------------------------------------------------------------------------------------
SQL> SELECT * FROM TEST3 WHERE PARDATE IN (TO_DATE('20210331', 'YYYYMMDD'),TO_DATE('20210401', 'YYYYMMDD'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 2200 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE INLIST| | 200 | 2200 | 4 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | TEST3 | 200 | 2200 | 4 (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------
自动化学习。
