I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
15 November, 2017
07 November, 2017
UNIQUE LOCAL (Partitioned) Index
It is easy to create a default GLOBAL Index that is defined as a Unique Index on a column (or composite of columns) containing unique values.
But what if you have a Partitioned Table and want to create a LOCAL (i.e. equi-partitioned with the table) Index as a Unique Index ? Are there any constraints ?
Let me demonstrate a Partitioned table listing Users by Region Code.
But what if you have a Partitioned Table and want to create a LOCAL (i.e. equi-partitioned with the table) Index as a Unique Index ? Are there any constraints ?
Let me demonstrate a Partitioned table listing Users by Region Code.
SQL> create table users
2 (region_code varchar2(3),
3 username varchar2(30),
4 account_status varchar2(32),
5 created date,
6 profile varchar2(128))
7 partition by range (region_code)
8 (partition a_m values less than ('N'),
9 partition n_r values less than ('S'),
10 partition s_z values less than (MAXVALUE))
11 /
Table created.
SQL>
SQL> insert into users
2 select substr(username,1,3), username, account_status, created, profile
3 from dba_users
4 /
39 rows created.
SQL>
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('','USERS');
PL/SQL procedure successfully completed.
SQL>
SQL> col partition_name format a30
SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'USERS'
4 order by partition_position
5 /
PARTITION_NAME NUM_ROWS
------------------------------ ----------
A_M 18
N_R 9
S_Z 12
SQL>
I now verify that I can create a GLOBAL (non-partitioned) Unique Index on USERNAME.
SQL> create unique index users_username_u1 on users(username) global; Index created. SQL> drop index users_username_u1; Index dropped. SQL> create unique index users_username_u1 on users(username); Index created. SQL> drop index users_username_u1; Index dropped. SQL>
I now verify that I can create a Non-Unique LOCAL Index (being equi-partitioned, the index is partitioned by REGION_CODE). (Being equi-partitioned, the default behaviour is the Index Partition Names inherit from the Table Partition Names).
SQL> create index users_username_l1 on users(username) local; Index created. SQL> select partition_name, num_rows 2 from user_ind_partitions 3 where index_name = 'USERS_USERNAME_L1' 4 order by partition_position 5 / PARTITION_NAME NUM_ROWS ------------------------------ ---------- A_M 18 N_R 9 S_Z 12 SQL> SQL> drop index users_username_l1; Index dropped. SQL>
I've proven (with the GLOBAL Index) that USERNAME is Unique across the whole table. Can I create a Unique LOCAL Index on this column ?
SQL> create unique index users_username_u_l1 on users(username) local;
create unique index users_username_u_l1 on users(username) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
SQL>
Note the Error Message. The Partition Key must be a subset of the Unique Index columns. Let me try adding the Partition Key (in my example table, the Partition Key is a single column -- it could be a composite of multiple columns. In that case all the columns of the Partition Key must for a subset of the Unique Index).
SQL> create unique index users_rc_un_u_l1 on users(region_code, username) local; Index created. SQL> select partition_name, num_rows 2 from user_ind_partitions 3 where index_name = 'USERS_RC_UN_U_L1' 4 order by partition_position 5 / PARTITION_NAME NUM_ROWS ------------------------------ ---------- A_M 18 N_R 9 S_Z 12 SQL> drop index users_rc_un_u_l1; Index dropped. SQL> create unique index users_un_rc_u_l1 on users(username, region_code) local; Index created. SQL> select partition_name, num_rows 2 from user_ind_partitions 3 where index_name = 'USERS_UN_RC_U_L1' 4 order by partition_position 5 / PARTITION_NAME NUM_ROWS ------------------------------ ---------- A_M 18 N_R 9 S_Z 12 SQL>
It doesn't matter if I put the Partition Key (REGION_CODE) before or after the target column(s) (USERNAME) when I create the LOCAL Unique Index. What is necessary is that the Partition Key be a subset of the Unique Index definition.
.
.
.
29 September, 2017
Partitioned Indexes
Most discussions about Partitioning in Oracle are around Table Partitioning. Rarely do we come across Index Partitioning.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.
So, here is a quick listing of Index Partitioning options (these tests are in 11.2.0.4)
First, I start with a regular, non-partitioned table.
I now attempt to create an Equi-Partitioned (LOCAL) Index on it.
As expected I can't create a LOCAL index on a non-partitioned table.
Can I create any partitioned index on this table ?
I try two different GLOBAL PARTITIONed Indexes
So, I must have a MAXVALUE partition for the Index. Note that the two indexes above are now Partitioned without the table itself being partitioned.
The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.
So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.
Let me now proceed with a Partitioned Table.
First, the Equi-Partitioned (LOCAL) Index.
The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table. Index Partitions are automatically created to match the Table Partitions with the same Partition Names. It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea. Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.
Next, I define two GLOBAL Partitioned Indexes on this table.
The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.
As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not a left-prefix of the Index).
In this blog post, I haven't touched on Partial Indexing (a 12c feature).
I haven't touched on Unique LOCALly Partitioned Indexes.
I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.
So, here is a quick listing of Index Partitioning options (these tests are in 11.2.0.4)
First, I start with a regular, non-partitioned table.
SQL> create table non_partitioned 2 (id_col number, 3 data_col_1 number, 4 data_col_2 number, 5 data_col_3 varchar2(15) 6 ) 7 / Table created. SQL>
I now attempt to create an Equi-Partitioned (LOCAL) Index on it.
SQL> create index equi_part on non_partitioned (id_col) local;
create index equi_part on non_partitioned (id_col) local
*
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned
SQL>
As expected I can't create a LOCAL index on a non-partitioned table.
Can I create any partitioned index on this table ?
I try two different GLOBAL PARTITIONed Indexes
SQL> create index global_part 2 on non_partitioned (id_col) global 3 partition by range (id_col) 4 (partition p_100 values less than (101), 5 partition p_200 values less than (201) 6 ) 7 / ) * ERROR at line 6: ORA-14021: MAXVALUE must be specified for all columns SQL> SQL> create index global_part 2 on non_partitioned (id_col) global 3 partition by range (id_col) 4 (partition p_100 values less than (101), 5 partition p_200 values less than (201), 6 partition p_max values less than (MAXVALUE) 7 ) 8 / Index created. SQL> SQL> create index global_part_comp 2 on non_partitioned (id_col, data_col_3) global 3 partition by range (id_col, data_col_3) 4 (partition p_1 values less than (101,'M'), 5 partition p_2 values less than (101,MAXVALUE), 6 partition p_3 values less than (201,'M'), 7 partition p_4 values less than (201,MAXVALUE), 8 partition p_max values less than (MAXVALUE, MAXVALUE) 9 ) 10 / Index created. SQL>
So, I must have a MAXVALUE partition for the Index. Note that the two indexes above are now Partitioned without the table itself being partitioned.
SQL> select index_name, partitioned 2 from user_indexes 3 where table_name = 'NON_PARTITIONED' 4 order by 1 5 / INDEX_NAME PAR ------------------------------ --- GLOBAL_PART YES GLOBAL_PART_COMP YES SQL>
The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.
SQL> create index global_part_nonprefix
2 on non_partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
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>
So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.
Let me now proceed with a Partitioned Table.
SQL> create table partitioned 2 (id_col number, 3 data_col_1 number, 4 data_col_2 number, 5 data_col_3 varchar2(15) 6 ) 7 partition by range (id_col) 8 (partition p_100 values less than (101), 9 partition p_200 values less than (201), 10 partition p_max values less than (MAXVALUE) 11 ) 12 / Table created. SQL>
First, the Equi-Partitioned (LOCAL) Index.
SQL> create index part_equi_part 2 on partitioned (id_col) local 3 / Index created. SQL> select partition_name, partition_position 2 from user_ind_partitions 3 where index_name = 'PART_EQUI_PART' 4 order by 2 5 / PARTITION_NAME PARTITION_POSITION ------------------------------ ------------------ P_100 1 P_200 2 P_MAX 3 SQL>
The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table. Index Partitions are automatically created to match the Table Partitions with the same Partition Names. It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea. Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.
Next, I define two GLOBAL Partitioned Indexes on this table.
SQL> create index part_gbl_part 2 on partitioned (data_col_1) global 3 partition by range (data_col_1) 4 (partition p_1 values less than (1001), 5 partition p_2 values less than (2001), 6 partition p_3 values less than (3001), 7 partition p_4 values less than (4001), 8 partition p_max values less than (MAXVALUE) 9 ) 10 / Index created. SQL> create index part_gbl_part_comp 2 on partitioned (data_col_2, data_col_3) global 3 partition by range (data_col_2, data_col_3) 4 (partition p_a values less than (10, 'M'), 5 partition p_b values less than (10, MAXVALUE), 6 partition p_c values less than (20, 'M'), 7 partition p_d values less than (20, MAXVALUE), 8 partition p_e values less than (30, 'M'), 9 partition p_f values less than (30, MAXVALUE), 10 partition p_max values less than (MAXVALUE, MAXVALUE) 11 ) 12 / Index created. SQL> SQL> l 1 select index_name, partition_name, partition_position 2 from user_ind_partitions 3 where index_name in 4 (select index_name from user_indexes 5 where table_name = 'PARTITIONED' 6 ) 7* order by 1,3 SQL> / INDEX_NAME PARTITIO PARTITION_POSITION ------------------ -------- ------------------ PART_EQUI_PART P_100 1 PART_EQUI_PART P_200 2 PART_EQUI_PART P_MAX 3
PART_GBL_PART P_1 1 PART_GBL_PART P_2 2 PART_GBL_PART P_3 3 PART_GBL_PART P_4 4 PART_GBL_PART P_MAX 5
PART_GBL_PART_COMP P_A 1 PART_GBL_PART_COMP P_B 2 PART_GBL_PART_COMP P_C 3 PART_GBL_PART_COMP P_D 4 PART_GBL_PART_COMP P_E 5 PART_GBL_PART_COMP P_F 6 PART_GBL_PART_COMP P_MAX 7 15 rows selected. SQL>
The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.
As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not a left-prefix of the Index).
SQL> create index part_global_part_nonprefix
2 on partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed
SQL>
In this blog post, I haven't touched on Partial Indexing (a 12c feature).
I haven't touched on Unique LOCALly Partitioned Indexes.
I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.
Subscribe to:
Posts (Atom)