Partitioning made easy - Part 2

In one of my previous post (http://mfzahirdba.blogspot.com/2014/12/partitioning-made-easy.html)  , I have mentioned about few of the partioning enhancements in Oracle 12c. 


In Oracle 12c , creation of partial indexes have made easy to maintain and administer.  Partial indexes helps us in saving space , index maintenance operations such as rebuilding / gathering statistics , so on. 

One way is to do that is , to define the partitioned table as indexing OFF / ON at the table level and then define indivdual partions to set indexing OFF  / ON. The second step is to define the partioned index as PARTIAL. 

Here is the example . 

For my business case , only the courses that were offered for the last three years are actively queried on ... In this case ,  I am setting these three partitions ( course_part_2013 , course_part_2014 , course_part_2015) to have index on the dataset. 


SQL> Create table course
  2  ( course_year int ,
  3    course_term varchar2(1) ,
  4    course_index varchar2(6) ,
  5    course_code varchar2(12)
  6    )
  7    indexing off
  8    PARTITION by range ( course_year)
  9    (
 10    partition course_part_2010 values less than ( 2011) indexing off,
 11    partition course_part_2011 values less than ( 2012) indexing off,
 12    partition course_part_2012 values less than ( 2013) indexing off,
 13    partition course_part_2013 values less than ( 2014) indexing on,
 14    partition course_part_2014 values less than ( 2015) indexing on,
 15    partition course_part_2015 values less than ( 2016) indexing on
 16    );

Table created.

Now , I am creating global PARTIAL index as below .

SQL>    Create index idx_course on course(course_index)  global indexing partial ;

Index created.


SQL>   Select
  2    table_name , partition_name ,  indexing
  3    from user_tab_partitions
  4    where table_name ='COURSE'  ;

TABLE_NAME           PARTITION_NAME                 INDE
-------------------- ------------------------------ ----
COURSE               COURSE_PART_2010               OFF
COURSE               COURSE_PART_2011               OFF
COURSE               COURSE_PART_2012               OFF
COURSE               COURSE_PART_2013               ON
COURSE               COURSE_PART_2014               ON
COURSE               COURSE_PART_2015               ON

6 rows selected.


SQL>  Select
  2    table_name , index_name , indexing
  3    from user_indexes
  4    where table_name ='COURSE' ;

TABLE_NAME           INDEX_NAME           INDEXIN
-------------------- -------------------- -------
COURSE               IDX_COURSE           PARTIAL



This is one of the good ones in Oracle 12c.

Partitioning made easy

One of the nice features in Oracle 12c is the easier mainteannace of partitioning operations. 

In pre-12c oracle databases , to add , split , merge the partitions ,  the commands has to be done per partition .  Starting with Oracle 12c , the operations can be done in one single statement.

Let us see an example.

We will create a table range based partitioned table called t_part with two partitions.

SQL> Create table t_part

 2  ( productcode  int ,  productname varchar2(150) , company varchar2(150))

 3  partition by range (productcode)

 4  (

 5  partition product_1000 values less than (1000) ,

 6  partition product_2000 values less than (2000)

 7  )

 8  ;

Table created.

To add partitions in prior to 12c , we would need to issue three statements as below.

SQL> alter table t_part

 2  add partition product_3000 values less than ( 3000) ;

Table altered.

SQL> alter table t_part

 2  add partition product_4000 values less than ( 4000) ;

Table altered.

SQL> alter table t_part

 2  add partition product_5000 values less than ( 5000) ;

Table altered.

For the similar operations in oracle 12c , we can do it in one statement as below. 

In the following example , I am adding five partitions in one statement.

SQL> alter table t_part

 2  add

 3  partition product_6000 values less than ( 6000) ,

 4  partition product_7000 values less than ( 7000) ,

 5  partition product_8000 values less than ( 8000) ,

 6  partition product_9000 values less than ( 9000),

 7  partition product_10000 values less than ( 10000),

 8  partition product_15000 values less than ( 15000)

 9  ;

Table altered.

SQL> insert into t_part

 2  select object_id as productcode

 3  , object_name as productname

 4  , owner as company from all_objects

 5  where object_id <15000 span="">

 6  ;

14602 rows created.

SQL> exec dbms_stats.gather_table_stats ( user , 'T_PART') ;

PL/SQL procedure successfully completed.

SQL> select  table_name , partition_name , num_rows ,

 2  last_analyzed

 3  from user_tab_partitions where table_name ='T_PART'

 4  order by 2 ;

TABLE_NAME PARTITION_NAME         NUM_ROWS LAST_ANAL

---------- -------------------- ---------- ---------

T_PART     PRODUCT_1000                889 20-DEC-14

T_PART     PRODUCT_10000               986 20-DEC-14

T_PART     PRODUCT_15000              4978 20-DEC-14

T_PART     PRODUCT_2000                924 20-DEC-14

T_PART     PRODUCT_3000               1000 20-DEC-14

T_PART     PRODUCT_4000               1000 20-DEC-14

T_PART     PRODUCT_5000               1000 20-DEC-14

T_PART     PRODUCT_6000                985 20-DEC-14

T_PART     PRODUCT_7000                944 20-DEC-14

T_PART     PRODUCT_8000                928 20-DEC-14

T_PART     PRODUCT_9000                968 20-DEC-14

11 rows selected.

Similarly , I can split the partition in one statement as well as shown below.

SQL> alter table t_part

 2  split

 3  partition product_15000

 4  into

 5  (

 6  partition product_11000 values less than ( 11000) ,

 7  partition product_12000 values less than ( 12000) ,

 8  partition product_13000 values less than ( 13000) ,

 9  partition product_14000 values less than ( 14000) ,

10  partition product_15000

11  ) ;

Table altered.

SQL> select  table_name , partition_name , num_rows ,

 2  last_analyzed

 3  from user_tab_partitions where table_name ='T_PART'

 4  order by 2 ;

TABLE_NAME PARTITION_NAME         NUM_ROWS LAST_ANAL

---------- -------------------- ---------- ---------

T_PART     PRODUCT_1000                889 20-DEC-14

T_PART     PRODUCT_10000               986 20-DEC-14

T_PART     PRODUCT_11000

T_PART     PRODUCT_12000

T_PART     PRODUCT_13000

T_PART     PRODUCT_14000

T_PART     PRODUCT_15000

T_PART     PRODUCT_2000                924 20-DEC-14

T_PART     PRODUCT_3000               1000 20-DEC-14

T_PART     PRODUCT_4000               1000 20-DEC-14

T_PART     PRODUCT_5000               1000 20-DEC-14

TABLE_NAME PARTITION_NAME         NUM_ROWS LAST_ANAL

---------- -------------------- ---------- ---------

T_PART     PRODUCT_6000                985 20-DEC-14

T_PART     PRODUCT_7000                944 20-DEC-14

T_PART     PRODUCT_8000                928 20-DEC-14

T_PART     PRODUCT_9000                968 20-DEC-14

15 rows selected.

This enhancement is one of the best in Oracle 12c.

Welcome your comments.

Minimize logging for Data Pump Import - IMPDP

Starting in Oracle 12c, we  can disable ( well ... minimize) the logging by setting the new setting in the TRANFORM parameter . Logging is not totally eliminated but it will be minimal . This setting has no effect if the database is in FORCE LOGGING mode. 

This is useful in loading large data sets or creating new databases .  With this setting , you can minimize the logging for table or index or both. 

if we look at the help at the command prompt ( impdp -help) , we can see there are few settings have added. 

11g 

TRANSFORM

Metadata transform to apply to applicable objects.

Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

12c

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, LOB_STORAGE, OID, PCTSPACE,

SEGMENT_ATTRIBUTES, STORAGE, and TABLE_COMPRESSION_CLAUSE.

Here are the examples.

In the following example , the logging is disabled for both table and index.

C:\>impdp zahir schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log  remap_schema=zahir:testuser transform=disable_archive_logging:Y

Import: Release 12.1.0.1.0 - Production on Thu Dec 11 14:33:04 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

Master table "ZAHIR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ZAHIR"."SYS_IMPORT_SCHEMA_01":  zahir/******** schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"TESTUSER" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "TESTUSER"."ZAHIRTESTL"                     12.50 MB  110350 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "ZAHIR"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Dec 11 14:33:18 2014 elapsed 0 00:00:11

In the following example , the logging is disabled for table. 

C:\>impdp zahir schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log  remap_schema=zahir:testuser transform=disable_archive_logging:Y:TABLE

Import: Release 12.1.0.1.0 - Production on Thu Dec 11 14:34:27 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

Master table "ZAHIR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ZAHIR"."SYS_IMPORT_SCHEMA_01":  zahir/******** schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y:TABLE

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"TESTUSER" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "TESTUSER"."ZAHIRTESTL" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "ZAHIR"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Thu Dec 11 14:34:31 2014 elapsed 0 00:00:01

In the following example , the logging is disabled for index.

C:\>impdp zahir schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log  remap_schema=zahir:testuser transform=disable_archive_logging:Y:INDEX

Import: Release 12.1.0.1.0 - Production on Thu Dec 11 14:34:44 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

Master table "ZAHIR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ZAHIR"."SYS_IMPORT_SCHEMA_01":  zahir/******** schemas=zahir directory=dataad dumpfile=zdata.dmp logfile=zdata.log remap_schema=zahir:testuser transform=disable_archive_logging:Y:INDEX

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"TESTUSER" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "TESTUSER"."ZAHIRTESTL" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "ZAHIR"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Thu Dec 11 14:34:47 2014 elapsed 0 00:00:01

I welcome your comments. 

Auto Create Statistics in Oracle 12c.

Exploring Oracle 12c .... 

Prior to Oracle 12c , we usually generate the table statistics  to give it to the Optimizer for generating cost effective plans.
 
In Oracle 12c  , this is done automatically.

Please see the example below .

Oracle 12c
===========

SQL> col product format a50
SQL> col version  format a15

SQL> Select * from product_component_version ;

PRODUCT                                            VERSION         STATUS
-------------------------------------------------- --------------- ---------------------------
NLSRTL                                             12.1.0.1.0      Production
Oracle Database 12c Enterprise Edition             12.1.0.1.0      64bit Production
PL/SQL                                             12.1.0.1.0      Production
TNS for 64-bit Windows:                            12.1.0.1.0      Production

SQL>

SQL> Create table t as Select * from all_objects;

Table created.


SQL> Create table t as Select * from all_objects;

Table created.

SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE   NUM_ROWS LAST_ANAL
----- ---------- ---------
T          73760 16-JUL-13


SQL> set pagesize 90
SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     ORACLE_MAINTAINED               2           1 NONE            16-JUL-13
T     EDITIONABLE                     2           1 NONE            16-JUL-13
T     SHARING                         3           1 NONE            16-JUL-13
T     EDITION_NAME                    0           0 NONE            16-JUL-13
T     NAMESPACE                       7           1 NONE            16-JUL-13
T     SECONDARY                       1           1 NONE            16-JUL-13
T     GENERATED                       2           1 NONE            16-JUL-13
T     TEMPORARY                       2           1 NONE            16-JUL-13
T     STATUS                          1           1 NONE            16-JUL-13
T     TIMESTAMP                    1133           1 NONE            16-JUL-13
T     LAST_DDL_TIME                1037           1 NONE            16-JUL-13
T     CREATED                      1061           1 NONE            16-JUL-13
T     OBJECT_TYPE                    23           1 NONE            16-JUL-13
T     DATA_OBJECT_ID                 86           1 NONE            16-JUL-13
T     OBJECT_ID                   73760           1 NONE            16-JUL-13
T     SUBOBJECT_NAME                  0           0 NONE            16-JUL-13
T     OBJECT_NAME                 40132           1 NONE            16-JUL-13
T     OWNER                          17           1 NONE            16-JUL-13

18 rows selected.




Oracle 11g
==========

SQL> set linesize 1000
SQL> set pagesize 90
SQL> col product format a50
SQL> col version  format a15
SQL> Select * from product_component_version ;

PRODUCT                                            VERSION         STATUS
-------------------------------------------------- --------------- ------------------
NLSRTL                                             11.2.0.3.0      Production
Oracle Database 11g Enterprise Edition             11.2.0.3.0      64bit Production
PL/SQL                                             11.2.0.3.0      Production
TNS for 64-bit Windows:                            11.2.0.3.0      Production

SQL> Create table t as Select * from all_objects;

Table created.


SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
T

SQL> col table_name format a5
SQL> col column_name format a20
SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     OWNER                                         NONE
T     OBJECT_NAME                                   NONE
T     SUBOBJECT_NAME                                NONE
T     OBJECT_ID                                     NONE
T     DATA_OBJECT_ID                                NONE
T     OBJECT_TYPE                                   NONE
T     CREATED                                       NONE
T     LAST_DDL_TIME                                 NONE
T     TIMESTAMP                                     NONE
T     STATUS                                        NONE
T     TEMPORARY                                     NONE
T     GENERATED                                     NONE
T     SECONDARY                                     NONE
T     NAMESPACE                                     NONE
T     EDITION_NAME                                  NONE

15 rows selected.

SQL> exec dbms_stats.gather_table_stats ( user , 'T' );

PL/SQL procedure successfully completed.

SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE   NUM_ROWS LAST_ANAL
----- ---------- ---------
T          56358 16-JUL-13

SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     OWNER                          16           1 NONE            16-JUL-13
T     OBJECT_NAME                 31094           1 NONE            16-JUL-13
T     SUBOBJECT_NAME                 12           1 NONE            16-JUL-13
T     OBJECT_ID                   56358           1 NONE            16-JUL-13
T     DATA_OBJECT_ID                 92           1 NONE            16-JUL-13
T     OBJECT_TYPE                    29           1 NONE            16-JUL-13
T     CREATED                      1122           1 NONE            16-JUL-13
T     LAST_DDL_TIME                1459           1 NONE            16-JUL-13
T     TIMESTAMP                    1429           1 NONE            16-JUL-13
T     STATUS                          2           1 NONE            16-JUL-13
T     TEMPORARY                       2           1 NONE            16-JUL-13
T     GENERATED                       2           1 NONE            16-JUL-13
T     SECONDARY                       1           1 NONE            16-JUL-13
T     NAMESPACE                      10           1 NONE            16-JUL-13
T     EDITION_NAME                    0           0 NONE            16-JUL-13

15 rows selected.