Search My Oracle Blog

Custom Search

15 November, 2017

Creating a PDB in a desired location in 12.2

A video on creating a Pluggable Database in a desired location, using the command-line.
.
.
.

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.

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.

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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com