12 August, 2018

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).




09 August, 2018

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.

27 July, 2018

18c (18.3) Installation On Premises

Documentation by @oraclebase  (Tim Hall) on installing 18c (18.3) On Premises on OEL :

Oracle Database 18c Installation On Oracle Linux 6 (OL6) and 7 (OL7)

To understand Patch Numbering in 18c, see Oracle Support Document IDs 2337415.1 and 2369376.1

.
.
.




 

Featured Post :

An "Awesome" List of Resources

Here's an "Awesome" List of Resources  https://github.com/sindresorhus/awesome . . .  

Preview Popular Posts