12c Database : Statistics Enhancements : CTAS and Insert as select now comes up with statistics

From 12c Onwards, Oracle statistics gathering has been improvised and after bulk load operations the statistics will be gather automatically. Earlier, Create as select , or Insert as select operations required manual statistics gathering, where in this has been removed now. The following bulk operations is supportive for automatic statistics gather on fly.

	CREATE TABLE AS SELECT
	
	INSERT INTO ... SELECT into an empty table using a direct path insert
	
	INSERT INTO ... SELECT into a non-empty table, partition, or subpartition

However there are Restrictions for those object that:-

	The objects must not belong to SYS.
	It is in an Oracle-owned schema such as SYS.
	It is a nested table.
	It is an index-organized table (IOT).
	It is an external table.
	It is a global temporary table defined as ON COMMIT DELETE ROWS.
	It has virtual columns.
	It has a PUBLISH preference set to FALSE.
	It is partitioned, INCREMENTAL is set to true, and extended syntax is not used.

Let's do a quick test.

	SQL> conn test/test@pdb12c
	Connected.
	SQL> create table t4 as select * from dba_objects;

	Table created.



	SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='T4';

	TABLE_NAME             NUM_ROWS LAST_ANAL
	-------------------- ---------- ---------
	T4                        90776 15-SEP-13

As You see, the last analyzed column is updated and num_rows also, In addition to that, now dba_tab_col_statistics has a new column "notes" where the value updated for this type is stats_on_load

	SQL>  select column_name,table_name,notes from dba_tab_col_statistics where table_name='T4';

	COLUMN_NAME                              TABLE_NAME           NOTES
	---------------------------------------- -------------------- -----------------------------------------
	ORACLE_MAINTAINED                        T4                   STATS_ON_LOAD
	EDITIONABLE                              T4                   STATS_ON_LOAD
	SHARING                                  T4                   STATS_ON_LOAD
	EDITION_NAME                             T4                   STATS_ON_LOAD
	NAMESPACE                                T4                   STATS_ON_LOAD
	SECONDARY                                T4                   STATS_ON_LOAD
	GENERATED                                T4                   STATS_ON_LOAD
	TEMPORARY                                T4                   STATS_ON_LOAD
	STATUS                                   T4                   STATS_ON_LOAD
	TIMESTAMP                                T4                   STATS_ON_LOAD
	LAST_DDL_TIME                            T4                   STATS_ON_LOAD

	COLUMN_NAME                              TABLE_NAME           NOTES
	---------------------------------------- -------------------- -----------------------------------------
	CREATED                                  T4                   STATS_ON_LOAD
	OBJECT_TYPE                              T4                   STATS_ON_LOAD
	DATA_OBJECT_ID                           T4                   STATS_ON_LOAD
	OBJECT_ID                                T4                   STATS_ON_LOAD
	SUBOBJECT_NAME                           T4                   STATS_ON_LOAD
	OBJECT_NAME                              T4                   STATS_ON_LOAD
	OWNER                                    T4                   STATS_ON_LOAD

	18 rows selected.

This feature is extremely helpful where lot of bulk load operations performed and required manual statistics collection.

-Thanks Geek DBA