从12c开始，数据库能针对批量加载的操作自动的进行在线统计信息的收集：
1）CREATE TABLE AS SELECT
2）INSERT INTO … SELECT 使用直接路径加载的方式插入到一个空表
在12C之前，我们针对大的数据量进行加载后，考虑到新能的原因都需要针对表再进行统计信息的收集，
这仍然是费时费力的操作。这个特性的引入，在我们进行数据加载的同时进行了统计信息的收集，避免
了不必要的表的二次扫描，同时也不需要再次人为的收集统计信息。
下面通过一些测试对这个特性加以澄清：
1)test1 以CREATE TABLE…AS SELECT..方式进行批量加载
create table big_table
as
select * from dba_objects;
alter session set nls_date_format=’yyyy/mm/dd hh24:mi:ss’;
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——– ———— —————- —————— ——————– ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 92715 1589 0 0 0 115 0 0 92715 2016/08/31 14:48:20 YES NO NO SHARED
select * from dba_tab_col_statistics where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS NOTES AVG_COL_LEN HISTOGRAM SCOPE
———– ————– ——————- ———— —————————————————————- ——————————————————- ———- ———- ———– ——————- ———– ———— ———- —————— ———– ————— ——-
SKYDL BIG_TABLE ORACLE_MAINTAINED 2 4E 59 .5 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 2 NONE SHARED
SKYDL BIG_TABLE EDITIONABLE 2 4E 59 .5 41933 1 2016/08/31 14:48:20 50782 YES NO STATS_ON_LOAD 2 NONE SHARED
SKYDL BIG_TABLE SHARING 3 4D45544144415441204C494E4B 4F424A454354204C494E4B .333333333 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 14 NONE SHARED
SKYDL BIG_TABLE EDITION_NAME 0 0 92715 0 2016/08/31 14:48:20 YES NO STATS_ON_LOAD 0 NONE SHARED
SKYDL BIG_TABLE NAMESPACE 24 C102 C15E .0416666667 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 3 NONE SHARED
SKYDL BIG_TABLE SECONDARY 2 4E 59 .5 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 2 NONE SHARED
SKYDL BIG_TABLE GENERATED 2 4E 59 .5 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 2 NONE SHARED
SKYDL BIG_TABLE TEMPORARY 2 4E 59 .5 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 2 NONE SHARED
SKYDL BIG_TABLE STATUS 1 56414C4944 56414C4944 1 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 6 NONE SHARED
SKYDL BIG_TABLE TIMESTAMP 1197 313939302D30382D32363A31313A32353A3030 323031362D30382D33313A31343A34383A3139 .000835421888 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 20 NONE SHARED
SKYDL BIG_TABLE LAST_DDL_TIME 1163 78660A010D2A32 7874081F0F3114 .000859845228 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 8 NONE SHARED
SKYDL BIG_TABLE CREATED 1049 78720707062802 7874081F0F3114 .000953288847 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 8 NONE SHARED
SKYDL BIG_TABLE OBJECT_TYPE 46 434C5553544552 584D4C20534348454D41 .0217391304 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 9 NONE SHARED
SKYDL BIG_TABLE DATA_OBJECT_ID 8641 80 C30A3511 .000115727346 83972 1 2016/08/31 14:48:20 8743 YES NO STATS_ON_LOAD 2 NONE SHARED
SKYDL BIG_TABLE OBJECT_ID 92715 C103 C30A3511 .0000107857413 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 5 NONE SHARED
SKYDL BIG_TABLE SUBOBJECT_NAME 344 2456534E5F31 57524D245F5044425F494E5F534E41505F4D5844425F4D58534E .00290697674 91925 1 2016/08/31 14:48:20 790 YES NO STATS_ON_LOAD 2 NONE SHARED
SKYDL BIG_TABLE OBJECT_NAME 54484 2F31303030333233645F44656C6567617465496E766F636174696F6E4861 794362437253756253616D706C696E67547970653230305F54 .0000183540122 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 25 NONE SHARED
SKYDL BIG_TABLE OWNER 35 415045585F303430323030 584442 .0285714286 0 1 2016/08/31 14:48:20 92715 YES NO STATS_ON_LOAD 6 NONE SHARED
选定了 18 行
+注意DBA_TAB_COL_STATISTICS.NOTES 字段因为批量加载被设置为STATS_ON_LOAD
2) test2 以INSERT INTO … SELECT..方式进行批量加载
drop table big_table;
create table big_table
as
select * from dba_objects
where 1=0;
–正如test1，统计信息会被收集
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——– ————– —————– —————— ——————- ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 0 0 0 0 0 0 0 0 0 2016/08/31 15:11:09 YES NO NO SHARED
–加载到一个空表
insert into big_table
select * from dba_objects;
92,715 行已插入。
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——– ————– —————– —————— ——————- ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 0 0 0 0 0 0 0 0 0 2016/08/31 15:11:09 YES NO NO SHARED
+应为没有走direct path insert，因此没有统计信息被收集
truncate table big_table;
+追加hint /*+append*/，确保direct path insert
insert /*+append*/ into big_table
select * from dba_objects;
92,715 行已插入。
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——– ———— —————– —————— ——————– ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 92715 0 0 0 0 115 0 0 92715 2016/08/31 15:15:24 YES NO NO SHARED
+INSERT INTO … SELECT 插入到一个空表且使用 direct path insert的时候统计信息才会被收集
+注意：BLOCKS仍然是0
3) test3 commit和再次插入的影响
drop table big_table;
create table big_table
as
select * from dba_objects
where 1=0;
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——– ————- —————- —————— ——————- ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 0 0 0 0 0 0 0 0 0 2016/08/31 15:27:50 YES NO NO SHARED
insert /*+append*/ into big_table
select * from dba_objects;
92,718 行已插入。
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——- ————- —————- —————— ——————- ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 92718 0 0 0 0 115 0 0 92718 2016/08/31 15:28:25 YES NO NO SHARED
commit;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——– ———— —————– —————— ——————- ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 92718 1589 0 0 0 115 0 0 92718 2016/08/31 15:29:04 NO NO NO SHARED
+在commit之后，’BLOCKS’ 和 ‘LAST_ANALYZED’ 和 ‘GLOBAL_STATS’的值才会改变
–再次插入
insert /*+append*/ into big_table
select * from dba_objects;
92,718 行已插入。
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE’;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——– ———— —————– —————— ——————- ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ——————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE TABLE 92718 1589 0 0 0 115 0 0 92718 2016/08/31 15:29:04 NO NO NO SHARED
+现在表不为空，再次以direct path插入这个表并不会收集统计再次
+这是期望的结果，因为表并不为空
4) test 4 hint NO_GATHER_OPTIMIZER_STATISTICS
‘
–假如您不想在批量加载的时候收集统计信息，可以追加hintNO_GATHER_OPTIMIZER_STATISTICS
CREATE TABLE big_table2 AS
SELECT /*+NO_GATHER_OPTIMIZER_STATISTICS */* FROM dba_objects;
select * from DBA_TAB_STATISTICS where owner=’SKYDL’ and table_name=’BIG_TABLE2′;
OWNER TABLE_NAME PARTITION_NAME PARTITION_POSITION SUBPARTITION_NAME SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO SAMPLE_SIZE LAST_ANALYZED GLOBAL_STATS USER_STATS STATTYPE_LOCKED STALE_STATS SCOPE
——— ————– —————– —————— ——————- ——————— ———— ———- ———- ———— ———- ———- ———– ————————- ——————- —————– ——————- ———– ————- ———— ———- ————— ———– ——-
SKYDL BIG_TABLE2 TABLE NO NO SHARED