Some Statistics on this Blog

This blog now has 630 posts (including this one), 1000 comments and 1.82million pageviews to date.

Partitioning -- 3c : Unique Index[es] on a Partitioned Table

Let's explore what sort of Unique Indexes you can create on a Partitioned Table.

There are three types of partitioning for Indexes :

a  Global (Non-Partitioned)

b  Global Partitioned

c  Local Partitioned

Can a Unique Index be created using either type ?

Let me start with another table, SALES_DATA_2  which has the same structure and Partition Key as SALES_DATA, except that it doesn't have the Primary Key definition that builds the Unique Index.

SQL> l
  1  CREATE TABLE SALES_DATA_2
  2 ( SALE_ID NUMBER,
  3   SALE_DATE DATE,
  4   INVOICE_NUMBER VARCHAR2(21),
  5   CUSTOMER_ID NUMBER,
  6   PRODUCT_ID NUMBER,
  7   SALE_VALUE NUMBER
  8 )
  9    TABLESPACE HEMANT
 10    PARTITION BY RANGE (SALE_DATE)
 11   (PARTITION P_2018  VALUES LESS THAN (TO_DATE(' 2019-01-01','YYYY-MM-DD'))
 12    TABLESPACE TBS_YEAR_2018 ,
 13   PARTITION P_2019 VALUES LESS THAN (TO_DATE(' 2020-01-01','YYYY-MM-DD'))
 14    TABLESPACE TBS_YEAR_2019 ,
 15   PARTITION P_2020 VALUES LESS THAN (TO_DATE(' 2021-01-01','YYYY-MM-DD'))
 16    TABLESPACE TBS_YEAR_2020 ,
 17   PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)
 18*   TABLESPACE HEMANT )
SQL> /

Table created.

SQL> 

Next, I try a Global (Non-Partitioned) Unique Index on SALE_ID.  Note that the "GLOBAL" Keyword is optional if it is Non-Partitioned.

SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id) global
  3  tablespace hemant
  4  /

Index created.

SQL>
SQL> select partitioned, status
  2  from user_indexes
  3  where index_name = upper('sales_2_uk')
  4  /

PAR STATUS
--- --------
NO  VALID

SQL> drop index sales_2_uk; 

Index dropped.

SQL> 

Effectively, this Global Index is the same as the Primary Key index on SALES_DATA that I built earlier.

Next, I try a Unique Global Partitioned Index on the same column.

SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id) global
  3  partition by range (sale_id)
  4  (partition p_1mill values less than (1000001) tablespace new_indexes,
  5   partition p_2mill values less than (2000001) tablespace new_indexes,
  6   partition p_3mill values less than (3000001) tablespace new_indexes,
  7   partition p_maxval values less than (maxvalue) tablespace new_indexes)
  8  /

Index created.

SQL>
SQL> select uniqueness, partitioned, status
  2  from user_indexes
  3  where index_name = upper('sales_2_uk')
  4  /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE    YES N/A

SQL>
SQL> l
  1  select column_position, column_name
  2  from user_part_key_columns
  3  where name = upper('sales_2_uk')
  4* order by column_position
SQL> /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
              1 SALE_ID

SQL> 
SQL> select partition_name, status
  2  from user_ind_partitions
  3  where index_name = upper('sales_2_uk')
  4  order by partition_position
  5  /

PARTITION_NAME                 STATUS
------------------------------ --------
P_1MILL                        USABLE
P_2MILL                        USABLE
P_3MILL                        USABLE
P_MAXVAL                       USABLE

SQL> 

So, that is a valid Unique Global Partitioned Index.

The next attempt is a Unique Local Partitioned Index -- i.e. partitioned by the same key as the Table.

SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id) local
  3  /
on sales_data_2 (sale_id) local
   *
ERROR at line 2:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index


SQL> !oerr ora 14039              
14039, 00000, "partitioning columns must form a subset of key columns of a UNIQUE index"
// *Cause:  User attempted to create a UNIQUE partitioned index whose
//          partitioning columns do not form a subset of its key columns
//          which is illegal
// *Action: If the user, indeed, desired to create an index whose
//          partitioning columns do not form a subset of its key columns,
//          it must be created as non-UNIQUE; otherwise, correct the
//          list of key and/or partitioning columns to ensure that the index'
//          partitioning columns form a subset of its key columns

SQL>
SQL> create unique index sales_2_uk
  2  on sales_data_2 (sale_id, sale_date) local
  3  /

Index created.

SQL> 
SQL> select uniqueness, partitioned, status
  2  from user_indexes
  3  where index_name = upper('sales_2_uk')
  4  /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE    YES N/A

SQL> select column_position, column_name
  2  from user_part_key_columns
  3  where name = upper('sales_2_uk')
  4  order by column_position
  5  /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
              1 SALE_DATE

