22 December, 2013

INTERVAL Partitioning

A quick demo of INTERVAL Partitioning.

(This demo in 11.2.0.2)

SQL> create table hkc_test_intvl_date
  2  (date_column date, 
  3  data_column varchar2(50))
  4  partition by range (date_column)
  5  interval (numtoyminterval(1,'MONTH'))
  6  (partition p_1 values less than (to_date('01-FEB-2013','DD-MON-YYYY')))
  7  /

Table created.

SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-JAN-2013','DD-MON-YYYY'),'First Row')
  3  /

1 row created.

SQL> col high_value format a40         
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> 
SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-FEB-2013','DD-MON-YYYY'),'Second Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> 

The automatically created partition for the February-2013 data has been given a "system-generated" name. Let's proceed with a few more rows :
SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-DEC-2013','DD-MON-YYYY'),'Third Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P62                        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> 

Interesting ! The newly created partition (SYS_P62) for the December-2013 data has a contiguous name but without any intervening partitions.  Oracle names the partitions in the order they are created.  What if we had multiple tables defined with Interval Partioning ?  What names would be assigned ?.
Partitions for intermediate date ranges with no data are NOT created !
SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-JUL-2013','DD-MON-YYYY'),'Fourth Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P63                        TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P62                        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL>

The new intervening partition for the July-2013 data has been given the name SYS_P63 because it was created *after* the SYS_P62 partition for the December-2013 data.

Let's create another table and a few more partitions.
SQL> create table hkc_test_intvl_number
  2  (id_column number,
  3  data_column varchar2(50))
  4  partition by range (id_column)
  5  interval (100)
  6  (partition P_1 values less than (101))
  7  /

Table created.

SQL> insert into hkc_test_intvl_number
  2  values (51,'Fifty One')
  3  /

1 row created.

SQL> insert into hkc_test_intvl_number
  2  values (253,'Two Hundred Fifty Three')
  3  /

1 row created.

SQL> insert into hkc_test_intvl_date
  2  values (to_date('16-APR-2013','DD-MON-YYYY'),'Fifth Row')
  3  /

1 row created.

SQL> 

Now that we have two different interval-partitioned tables with multiple partitions, let's query for their partition names.
SQL> l
  1  select table_name, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name like 'HKC_TEST_INTVL%'
  4* order by table_name, partition_position
SQL> /

TABLE_NAME            PARTITION_NAME        HIGH_VALUE
--------------------- --------------------- ----------------------------------------
HKC_TEST_INTVL_DATE   P_1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P61               TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P65               TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P63               TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P62               TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_NUMBER P_1                   101
HKC_TEST_INTVL_NUMBER SYS_P64               301

7 rows selected.

SQL> 

Notice how SYS_P64 for the new table HKC_TEST_INTVL_NUMBER was created before SYS_P65. So, partition names are not tied to the tables -- they are derived from a sequence and assigned as and when new partitions need to be created.

.
.
.

11 December, 2013

DEFAULT ON NULL on INSERT

Prior to 12c, the DEFAULT for a column would not be applied if you explicitly (or implicitly) inserted a NULL into that column.  The NULL would override the DEFAULT value  -- the DEFAULT would not get applied.

Thus in 11.2.0.3 :


SQL> create table test_null_default (id_column number, data_column varchar2(10) default 'SPACE');

Table created.

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

1 row created.

