Hi We are using version 11.2.0.4.0 version of oracle. We have two similar kind of index in a big transaction table -T1 in which ~200million records inserted daily in a conventional method from Java in multiple threads. We want to remove one of the duplicate local index to make it better along with get back ~2TB of storage space. Here table T1 is range partitioned(daily partitioned) on column "c3_dt" which is a truncated date column with no time component in it. so it means in as single partition there would be only one distinct value for column c3_dt and a single partition holds ~200million records.
As index idx1 is already having first two columns in it so its look safe and obvious to drop index idx2 in first place. Btw i see , In current queries ~90% of the time index idx1 is getting used. so i have few questions here , as we have third column in the composite index IDX1 is a truncated date column which is partition key, so i was thinking it doesn't make sense to have that column included in the local index, as for that specific partition it only have one distinct value in it. So we should drop index IDX1 keeping IDX2 intact, so we would get rid of a much bulkier index with no performance hit. Need expert suggestions regarding same if my understanding is correct or i am missing anything?
I do see that it has index IDX1 as unique which is a unique constraint maintaining data quality/integrity intact, so we may have to justify the need to drop unique index and find tradeoff between these two option.
also we are planning to first make the index invisible and then drop after sometime of observation, as in existing queries where we have some profiles in place(something as below)and idx1 is getting used, does this change will make or invalidate the profiled path or it will silently pick the index idx2 in place of idx1? Need expert suggestion about this.
q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1" "T1"."C2" "T1"."C3_DT"))]',
Index idx1 is unique index on- (c1,c2,c3_dt) and idx2 is on (c1,c2).
INDEX Idx2 is non unique index on (c1,c2)
Index Idx1 is having total size for all partitions ~2.5 TB
Index IXD2 is having total size for all partitions - 2 TB
Here column c1 is of data type VARCHAR2 and c2 is of data type Number and c3_dt is date datatype. Below are the statistics for the columns
Global statistics:-
column_name, data_type,num_distinct,density,num_nulls
C3_dt DATE 211 0.004739336492891 0
c1 VARCHAR2 1597649 6.2591971077502E-7 0
c2 NUMBER 1996800 5.00801282051282E-7 0
Partition level stats:-
column_name,num_distinct,density,num_nulls
C1 8754 0.00127388535031847 0
c2 1398016 7.1529939571507E-7 0
C3_dt 1 2.43469886548297E-9 0