Environment info:
Oracle RDBMS version 19.18 running on RHEL 8.10
I have a non-partitioned table named ORDER_DETAIL which is a critical part in Order processing/Order work flow.
ORDER_DETAIL is 250 GB in size and has 325 million rows. Every weekend, the data is archived to 'arch' tables to keep the table size under control.
It has 4 indexes and I am going to create the 5th index to speed up some important SQLs.
The newly proposed index's DDL will be like below.
create index idx5_order_detail on order_detail (proc_centre_code, region_code) online tablespace ord_idx_tbs ;
I have noticed that, out of the 325 million rows in ORDER_DETAIL, only 1/10th of proc_centre_code column's values are unique and the rest are duplicates.
So, I thought of using COMPRESS 1 for this index.
I have 3 questions:
Question1. ORDER_DETAIL is an INSERT intensive table which sees around 2 million INSERTs an hour during business hours.
So, would Index compression (as described above) slow down the index maintenance and thereby slow down the INSERTs itself ? Business would be very unhappy if this is the case as INSERTs to ORDER_DETAIL should happen seamlessly for the order processing.
Question2. Having only 1/10th of rows being unique (i.e 90% of the rows are duplicates) make a column a good candidate for Index compression (which I believe is effectively deduplication) ?
Question3. (Not so relevant question at all as it licensing related)
Does index compression using COMPRESS 1 come under Advanced compression license ?