How Partial Indexing helps you save space in #Oracle 12c

partial

Over time certain partitions may become less popular. In 12c, you don’t have to index these partitions anymore! This can save huge amounts of space and is one of the best 12c New Features in my opinion. Really a big deal if you are working with range partitioned tables where the phenomenon of old ranges becoming unpopular is very common. Let’s have a look, first at the problem:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD';   

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22
LOCAL_OLD  Q3			   22
LOCAL_OLD  Q2			   22
LOCAL_OLD  Q1			   22

Without the New Feature, every part of the table is being indexed like shown on the below picture:

Ordinary Indexes on a partitioned table

Ordinary Indexes on a partitioned table

Say partitions Q1, Q2 and Q3 are not popular any more, only Q4 is accessed frequently. In 12c I can do this:

SQL> alter table sales_range modify partition q1 indexing off;

Table altered.

SQL> alter table sales_range modify partition q2 indexing off;

Table altered.

SQL> alter table sales_range modify partition q3 indexing off;

Table altered.

This alone doesn’t affect indexes, though. They must be created with the new INDEXING PARTIAL clause now:

SQL> drop index local_old;

Index dropped.

SQL> drop index global_old;

Index dropped.

SQL> create index local_new on sales_range(time_id) indexing partial local nologging;

Index created.

SQL> create index global_new on sales_range(name) global indexing partial nologging;

Index created.

You may notice that these commands execute much faster now because less I/O needs to be done. And there is way less space consumed:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments
     where segment_name like '%NEW';
SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
LOCAL_NEW Q4 22
GLOBAL_NEW 24

That’s because the indexes look like this now:

Partial Indexes

Partial Indexes

Instead of dropping the old index you can also change it into using the New Feature:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22
LOCAL_OLD  Q3			   22
LOCAL_OLD  Q2			   22
LOCAL_OLD  Q1			   22

SQL> alter index LOCAL_OLD indexing partial;

Index altered.

For a LOCAL index, that frees the space from the unpopular partitions immediately:

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22

That is different with a GLOBAL index:

SQL> alter index GLOBAL_OLD indexing partial;

Index altered.

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
GLOBAL_OLD			   96
LOCAL_OLD  Q4			   22

Still uses as much space as before, but now this releases space from unpopular parts of the index:

SQL> alter index global_old rebuild indexing partial;

Index altered.

SQL> select segment_name,partition_name ,bytes/1024/1024 from user_segments 
     where segment_name like '%OLD%';

SEGMENT_NA PARTITION_ BYTES/1024/1024
---------- ---------- ---------------
LOCAL_OLD  Q4			   22
GLOBAL_OLD			   24

Cool 12c New Feature, isn’t it? 🙂

, ,

  1. #1 by John Kelly on December 1, 2016 - 16:26

    This new feature sounds great, does it also mean that if you move a partition and the indexing is switched off that you do not need to rebuild the global index,.
    Also can you carry out partition maintenance like splitting and not incur the cost of having to rebuild the global index.
    I currently don’t have access to 12c so it is difficult for me to test this out.

  2. #2 by Lisandro Fernigrini on December 1, 2016 - 21:36

    Great post of a really interesting and usefull new functionality !

  3. #3 by Uwe Hesse on December 2, 2016 - 10:13

    John, when I just tested with 12.1.0.2, after an
    alter table sales_range move partition q1;
    – which was assigned indexing off – the global index turned unusable although it did not index the partition that was moved.
    Had to rebuild it with
    alter index GLOBAL_NEW rebuild indexing partial;
    Didn’t try SPLIT, but with a clear case like moving an un-indexed partition making the index unusable in spite, I have little hope it remains valid after a SPLIT.

  4. #4 by fouedgray on March 9, 2017 - 16:10

    Thank you for sharing this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: