Are global indexes used when inserting directly into partition?

Question

In Oracle 12c 12.1, when inserting directly into a specific partition, are any global unique indexes I have across multiple partitions on the same table still used? Will the uniqueness constraint continue to be maintained across partitions? If not, what is the benefit of having the global index in the first place?

Answer 1

Why not a simple test if you are in doubts?

create table test_tab (
id int,
trans_date date)
PARTITION BY RANGE (trans_date) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION p_2020 VALUES LESS THAN ( DATE'2021-01-01'),
   PARTITION p_2021 VALUES LESS THAN ( DATE'2022-01-01') 
);

create unique index test_tab_ux1 on test_tab(id);

insert into test_tab partition (p_2020)
(id, trans_date) values (1, DATE'2020-01-01');

insert into test_tab partition (p_2021)
(id, trans_date) values (1, DATE'2021-01-01');
ORA-00001: unique constraint (ZZZ.TEST_TAB_UX1) violated

So, you see the index maintains the uniquness across the partitions which is expected.

You should know that it has its price - anytime you drop or truncate some partition the index gets invalid and must be rebuild (either manually or integrated while using UPDATE INDEXES).

So basically you try to avoid unique constraint on partitioned tables at all (and enforce the consistency in the maintaining process) or at least to have part of the unique key as the partition key - a case that can be covered with a local index.