Gather Statistics Enhancements in 12c -- 2

Building on my previous example, I demonstrate how the IAS (INSERT AS SELECT)  also includes a Gather Table Stats.

00:35:55 SQL> create table obj_list_2 tablespace hemant       
00:36:21   2  as select * from obj_list where 1=2;

Table created.

00:36:29 SQL> select count(*) from obj_list_2;

  COUNT(*)
----------
  0

00:36:40 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:36:53   2  from user_tables 
00:36:58   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:37:03 SQL> 

The table got built with 0 (zero) rows.Now, let's populate the table.

00:37:03 SQL> insert into obj_list_2
00:38:42   2  select * from obj_list
00:38:46   3  where owner = 'SYS';

41818 rows created.

00:38:54 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:39:06   2  from user_tables
00:39:08   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:39:11 SQL> commit;

Commit complete.

00:39:39 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:39:52   2  from user_tables
00:39:56   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:39:59 SQL> 

A selective insert does not update the statistics. What if we do a full IAS ?

00:39:59 SQL> insert into obj_list_2
00:41:32   2  select * from obj_list;

91465 rows created.

00:41:39 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:41:47   2  from user_tables
00:41:50   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:41:51 SQL> commit;

Commit complete.

00:41:55 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:42:04   2  from user_tables
00:42:07   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2    0 11-AUG 00:36

00:42:09 SQL> 

So, a simple IAS will not update statistics. What will cause an IAS to update statistics ?
Let's begin again.

00:43:47 SQL> truncate table obj_list_2;

Table truncated.

00:51:16 SQL> insert /*+ APPEND */ into obj_list_2
00:51:26   2  select * from obj_list;

91465 rows created.

00:51:34 SQL> commit;

Commit complete.

00:51:37 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:51:45   2  from user_tables
00:51:48   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:51

00:51:51 SQL> 

Aaha ! We have it now. A Direct Path INSERT into an empty table. This is what updates statistics when an IAS is done.

Notes :
1.  I do not expect DELETE operations to update statistics.
2.  Both the CTAS and IAS do not update Index Statistics.

Let's see the latter case.

00:51:51 SQL> truncate table obj_list_2;

Table truncated.

00:54:35 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:54:50   2  from user_tables
00:54:53   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:51

00:54:55 SQL> create index obj_list_2_ndx tablespace hemant
00:55:22   2  on obj_list_2 (owner);
create index obj_list_2_ndx tablespace hemant
                            *
ERROR at line 1:
ORA-00969: missing ON keyword


00:55:40 SQL> create index obj_list_2_ndx on obj_list_2 (owner) tablespace hemant;

Index created.

00:56:12 SQL> select index_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:56:27   2  from user_indexes
00:56:31   3  order by 1;

INDEX_NAME
--------------------------------------------------------------------------------
  NUM_ROWS TO_CHAR(LAST
---------- ------------
OBJ_LIST_2_NDX
  0 11-AUG 00:56


00:56:34 SQL> col index_name format a30
00:56:40 SQL> 
00:57:00 SQL> insert /*+ APPEND */ into obj_list_2 
00:57:15   2  select * from obj_list;

91465 rows created.

00:57:22 SQL> commit;

Commit complete.

00:57:24 SQL> select table_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:57:39   2  from user_tables
00:57:42   3  order by 1;

TABLE_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST       91465 06-AUG 22:57
OBJ_LIST_2       91465 11-AUG 00:57

00:57:45 SQL> select index_name, num_rows, to_char(last_analyzed,'DD-MON HH24:MI')
00:57:59   2  from user_indexes
00:58:03   3  order by 1;

INDEX_NAME    NUM_ROWS TO_CHAR(LAST
------------------------------ ---------- ------------
OBJ_LIST_2_NDX    0 11-AUG 00:56

00:58:07 SQL> 

So, Table Statistics did get updated at the next Direct Path Insert (see the update time as 00:57) but Index statistics did not get updated.
.
.
.