16 December, 2018

Partioning -- 13a : Relocating a Partition

When you want to / need to move a Partition to a different Tablespace (e.g. as part of a LifeCycle Management Policy), you may need downtime to relocate the Partition.  However, version 12cRelease1 allows Online Relocation of a Partition.

Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :

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

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2016                         TBS_YEAR_2016
P_2017                         TBS_YEAR_2017
P_2018                         TBS_YEAR_2018
P_2019                         TBS_YEAR_2019
P_2020                         TBS_YEAR_2020
P_MAXVALUE                     USERS

6 rows selected.

SQL> 
SQL> select index_name, tablespace_name, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  /

INDEX_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
SYS_C0017514                   HEMANT                         VALID
SALES_DATA_LCL_NDX_1                                          N/A
SALES_DATA_LCL_NDX_2                                          N/A

SQL>
SQL> l
  1  select index_name, partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name in
  4  (select index_name
  5   from user_indexes
  6   where table_name = 'SALES_DATA')
  7* order by index_name, partition_position
SQL> /

INDEX_NAME                     PARTITION_NA TABLESPACE_NAM STATUS
------------------------------ ------------ -------------- --------
SALES_DATA_LCL_NDX_1           P_2016       TBS_YEAR_2016  USABLE
SALES_DATA_LCL_NDX_1           P_2017       TBS_YEAR_2017  USABLE
SALES_DATA_LCL_NDX_1           P_2018       TBS_YEAR_2018  USABLE
SALES_DATA_LCL_NDX_1           P_2019       TBS_YEAR_2019  USABLE
SALES_DATA_LCL_NDX_1           P_2020       TBS_YEAR_2020  USABLE
SALES_DATA_LCL_NDX_1           P_MAXVALUE   USERS          USABLE
SALES_DATA_LCL_NDX_2           P_2016       TBS_YEAR_2016  USABLE
SALES_DATA_LCL_NDX_2           P_2017       TBS_YEAR_2017  USABLE
SALES_DATA_LCL_NDX_2           P_2018       TBS_YEAR_2018  USABLE
SALES_DATA_LCL_NDX_2           P_2019       TBS_YEAR_2019  USABLE
SALES_DATA_LCL_NDX_2           P_2020       TBS_YEAR_2020  USABLE
SALES_DATA_LCL_NDX_2           P_MAXVALUE   USERS          USABLE

12 rows selected.

SQL> 


I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :

SQL> alter table SALES_DATA
  2  move partition P_2016
  3  tablespace ARCHIVE_SALES_DATA
  4  /

Table altered.

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

PARTITION_NA TABLESPACE_NAME
------------ ------------------
P_2016       ARCHIVE_SALES_DATA
P_2017       TBS_YEAR_2017
P_2018       TBS_YEAR_2018
P_2019       TBS_YEAR_2019
P_2020       TBS_YEAR_2020
P_MAXVALUE    USERS

6 rows selected.

SQL> 
SQL> select index_name, tablespace_name, partitioned, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by index_name
  5  /

INDEX_NAME                     TABLESPACE_NAME    PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1                              YES N/A
SALES_DATA_LCL_NDX_2                              YES N/A
SYS_C0017514                   HEMANT             NO  UNUSABLE

SQL> alter index SYS_C0017514 rebuild ;   

Index altered.

SQL> select index_name, tablespace_name, partitioned, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by index_name
  5  /

INDEX_NAME                     TABLESPACE_NAME    PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1                              YES N/A
SALES_DATA_LCL_NDX_2                              YES N/A
SYS_C0017514                   HEMANT             NO  VALID

SQL> 
SQL> l
  1  select index_name, partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name in
  4  (select index_name
  5  from user_indexes
  6  where table_name = 'SALES_DATA')
  7* order by index_name, partition_position
SQL> /

