Wednesday, June 26, 2013

Oracle 12c New Features - Online Statistics Gathering for bulk inserts

In Oracle 12c some bulk transactions like "create table as" and "insert into select from" will automatically collect statistics.  Previously DBAs needed to collect statistics manually after the insert, or run with statistics from previous inserts which may not properly reflect the data.

In this example we create a table, and expect it to contain 100,000 rows. 

SQL> create table my_source as select * from dba_source where rownum < 100000;

Table created.

After the table is created we can see that statistics have automatically been created, but the number seems wrong.

SQL> select num_rows from dba_tables where table_name = 'MY_SOURCE';

  NUM_ROWS
----------
     17170

Checking the table contents, we can see that the statistics are correct. 

SQL> select count(*) from my_source;

  COUNT(*)
----------
     17170

There are more than 100,000 rows in the DBA_SOURCE table, so why didn't we get that number ?

SQL> select count(*) from dba_source;

  COUNT(*)
----------
    331090

The difference between the expected row count (100,000) vs the actual (17170) can be explained by the ORIGIN_CON_ID column on the DBA_SOURCE table, which restricts our original select to only the records related to the current container.

SQL> desc dba_source
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 OWNER                                              VARCHAR2(128)
 NAME                                               VARCHAR2(128)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                              VARCHAR2(4000)
 ORIGIN_CON_ID                                      NUMBER

SQL> select count(*) from dba_source where origin_con_id = 3;

  COUNT(*)
----------
     17170