SQL> select id_column, decode(data_column,NULL,'A Null',data_column) from test_null_default;

 ID_COLUMN DECODE(DAT
---------- ----------
         1 A Null

1 row selected.
SQL> insert into test_null_default(id_column) values (2);

1 row created.

SQL> select id_column, decode(data_column,NULL,'A Null',data_column) from test_null_default;

 ID_COLUMN DECODE(DAT
---------- ----------
         1 A Null
         2 SPACE

2 rows selected.
SQL>

However, 12c has introduced a DEFAULT ON NULL clause.

Thus :

SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 11 22:19:53 2013

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

Enter user-name: hemant/hemant
Last Successful login time: Wed Dec 11 2013 22:19:41 +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 table test_null_default
  2  (id_column number, data_column varchar2(10) default on null 'SPACE');

Table created.

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

1 row created.

SQL> select * from test_null_default;

 ID_COLUMN DATA_COLUM
---------- ----------
         1 SPACE

SQL> 
SQL> insert into test_null_default (id_column) values (2);

1 row created.

SQL> select * from test_null_default;

 ID_COLUMN DATA_COLUM
---------- ----------
         1 SPACE
         2 SPACE

SQL> 

Thus, the "on null" specification applied in the case of the first row.

.
.
.

08 December, 2013

GATHER_TABLE_STATS : What SQLs does it call ?. 12c

The DBMS_STATS.GATHER_TABLE_STATS procedure actually generates and executes SQL statements to collect table, column and index statistics.

Let's see a simple example in 12c 12.1.0.1:

SQL> create table my_simple_table
  2  as select object_id as id_col, object_name as name_col, 
  3  owner as owner_col, created as date_col
  4  from dba_objects;

Table created.

SQL> select count(*) from my_simple_table;

  COUNT(*)
----------
     91493

SQL> create index my_simple_ndx on my_simple_table(id_col);

Index created.

SQL>
SQL> select name_col from my_simple_table where owner_col = 'HEMANT';

NAME_COL
------------------------------
OBJ_LIST
OBJ_LIST_2_NDX
OBJ_LIST_2
MY_GTT_DELETE_12C
MY_GTT_PRESERVE_12C
MY_SIMPLE_TABLE

6 rows selected.

SQL> select count(*) from my_simple_table where owner_col = 'SYS';

  COUNT(*)
----------
     41841

SQL> 
SQL> execute dbms_Session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','MY_SIMPLE_TABLE',cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> execute dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oel6 Desktop]$ 

So, I have setup a simple table with one index and also executed queries against one column. What are the SQLs in the trace file ? [Ignoring the recursive calls that are used for parsing and lookup on the object (MY_SIMPLE_TABLE) ]

select /*+  full(t)    no_parallel(t) no_parallel_index(t) dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl no_substrb_pad  */to_char(count("ID_COL")),
  to_char(substrb(dump(min("ID_COL"),16,0,64),1,240)),
  to_char(substrb(dump(max("ID_COL"),16,0,64),1,240)),
  to_char(count("NAME_COL")),to_char(substrb(dump(min("NAME_COL"),16,0,64),1,
  240)),to_char(substrb(dump(max("NAME_COL"),16,0,64),1,240)),
  to_char(count("OWNER_COL")),to_char(substrb(dump(min("OWNER_COL"),16,0,64),
  1,240)),to_char(substrb(dump(max("OWNER_COL"),16,0,64),1,240)),
  to_char(count("DATE_COL")),to_char(substrb(dump(min("DATE_COL"),16,0,64),1,
  240)),to_char(substrb(dump(max("DATE_COL"),16,0,64),1,240)),
  count(rowidtochar(rowid))
from
 "HEMANT"."MY_SIMPLE_TABLE" t  /* NDV,NIL,NIL,NDV,NIL,NIL,TOPN,NIL,NIL,NDV,
  NIL,NIL,RWID,U254,U254,U254,U254U*/


SELECT /*+ parallel */ TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/pos') + 1) POS, EXTRACTVALUE(VALUE(T),
  '/select_list_item/value') VAL, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/nonnulls')) NONNULLS, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/ndv')) NDV, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/split')) SPLIT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/rsize')) RSIZE, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/rowcnt')) ROWCNT, TO_NUMBER(EXTRACTVALUE(VALUE(T),
  '/select_list_item/topncnt')) TOPNCNT, EXTRACT(VALUE(T),
  '/select_list_item/topn_values').GETCLOBVAL() TOPN, NULL MINFREQ, NULL
  MAXFREQ, NULL AVGFREQ, NULL STDDEVFREQ
FROM
 TABLE(XMLSEQUENCE(EXTRACT(:B1 , '/process_result/select_list_item'))) T
  ORDER BY TOPNCNT DESC


select /*+  no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad  */ substrb(dump(substrb("OWNER_COL",1,64),16,0,64),1,240) val,
                      rowidtochar(rowid) rwid from "HEMANT"."MY_SIMPLE_TABLE" t where rowid in (chartorowid('AAAWvkAAGAAAADbAAA'),chartorowid('AAAWvkAAGAAAADbACN'),chartorowid('AAAWvkAAGAAAADdACM'),chartorowid('AAAWvkAAGAAAADhAB7'),chartorowid('AAAWvkAAGAAAAEWAAc'),chartorowid('AAAWvkAAGAAAAEaABr'),chartorowid('AAAWvkAAGAAAAFEABB'),chartorowid('AAAWvkAAGAAAAFEABC'),chartorowid('AAAWvkAAGAAAAFEABE'),chartorowid('AAAWvkAAGAAAAFEABI'),chartorowid('AAAWvkAAGAAAAFFAB8'),chartorowid('AAAWvkAAGAAAAFGAAE'),chartorowid('AAAWvkAAGAAAAFGABG'),chartorowid('AAAWvkAAGAAAAFPAAv'),chartorowid('AAAWvkAAGAAAAL4ABl'),chartorowid('AAAWvkAAGAAAAM+ABB'),chartorowid('AAAWvkAAGAAAAMRABu'),chartorowid('AAAWvkAAGAAAAMUAA9'),chartorowid('AAAWvkAAGAAAAMXAAM'),chartorowid('AAAWvkAAGAAAAMZABw'),chartorowid('AAAWvkAAGAAAAMZACE'),chartorowid('AAAWvkAAGAAAAN0AAz'),chartorowid('AAAWvkAAGAAAAN2AA4'),chartorowid('AAAWvkAAGAAAAN3AAe'),chartorowid('AAAWvkAAGAAAAN3AAm'),chartorowid('AAAWvkAAGAAAAN3ABN'),chartorowid('AAAWvkAAGAAAANPAAI'),chartorowid('AAAWvkAAGAAAANUABo'),chartorowid('AAAWvkAAGAAAANWABM'),chartorowid('AAAWvkAAGAAAANWACG'),chartorowid('AAAWvkAAGAAAANxAB4'),chartorowid('AAAWvkAAGAAAANxAB7')) order by nlssort(substrb("OWNER_COL",1,64),'NLS_SORT = binary')


select /*+  no_parallel_index(t, "MY_SIMPLE_NDX")  dbms_stats
  cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  xmlindex_sel_idx_tbl no_substrb_pad  no_expand index(t,"MY_SIMPLE_NDX") */
  count(*) as nrw,count(distinct sys_op_lbid(93157,'L',t.rowid)) as nlb,null
  as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf
from
 "HEMANT"."MY_SIMPLE_TABLE" t where "ID_COL" is not null



The first SQL is a simple Full Table Scan that, besides gathering a count of rows in the table, gathers basic column statistics : Number of Not Null values, Min value, Max value. What are the second and third SQLs ? The fourth SQL gather Index stats.

.
.
.