INDEX_NAME                     PARTITION_NA TABLESPACE_NAME    STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1           P_2016       TBS_YEAR_2016      UNUSABLE
SALES_DATA_LCL_NDX_1           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_1           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_1           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_1           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_1           P_MAXVALUE   USERS              USABLE
SALES_DATA_LCL_NDX_2           P_2016       TBS_YEAR_2016      UNUSABLE
SALES_DATA_LCL_NDX_2           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_2           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_2           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_2           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_2           P_MAXVALUE   USERS              USABLE

12 rows selected.

SQL> 
SQL> alter index SALES_DATA_LCL_NDX_1
  2  rebuild partition P_2016
  3  tablespace ARCHIVE_SALES_DATA
  4  /

Index altered.

SQL> 
SQL> alter index SALES_DATA_LCL_NDX_2
  2  rebuild partition P_2016
  3  tablespace ARCHIVE_SALES_DATA
  4  /

Index altered.

SQL> 
SQL> l
  1  select index_name, partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name in
  4  (select index_name
  5  from user_indexes
  6  where table_name = 'SALES_DATA')
  7* order by index_name, partition_position
SQL> /

INDEX_NAME                     PARTITION_NA TABLESPACE_NAME    STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1           P_2016       ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_1           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_1           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_1           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_1           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_1           P_MAXVALUE   USERS              USABLE
SALES_DATA_LCL_NDX_2           P_2016       ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_2           P_2017       TBS_YEAR_2017      USABLE
SALES_DATA_LCL_NDX_2           P_2018       TBS_YEAR_2018      USABLE
SALES_DATA_LCL_NDX_2           P_2019       TBS_YEAR_2019      USABLE
SALES_DATA_LCL_NDX_2           P_2020       TBS_YEAR_2020      USABLE
SALES_DATA_LCL_NDX_2           P_MAXVALUE   USERS              USABLE

12 rows selected.

SQL> 


When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed.  So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE".  These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.

Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.

I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.


13 December, 2018

Partitioning -- 12 : Data Dictionary Queries

Here's a compilation of some useful data dictionary queries on the implementation of Partitioning.

REM  List all Partitioned Tables in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, table_name, partitioning_type, subpartitioning_type, partition_count, 
def_subpartition_count as default_subpart_count
from dba_part_tables
order by owner, table_name


REM List all Partitioned Indexes in the database (or filter by OWNER in the WHERE clause)
REM Note that the last column is the *defined* default subpartition count
select owner, index_name, table_name, partitioning_type, subpartitioning_type, partition_count, 
def_subpartition_count as default_subpart_count
from dba_part_indexes
order by owner, index_name


REM List Partition Key Columns for all Partitioned Tables 
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as Partition Key may consist of multiple columns
select owner, name, column_name
from dba_part_key_columns
where object_type = 'TABLE'
order by owner, name, column_position


REM List Partition Key Columns for Table SubPartitions 
REM (or filter by OWNER or NAME (NAME is TABLE_NAME when object_type='TABLE'))
REM Need to order by column_position as SubPartition Key may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'TABLE'
order by owner, name, column_position


REM List Partition Key Columns for Index SubPartitions 
REM (or filter by OWNER or NAME (NAME is INDEX_NAME when object_type='INDEX'))
REM Need to order by column_position as SubPartition may consist of multiple columns
select owner, name, column_name
from dba_subpart_key_columns
where object_type = 'INDEX'
order by owner, name, column_position


REM List all Table Partitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
REM Need to order by partition_position
select table_owner, table_name, partition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_partitions
order by table_owner, table_name, partition_position


REM List all Table SubPartitions (or filter by TABLE_OWNER or TABLE_NAME in the WHERE clause)
select table_owner, table_name, partition_name, subpartition_name, high_value, tablespace_name, num_rows, last_analyzed
from dba_tab_subpartitions
order by table_owner, table_name, subpartition_position


REM List all Index Partitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
REM Need to order by partition_position
REM Note : For Table Names, you have to join back to dba_indexes 
select index_owner, index_name, partition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_partitions
order by index_owner, index_name, partition_position


