Gather Statistics Enhancements in 12c -- 4

In 11gR2, I have demonstrated how Gather_Stats on a Global Temporary Table defined as ON COMMIT DELETE ROWS actually does a COMMIT before gathering statistics thus actually deleting the rows before gathering statistics.

The behaviour has changed in 12c.  Here's a demo :

[oracle@oel6 ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 15 15:42:21 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: hemant/hemant
Last Successful login time: Tue Aug 20 2013 22:57:13 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create global temporary table MY_GTT_DELETE_12c
  2  (object_id number, object_name varchar2(30)) on commit DELETE rows;

Table created.

SQL> insert into MY_GTT_DELETE_12c 
  2  select object_id, object_name from dba_objects
  3  where object_id is not null;

91491 rows created.

SQL> select count(*) from MY_GTT_DELETE_12c;

  COUNT(*)
----------
     91491

SQL> execute dbms_stats.gather_table_stats('','MY_GTT_DELETE_12C');

PL/SQL procedure successfully completed.

SQL> select count(*) from MY_GTT_DELETE_12C;

  COUNT(*)
----------
     91491

SQL> select num_rows, to_char(last_analyzed,'DD-MON HH24:MI:SS')
  2  from user_tables
  3  where table_name = 'MY_GTT_DELETE_12C'
  4  /

  NUM_ROWS TO_CHAR(LAST_AN
---------- ---------------


SQL> 
SQL> create global temporary table
  2  MY_GTT_PRESERVE_12C
  3  (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

SQL> insert into MY_GTT_PRESERVE_12C
  2  select object_id, object_name
  3  from dba_objects
  4  where object_id is not null;

91492 rows created.

SQL> select count(*) from MY_GTT_PRESERVE_12C;

  COUNT(*)
----------
     91492

SQL> execute dbms_stats.gather_table_stats('','MY_GTT_PRESERVE_12C');

PL/SQL procedure successfully completed.

SQL> select count(*) from MY_GTT_PRESERVE_12C;

  COUNT(*)
----------
     91492

SQL> select num_rows, to_char(last_analyzed,'DD-MON HH24:MI:SS')
  2  from user_tables
  3  where table_name = 'MY_GTT_PRESERVE_12C'
  4  /

  NUM_ROWS TO_CHAR(LAST_AN
---------- ---------------


SQL> 

Thus, the rows were "preserved" even though DBMS_STATS normally issues a COMMIT when it begins.  Apparently, additional code has been created to handle GTT.  Unfortunately, this means that DBMS_STATS does not "see" the rows -- now in both cases of ON COMMIT DELETE / PRESERVE rows.  Oracle's design is to have "private" statistics on GTTs.
.
.
.

The DEFAULT value for a column

Here's a simple demo of how a column's DEFAULT definiton behaves.

SQL> create table test_default (id_col number, data_col varchar2(5));

Table created.

SQL> insert into test_default values (1,NULL);

1 row created.

SQL> alter table test_default modify (data_col default 'YES');

Table altered.

SQL> insert into test_default select 2,'TWO' from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO

SQL> insert into test_default (id_col) select 3 from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO
         3 YES

SQL> alter table test_default modify (data_col default NULL);

Table altered.

SQL> insert into test_default (id_col) select 4 from dual;

1 row created.

SQL> select * from test_default order by 1;

    ID_COL DATA_
---------- -----
         1
         2 TWO
         3 YES
         4

SQL> 

So, for row 2, if a value is specified for the column, it overrides the default.
For row 3, when a value is NOT specified, the default applies.
The "modify (data_col default NULL)" allows me to "reset" the DEFAULT definition to allow NULLs.  That is how row 4 inserts a NULL.

Question : What if the column has a DEFAULT "YES" and I run :

insert into test_default select 2, NULL from dual;

Will the DEFAULT override the NULL ?

.
.
.