SQL> select column_position, column_name 
  2  from user_ind_columns
  3  where index_name = upper('sales_2_uk')
  4  order by column_position
  5  /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
              1 SALE_ID
              2 SALE_DATE

SQL> 
SQL> select partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name = upper('sales_2_uk')
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
P_2018                         TBS_YEAR_2018                  USABLE
P_2019                         TBS_YEAR_2019                  USABLE
P_2020                         TBS_YEAR_2020                  USABLE
P_MAXVALUE                     HEMANT                         USABLE

SQL> 

So, a Unique Local Partitioned Index must include the Table Partition Key as a subset of the Index Key columns.  This is something you must consider when Partitioning the Table and Index both.
(Also, note how USER_PART_KEY_COLUMNS doesn't show SALE_ID as a Partition Key.  This is in 11.2.0.4)

Partitioning -- 3b : More Indexes on a Partitioned Table

In the previous blog post, I had demonstrated a Global (Non-Partitioned) Index and a Local (Partitioned) Index.  A Global Index itself may be partitioned.  A Local Index is actually Equi-Partitioned with the Tablee.

Let me demonstrate a Global Partitioned Index on the same table now.

Given the SALES_DATA table which is partitioned by SALE_DATE, I might want to support queries by CUSTOMER_ID.  What if I have millions of customers?  My CUSTOMERS table might be partitioned by CUSTOMER_ID.  Can I have an Index on SALES_DATA that is also partitioned by CUSTOMER_ID ?

SQL> l
  1  create index sales_data_glbl_part_ndx_2
  2  on sales_data (customer_id)
  3  global
  4  partition by range (customer_id)
  5  (
  6   partition p_1 values less than (501000) tablespace new_indexes,
  7   partition p_2 values less than (1000001) tablespace new_indexes,
  8   partition p_3 values less than (1500001) tablespace new_indexes,
  9   partition p_4 values less than (maxvalue)
 10* )
SQL> /

Index created.

SQL> 

This is a Global Partitioned Index which is prefixed on a left prefix (CUSTOMER_ID) of the Index Column(s). Oracle does not support Global Partitioned Indexes that are non-prefixed.  Let me demonstrated the difference between the two.

SQL> drop index sales_data_glbl_part_ndx_2;

Index dropped.

SQL> 
SQL> l
  1  create index sales_data_glbl_part_ndx_2
  2  on sales_data (customer_id, sale_date)
  3  global
  4  partition by range (customer_id)
  5  (
  6   partition p_1 values less than (501000) tablespace new_indexes,
  7   partition p_2 values less than (1000001) tablespace new_indexes,
  8   partition p_3 values less than (1500001) tablespace new_indexes,
  9   partition p_4 values less than (maxvalue)
 10* )
SQL> /

Index created.

SQL> 
SQL> 
SQL> l
  1  create index sales_data_glbl_part_ndx_3
  2  on sales_data (sale_date)
  3  global
  4  partition by range (customer_id)
  5  (
  6   partition p_1 values less than (501000) tablespace new_indexes,
  7   partition p_2 values less than (1000001) tablespace new_indexes,
  8   partition p_3 values less than (1500001) tablespace new_indexes,
  9   partition p_4 values less than (maxvalue)
 10* )
SQL> /
partition by range (customer_id)
                               *
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL> 
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause:  User attempted to create a GLOBAL non-prefixed partitioned index
//          which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
//          index, it must be created as LOCAL; otherwise, correct the list 
//          of key and/or partitioning columns to ensure that the index is 
//          prefixed

SQL> 

My first attempt above is valid because the Index is partitioned on CUSTOMER_ID although it is defined on (CUSTOMER_ID, SALE_DATE).  Thus, it is partitioned on a left prefix. The second index attempt fails because the Partition Key (CUSTOMER_ID) is not a left prefix (part of) the Index key (SALE_DATE alone).

Remember : The Index I've created is  partitioned on CUSTOMER_ID while the Table itself is partitioned on SALE_DATE.
This Index is a Global Index so any Partition Maintenance operation (TRUNCATE, DROP, SPLIT, MERGE) may result in the *entire* Index becoming UNUSABLE unless the UPDATE INDEXES clause is used in the Partition Maintenance DDL statement.  This was demonstrated with the Primary Key index being a Global Index in my earlier blog post.


What about Bitmap Indexes ?  Can a Bitmap Index on a partitioned Table be either Global or Local ?  Oracle does not support Global Bitmap Indexes.  A Bitmap Index on a Partitioned Table has to be Local.

SQL> create bitmap index sales_data_glbl_ndx_4
  2  on sales_data(product_id)
  3  /
on sales_data(product_id)
   *
ERROR at line 2:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables


SQL> !oerr ora 25122
25122, 00000, "Only LOCAL bitmap indexes are permitted on partitioned tables"
// *Cause: An attempt was made to create a global bitmap index on a partioned
//         table.
// *Action: create a local bitmap index instead.

SQL> 
SQL> create bitmap index sales_data_lcl_ndx_2
  2  on sales_data(product_id) local
  3  /

Index created.

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_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P_2018                         TBS_YEAR_2018
P_2019                         TBS_YEAR_2019
P_2020                         TBS_YEAR_2020
P_MAXVALUE                     USERS

SQL> select partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name = 'SALES_DATA_LCL_NDX_2'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
P_2018                         TBS_YEAR_2018                  USABLE
P_2019                         TBS_YEAR_2019                  USABLE
P_2020                         TBS_YEAR_2020                  USABLE
P_MAXVALUE                     USERS                          USABLE

SQL> 

As demonstrated earlier, a Local Index automatically inherits the Partition Name and Tablespace Name from the Table Partitions.

To summarise, these are the Indexes on my SALES_DATA table :

SQL> l        
  1  select index_name, index_type, partitioned, tablespace_name, status
  2  from user_indexes
  3* where table_name = 'SALES_DATA'
SQL> /

INDEX_NAME                     INDEX_TYPE                  PAR
------------------------------ --------------------------- ---
TABLESPACE_NAME                STATUS
------------------------------ --------
SYS_C0017514                   NORMAL                      NO
HEMANT                         VALID

SALES_DATA_LCL_NDX_1           NORMAL                      YES
                               N/A

SALES_DATA_GLBL_PART_NDX_2     NORMAL                      YES
                               N/A

SALES_DATA_LCL_NDX_2           BITMAP                      YES
                               N/A


SQL>

Remember that for a Partitioned Index (either Global or Local), the Tablespace Name and Status are attributes at the Partition definition level not at the Index definition.

Oracle Database Configurations using Docker and Vagrant

Oracle now makes available configurations for the Database (and other products) on both Docker and Vagrant via GitHub.

Good time to familiarize oneself with GitHub, Docker and/or Vagrant.


For the Docker configuration see : https://github.com/oracle/docker-images/tree/master/OracleDatabase

Helpful guide to installation at http://oracle-help.com/articles/prebuilt-oracle-database-18c-with-docker/


For the Vagrant configuration see : https://github.com/oracle/vagrant-boxes/tree/master/OracleDatabase

Helpful guide to installation at http://dbaparadise.com/2018/08/18c-up-and-running-in-30-minutes/  and  http://oracle-help.com/oracle-18c/oracle-database-18c-installation-with-vagrant/




(Note : The Examples have been available on GitHub for quite some time at https://github.com/oracle/oracle-db-examples )

Partitioning -- 3a : Indexes on a Partitioned Table

Building on the case study of the Range Partitioned Table from the previous Blog Post, here are some Indexes.

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

INDEX_NAME                     TABLESPACE_NAME                PAR UNIQUENES
------------------------------ ------------------------------ --- ---------
SYS_C0017514                   HEMANT                         NO  UNIQUE

SQL> 
SQL> select column_name, column_position
  2  from user_ind_columns
  3  where index_name = 'SYS_C0017514'
  4  /

COLUMN_NAME                    COLUMN_POSITION
------------------------------ ---------------
SALE_ID                                      1

SQL> 

We have an Index automatically built for the Primary Key constraint (note that the Index was created in the user's DEFAULT Tablespace).  This Index is a Global, Non-Partitioned Index.   Therefore, any Partition Maintenance operation (for a non-empty Partition) on the table may set the Index UNUSABLE unless the UPDATE INDEXES clause is used.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> alter table sales_data drop partition P_2016;

Table altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> insert into sales_data
  2  values (1001,to_date('01-FEB-2017','DD-MON-RR'),'ABC1001',1,10,1000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table sales_data drop partition P_2017;

Table altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
UNUSABLE

SQL> 
SQL> alter index SYS_C0017514 rebuild;

Index altered.

SQL> select status from user_indexes
  2  where index_name = 'SYS_C0017514'
  3  /

STATUS
--------
VALID

SQL> 

When I dropped the *empty* P_2016 Partition, the Index remained valid.  However, when I dropped the *non-empty*  P_2017 Partition (even if it has a single row and irrespective of whether Table/Partition statistics have been gathered) without the UPDATE INDEXES clause, the Index became UNUSABLE.  An UNUSABLE Unique Index will not allow fresh inserts (into *any* Partition of the table).

Next, I attempt to create a Local Partitioned Index.  Such an Index has a Partition corresponding to each Table Partition.

SQL> create index sales_data_lcl_ndx_1
  2  on sales_data (sale_date, invoice_number) local
  3  /

Index created.

SQL> select partitioned      
  2  from user_indexes
  3  where index_name = 'SALES_DATA_LCL_NDX_1'
  4  /

PAR
---
YES

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

PAR TABLESPACE_NAME                STATUS
--- ------------------------------ --------
YES                                N/A

SQL> 
SQL> select partition_name, tablespace_name, status
  2  from user_ind_partitions
  3  where index_name = 'SALES_DATA_LCL_NDX_1'
  4  order by partition_position
  5  /

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
P_2018                         TBS_YEAR_2018                  USABLE
P_2019                         TBS_YEAR_2019                  USABLE
P_2020                         TBS_YEAR_2020                  USABLE
P_MAXVALUE                     USERS                          USABLE

SQL> 

A Local Partitioned Index is created with the LOCAL keyword in the CREATE INDEX statement.
For a Partitioned Index, the TABLESPACE_NAME and STATUS attributes carry no meaning at the Index level --- these have values for each Partition.

Note how the Index Partitions were created with the same Partition Name and Tablespace Name as the Table Partitions. Similarly, any Partition Maintenance operations (DROP, MERGE, SPLIT) at the Table level will automatically be applied to the Index, dropping/creating the corresponding Index Partition(s).

In my next post, I will look at two other types of Index definitions on a Partitioned Table  (Global Partitioned Index and Bitmap Index).

Partitioning -- 2 : Simple Range Partitioning -- by DATE

Range Partitioning allows you to separate a logical table into a number of distinct physical segments, each segment holding data that maps to a range of values.
(I encourage you to read the Introduction in the first post in this series)

The simplest and most common implementation is Range Partitioning by a DATE column.

SQL> l
  1  create table sales_data
  2  (sale_id number primary key,
  3   sale_date date,
  4   invoice_number varchar2(21),
  5   customer_id number,
  6   product_id number,
  7   sale_value number)
  8  partition by range (sale_date)
  9  (partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY'))
 10   tablespace TBS_YEAR_2015,
 11  partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY'))
 12   tablespace TBS_YEAR_2016,
 13  partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY'))
 14   tablespace TBS_YEAR_2017,
 15  partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
 16   tablespace TBS_YEAR_2018,
 17  partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY'))
 18   tablespace TBS_YEAR_2019,
 19  partition P_MAXVALUE values less than (MAXVALUE)
 20   tablespace USERS
 21* )
SQL> /

Table created.

SQL> 

Here, I have created each Partition in a separate tablespace.  Note that the Partition Key (SALE_DATE) does not have to be the same as the Primary Key (SALE_ID)

I have also created a MAXVALUE Partition  (Some DBAs/Developers may mistakenly assume this to be a *default* partition.  Range Partitioning, unlike List Partitioning, does not have the concept of a "default" partition.  This simply is the Partition for incoming rows that have Partition Key value that is higher than the last (highest) defined Partition Key Upper Bound (31-Dec-2019 23:59:59 in this case)).

I can look up the data dictionary for these partitions in this manner :

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_2015          TBS_YEAR_2015
P_2016          TBS_YEAR_2016
P_2017          TBS_YEAR_2017
P_2018          TBS_YEAR_2018
P_2019          TBS_YEAR_2019
P_MAXVALUE         USERS

6 rows selected.

SQL> 

Partitions are ordered by Partition *Position*  not Name.

How do I add a new partition for data for the year 2020 ?  By "splitting" the MAXVALUE partition.

SQL> alter table sales_data                 
  2  split partition P_MAXVALUE
  3  at (to_date('01-JAN-2021','DD-MON-YYYY'))
  4  into
  5  (partition P_2020 tablespace TBS_YEAR_2020, partition P_MAXVALUE)
  6  /

Table altered.

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

PARTITION_NAME         HIGH_VALUE
------------------------------ ---------------------------------------------
P_2015          TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD
P_2016          TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD
P_2017          TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD
P_2018          TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD
P_2019          TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD
P_2020          TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD
P_MAXVALUE         MAXVALUE

7 rows selected.

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

PARTITION_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
P_2015          TBS_YEAR_2015
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

7 rows selected.

SQL> 

Note that, irrespective of the data format I specify in the CREATE or SPLIT commands, Oracle presents the Upper Bound Date (HIGH_VALUE) in it's own format, using a Gregorian Calendar.

How do I remove an older partition ?

SQL> alter table sales_data
  2  drop partition P_2015
  3  /

Table altered.

SQL> 

A DROP command is very simple.

In my next post, I will add Indexes to this table.

Partitioning -- 1 : Introduction

I am beginning a new series of Blog Posts on Partitioning in Oracle.  I plan to cover 11g and 12c.   I might add posts on changes in 18c  (which is really 12.2.0.2 currently)

First, this is my presentation at AIOUG Sangam 11
and this the corresponding article

This series of posts will have new examples, from the simple to the complex, not present in the above presentation / article.