Search My Oracle Blog

Custom Search

15 October, 2013

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

10 October, 2013

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 ?

.
.
.

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