Search My Oracle Blog

Custom Search

11 August, 2013

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.
.
.
.
 

1 comment:

Mahir M. Quluzade said...

Thanks for sharing Hemant! It is very interesting situation.

Mahir

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016