Posted by Just a quick post to clear up some confusion that can be seen on the partial indexing capabilities in Oracle Database 12c and above. I was at an event in the Oracle Perth office last week, and an attendee mentioned that they thought the feature was not working or was not enabled on their database, and presented the following demonstration to prove their case:
SQL> create table t ( x int, y int )
2 partition by range ( x )
3 (
4 partition p1 values less than ( 1000 ) indexing on,
5 partition p2 values less than ( 2000 ) indexing off
6 );
Table created.
SQL> insert into t
2 select rownum, rownum from dual connect by level < 2000;
1999 rows created.
SQL> create index ix on t ( x ) local;
Index created.
SQL> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'IX';
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
IX P1
IX P2
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'IX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 USABLE
At first glance, this looks counter-intuitive. I have explicitly specified that I do not want indexing on partition P2, yet after creating a local index, I still have 2 segments, one for each partition, and double-checking USER_IND_PARTITIONS tells me that they are both “fully-fledged” usable index partitions.
As per the documentation linked above, nominating the INDEXING ON / OFF at the partition level on the table definition is not the whole story. When you create the index, you need to inform the database that you wish a particular index to respect that intent. This is because you might want some indexes to be partial and others not to be.
So by slightly changing my CREATE INDEX statement, I can get the desired outcome.
SQL> drop index ix;
Index dropped.
SQL> create index ix on t ( x ) local indexing partial;
Index created.
SQL> select segment_name, partition_name
2 from user_segments
3 where segment_name = 'IX';
SEGMENT_NAME PARTITION_NAME
------------------------------ ------------------------------
IX P1
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'IX';
PARTITION_NAME STATUS
------------------------------ --------
P1 USABLE
P2 UNUSABLE
Learning is not a spectator sport 
thanks for this details.
Also Richard has gone through this partial indexing feature in detail along with a nice use case.
https://richardfoote.wordpress.com/2013/07/08/12c-partial-indexes-for-partitioned-tables-part-i-ignoreland/
nice post !!!
wanted to know if, I need to use this feature in an existing table, where indexing on/off is not defined at the partitioned level, is that possible to alter?
Yes, you can do “alter table T partition P1 indexing off” etc
thanks !!!