REM List all Index SubPartitions (or filter by INDEX_OWNER or INDEX_NAME in the WHERE clause)
select index_owner, index_name, partition_name, subpartition_name, tablespace_name, num_rows, last_analyzed
from dba_ind_subpartitions
order by index_owner, index_name, subpartition_position


I have listed only a few columns from the data dictionary views of interest.  You may extract more information by referencing other columns or joining to other data dictionary views.



02 December, 2018

Partitioning -- 11 : Composite Partitioning

Oracle allows Composite Partitioning where a Partition can, itself, be Sub-Partitioned.  Each SubPartition is a distinct segment (allocation of physical Oracle Data blocks) while the Partition itself remains a logical definition without a segment.

Composite Partitioning can comprise of :


  • Range-Hash  
  • Range-List  
  • Range-Range
  • List-Range
  • List-Hash
  • List-List
  • Interval-Hash
  • Interval-List
  • Interval-Range

Here is one example of Range-List Partitioning :

SQL> drop table my_sales_table; Table dropped. SQL>
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> 
SQL> l
  1  create table my_sales_table
  2  (invoice_id  number(16) primary key,
  3   invoice_date date,
  4   region_code varchar2(5),
  5   invoice_amount number)
  6  partition by range (invoice_date)
  7  subpartition by list (region_code)
  8  subpartition template
  9  (
 10   subpartition US values ('US') tablespace tbs_US,
 11   subpartition EMEA values ('EMEA') tablespace tbs_EMEA,
 12   subpartition ASIA values ('ASIA') tablespace tbs_ASIA,
 13   subpartition OTHERS values (DEFAULT) tablespace tbs_OTHERS)
 14  (
 15   partition p_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
 16   partition p_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
 17* )
SQL> /

Table created.

SQL> 
SQL> select table_name, partition_name, subpartition_name
  2  from user_tab_subpartitions
  3  where table_name = 'MY_SALES_TABLE'
  4  /

TABLE_NAME                     PARTITION_NAME  SUBPARTITION_NAME
------------------------------ --------------- ------------------------------
MY_SALES_TABLE                 P_2018          P_2018_US
MY_SALES_TABLE                 P_2018          P_2018_EMEA
MY_SALES_TABLE                 P_2018          P_2018_ASIA
MY_SALES_TABLE                 P_2018          P_2018_OTHERS
MY_SALES_TABLE                 P_2019          P_2019_US
MY_SALES_TABLE                 P_2019          P_2019_EMEA
MY_SALES_TABLE                 P_2019          P_2019_ASIA
MY_SALES_TABLE                 P_2019          P_2019_OTHERS

8 rows selected.

SQL> 
SQL> l
  1  select segment_name, segment_type, partition_name, tablespace_name
  2  from user_segments
  3  where segment_name = 'MY_SALES_TABLE'
  4* order by 1,2,3
SQL> /

SEGMENT_NAME    SEGMENT_TYPE       PARTITION_NAME  TABLESPACE_N
--------------- ------------------ --------------- ------------
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_ASIA     TBS_ASIA
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_EMEA     TBS_EMEA
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_OTHERS   TBS_OTHERS
MY_SALES_TABLE  TABLE SUBPARTITION P_2018_US       TBS_US
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_ASIA     TBS_ASIA
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_EMEA     TBS_EMEA
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_OTHERS   TBS_OTHERS
MY_SALES_TABLE  TABLE SUBPARTITION P_2019_US       TBS_US

8 rows selected.

SQL> 


Note how the actual SubPartition Names are auto-created by Oracle using the composite of the Partition Name  (P_2018, P_2019) and the SubPartition Name (from the SubPartition Template).

In this case, the names that are SubPartition in USER_TAB_SUBPARTITIONS appear as PARTITION_NAME in USER_SEGMENTS because each of the two logical Partitions (P_2018, P_2019) don't actually have their own Segments.


Note :  I set "deferred_segment_creation" to FALSE so that all the Segments would be created upfront even if they are not populated. "deferred_segment_creation" is an 11g feature.