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 von John Kelly am Dezember 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 von Lisandro Fernigrini am Dezember 1, 2016 - 21:36

    Great post of a really interesting and usefull new functionality !

  3. #3 von Uwe Hesse am Dezember 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 von fouedgray am März 9, 2017 - 16:10

    Thank you for sharing this post.

  5. #5 von fouedgray am Juni 2, 2017 - 15:21

    Nice post as usual. Thanks

  6. #6 von Maya am Juli 26, 2017 - 02:24

    Is there a way in oracle to always index on the latest table partition and turn it off on the rest?

  7. #7 von PetrS am August 22, 2018 - 08:06

    Hello,
    Pretty example. What way, when I have partitioned table in dataearehouse, where primary key and unique key exists? Unique key is over natural key. I have a 4 year data partitioned by day, and only actual year can be modified, other could be read only partition?

  8. #8 von Uwe Hesse am August 22, 2018 - 08:41

    Hi,
    if a primary respectively unique key is enforced with a constraint that uses an index, then this index cannot be partial:
    https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/partition-concepts.html#GUID-256BA7EE-BF49-42DE-9B38-CD2480A73129
    You can make any table partition read only if you think that’s a good idea because no further DML is going to hit that partition:
    https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/partition-create-tables-indexes.html#GUID-9D7149B6-A2FF-47CA-8F00-47CBFD33F82B

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Diese Seite verwendet Akismet, um Spam zu reduzieren. Erfahre, wie deine Kommentardaten verarbeitet werden..