Skip to Main Content
  • Questions

  • Compression on Local Index partition

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prasad.

Asked: January 11, 2016 - 2:01 pm UTC

Last updated: January 12, 2016 - 12:40 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi,
I've a table definition, which is range partitioned and hash sub-partitioned like this:

CREATE TABLE T(
ORDER_ID VARCHAR2(30) NOT NULL,
SERVICE_NAME VARCHAR2(50) NOT NULL,
SERVICE_OPERATION VARCHAR2(50) NOT NULL,
DATA_ITEM_1 VARCHAR2(100),
DATA_ITEM_1 VARCHAR2(100),
DATA_ITEM_1 VARCHAR2(100),
DATA_ITEM_1 VARCHAR2(100),
... SO ON,
ORDER_CRT_DATE DATE GENERATED ALWAYS AS (TO_DATE(SUBSTR(ORDER_ID, 1,8), 'YYYYMMDD')) VIRTUAL
)
PARTITION BY RANGE (ORDER_CRT_DATE)
INTERVAL (Numtodsinterval(1,'day'))
SUBPARTITION BY HASH(SERVICE_NAME, SERVICE_OPERATION)
SUBPARTITIONS 64 STORE IN (USERS)
(PARTITION P_FIRST_PART VALUES LESS THAN (TO_DATE('16-DEC-2015','DD-MON-YYYY')));

And my local index is defined as:

CREATE INDEX T_IDX ON T(ORDER_CRT_DATE, SERVICE_NAME, SERVICE_OPERATION, ORDER_ID) LOCAL
TABLESPACE "USERS" COMPRESS 3;


Here's my question:

1) In the index definition, not sure if "COMPRESS 3" would compress data in columns - ORDER_CRT_DATE, SERVICE_NAME AND SERVICE_OPERATION? or would it not consider ORDER_CRT_DATE column for compression at all as it is the column on which it is range partitioned, hence the index must also use it for it's own partitioning so ORDER_CRT_DATE is not considered for compression.

The object creation seems to be working but not sure how to double check if the compression is happening precisely on those 3 columns (ORDER_CRT_DATE, SERVICE_NAME AND SERVICE_OPERATION) but not SERVICE_NAME, SERVICE_OPERATION and ORDER_ID. It the former that is preferred. Or should the index compression be "COMPRESS 2" in this case?

Any inputs is greatly appreciated. Thanks.

and Chris said...

Index compression has no impact on the table data. It only affects the data in the index itself.

Compress N compresses the first N columns in the index. So compress 3 includes:

ORDER_CRT_DATE, SERVICE_NAME, SERVICE_OPERATION


Compress 2 includes:

ORDER_CRT_DATE, SERVICE_NAME


If you want to change which columns Oracle includes, you need to recreate the index with the new column order.

If you want to compress the table data, you need to do this when defining the table:

http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT1132

If you want to compress specific columns in the table itself, you can use Hybrid Columnar Compression. You need to be on Exadata to use this though!

For further reading on index compression, check out:

https://richardfoote.wordpress.com/category/index-compression/
https://connormcdonald.wordpress.com/2015/08/05/index-compressionworking-out-the-compression-number/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Prasad Panchagnula, January 11, 2016 - 8:31 pm UTC

Thank you for your time and providing the references. I'm still struggling to understand how index compression would work in a partition & subpartition example (I totally understand it in a non-partitioned example though!!). Please bear with me while I explain it with a simple example:

If i've a following sample data:
02-JAN-16, SERVICE #1, OPERATION #1, ORD_0001
02-JAN-16, SERVICE #1, OPERATION #1, ORD_0002
02-JAN-16, SERVICE #1, OPERATION #2, ORD_0003

In simplistic terms, the LOCAL PARITIONED index data organisation could be visualised as: 

02-JAN-16  (<--PARTITION)
 SERVICE #1, OPERATION #1 (block #1)    (<--SUBPARTITION HASHED)
  ORD_0001, (ROW ID corresponding to ORD_0001 row)
  ORD_0002, (ROW ID corresponding to ORD_0002 row)
 SERVICE #1, OPERATION #2 (block #2)    (<--SUBPARTITION HASHED)
  ORD_0003, (ROW ID corresponding to ORD_0003 row)



From the above visualisation, the partition and subpartition columns (ORDER_CRT_DATE, SERVICE_NAME, SERVICE_OPERATION) are already used up to decide the partition & the block related to subpartition in which the indexed data needs to be stored. So, when it finally comes
to storing the index data in the block, would it still store the index data as shown below(just like in a non-partitioned index), so that the compression could then be applied on the first 3 columns? 

02-JAN-16  (<--PARTITION)
 SERVICE #1, OPERATION #1 (block #1)    (<--SUBPARTITION)
  02-JAN-16, SERVICE #1, OPERATION #1, ORD_0001, ROWID corresponding to ORD_0001
  02-JAN-16, SERVICE #1, OPERATION #1, ORD_0002, ROWID corresponding to ORD_0002


(OR)
would it just store only the ORDER_ID & ROWID because the first 3 columns in the index would always decide which partition & subpartition the data resides, hence it is redundant to store partition & subpartitions column data:
  
02-JAN-16  (<--PARTITION)
 SERVICE #1, OPERATION #1 (block #1)    (<--SUBPARTITION)
  ORD_0001, (ROW ID corresponding to ORD_0001 row)
  ORD_0002, (ROW ID corresponding to ORD_0002 row)


If it is the latter, then, there is no index compression right?

Thanks again for your time and I really do appreciate your inputs. Thanks.
Chris Saxon
January 12, 2016 - 12:40 am UTC

Based on your DDL, the columns are *in* the index (even though they are the partition keys as well). So within the index structure, you are going to have that column data (and hence it will be compressed).

If you had done

create index MY_IX on T (ORDER_ID) local

then this would not have been the case.

But dont forget, there might be a huge difference between

"jump to the *exact* spot in the index for ORDER_CRT_DATE = 2015-01-02 12:34:00, SERVICE_NAME = ABC, SERVICE_OPERATION = 123, in the index... and then find ORDER_ID = 10"

versus

"jump to the index *partition* for ORDER_CRT_DATE = 2015-01-02, SERVICE_NAME = ABC, SERVICE_OPERATION = 123" and then scan along that index segment for ORDER_ID = 10

I'm not saying one is better than the other...I'm saying they have *different* characteristics.

Hope this helps.

Prasad Panchagnula, January 12, 2016 - 12:23 pm UTC

Thank you for your explanation.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.