Search My Oracle Blog

Custom Search

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.

.
.
.

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