This blog now has 630 posts (including this one), 1000 comments and 1.82million pageviews to date.
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
31 August, 2018
26 August, 2018
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.
Next, I try a Global (Non-Partitioned) Unique Index on SALE_ID. Note that the "GLOBAL" Keyword is optional if it is Non-Partitioned.
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.
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.
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)
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)
18 August, 2018
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 ?
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.
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.
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 :
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.
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.
Subscribe to:
Posts (Atom)