In Oracle Database 12c, there is a potentially useful
feature called partial index. This feature helps to save disk space by avoiding
having indexes for specific partitions. The partial index can be useful, for
example, during data load operations, to avoid index maintenance overhead
during the load for the partitions which are being loaded.
In 12c, the partial indexing is exposed through new syntax
for the CREATE TABLE DDL, which now supports an INDEXING attribute for each partition,
which can be ON (the default) or OFF. Likewise, the CREATE INDEX statement supports syntax
for creation of partial indexes.
When CREATE INDEX statement executes with the INDEXING
PARTIAL syntax, the partitions which have INDEXING=OFF are not indexed. To
explore the partial index feature we use a test container database named CDB1.
Container db: CDB1
Pluggable db: SALESPDB
Owner/table: SALES.sales
1. Creating a
partitioned table with selective indexing.
In this table the indexing is turned off for partition p1
and its enabled for others.
create table sales ( time_id date, quantity number(5))
partition by range (time_id)
(
partition p1 values
less than (to_date('01012014','ddmmyyyy')) indexing off,
partition p2 values
less than (to_date('01062014','ddmmyyyy')) indexing on,
partition p3 values
less than (to_date('31122014','ddmmyyyy')) indexing on,
partition p4 values
less than (maxvalue)
)
2. Create a locally
partitioned index on the time_id column (note the indexing partial syntax)
SQL>
create index test_idx on sales(time_id) indexing partial local;
Index
created.
3. Populate the table
with data
insert
into sales select to_date('01012010','ddmmyyyy')+ rownum, 100 from dual connect
by level <=5000
SQL>
/
5000
rows created.
SQL>
commit;
Commit
complete.
4. Examine the
dba_ind_partitions view to see which index partitions have a segment.
SQL>
select partition_name,segment_created, status from user_ind_partitions
2
where index_name='TEST_IDX';
PARTITION_NAME SEG STATUS
--------------------
--- --------
P1 NO UNUSABLE
P2 YES USABLE
P3 YES USABLE
P4 YES USABLE
It is seen that segments do not exist for partition p1
because indexing was turned off for that partition. Further there index
partition corresponding to the partition with indexing off is set as unusable.
Partial indexing and global indexes
Looking up the Oracle Database Concepts documentation,
Chapter # 4, Partitions, Views, and Other Schema Objects, in the section titled
Partial Indexes for Partitioned Tables, it is mentioned that partial global
indexes are not affected or invalidated by partition maintenance operations on partitions
having indexing=OFF.
From the
documentation:
Before Oracle Database 12c, an exchange
partition operation required a physical update of an associated global index to
retain it as usable. Starting with Oracle Database 12c,
if the partitions involved in a partition maintenance operation are not part of
a partial global index, then the index remains usable without requiring any
global index maintenance.
I tried to
check this out but found otherwise. Below are the steps executed.
Step #1.
First, I created a table called sales
range-partitioned on the time_id column with indexing turned off for all but
one partition.
1 create table
sales ( time_id date, region_id number(3), amt number(5))
2 partition by range ( time_id )
3 (
4 partition p0 values less than
(to_date('01012010','ddmmyyyy'))indexing on ,
5 partition p1 values less than
(to_date('01012014','ddmmyyyy')) indexing off,
6 partition p2 values less than
(to_date('01042014','ddmmyyyy')) indexing off,
7 partition p3 values less than
(to_date('01072014','ddmmyyyy')) indexing off,
8 partition p4 values less than
(to_date('01012015','ddmmyyyy')) indexing off,
9 partition p5 values less than (maxvalue)
indexing off;
Step #2.
I created a global partitioned index on the
region_id column, having 4 partitions, and enabled the partial indexing option.
SQL>
create index test_idx on sales(region_id)
2
global
3
partition by range(region_id)
4
(partition p1 values less than(2),
5
partition p2 values less than(3),
6
partition p3 values less than(4),
7
partition p4 values less than(maxvalue)
8 ) indexing partial;
Index
created.
Step #3.
I checked the status of the table and index
partitions that now existed. There are 4 index partitions corresponding to the
4 defined in the DDL. As for the table, there is NO data in the in any
partition so far; and there are a total of 6 partitions. Only partition P0 has
indexing enabled.
SQL> select partition_name,indexing from
user_tab_partitions
2 where table_name='SALES';
PARTITION_NAME
INDE
-------------------- ----
P0
ON
P1
OFF
P2
OFF
P3
OFF
P4
OFF
P5
OFF
6 rows selected.
SQL> select partition_name,segment_created,status from
user_ind_partitions
2 where index_name='TEST_IDX';
PARTITION_NAME
SEG STATUS
-------------------- --- --------
P4
YES USABLE
P3
YES USABLE
P2
YES USABLE
P1
YES USABLE
Step #4.
I prepare a temporary heap organized table to
stage the partition exchange load. I will soon load the data from this staging
table to the sales table. The staging date dates are all > 01 Jan 2010, therefore
when loaded into the sales table, it will be loaded into the table partition p1, which has indexing turned OFF.
SQL> insert into t_sales
2 select to_date('01012010','ddmmyyyy') +
rownum, 3, 100 from dual
3 connect by level <=10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from t_sales;
TIME_ID
REGION_ID AMT
--------- ---------- ----------
02-JAN-10
3 100
03-JAN-10
3 100
04-JAN-10
3 100
05-JAN-10
3 100
06-JAN-10
3 100
07-JAN-10
3 100
08-JAN-10
3 100
09-JAN-10
3 100
10-JAN-10
3 100
11-JAN-10
3 100
10 rows selected.
Step #5.
In this step, I perform the partition exchange
load operation. I do not specify a update global indexes clause because I want
to see the effect of just doing a partition exchange on the global index.
SQL>
alter table sales exchange partition p1 with table t_sales;
Table
altered.
Examining the table data for the target
partition
SQL>
select * from sales partition(p1);
TIME_ID REGION_ID AMT
---------
---------- ----------
02-JAN-10 3 100
03-JAN-10 3 100
04-JAN-10 3 100
05-JAN-10 3 100
06-JAN-10 3 100
07-JAN-10 3 100
08-JAN-10 3 100
09-JAN-10 3 100
10-JAN-10 3 100
11-JAN-10 3 100
10
rows selected.
Step #6.
Examine the status of the partitions of the
global index, they are ALL unusable now.
SQL>
select partition_name,segment_created,status from user_ind_partitions
2 where
index_name='TEST_IDX';
PARTITION_NAME SEG STATUS
--------------------
--- --------
P4 YES UNUSABLE
P3 YES UNUSABLE
P2 YES UNUSABLE
P1 YES UNUSABLE
I have done a partition exchange on a partition
with indexing=off (partition p1).
As per Oracle doc, it should not have
invalidated any of the global index partitions which was created partial.
