Using Index Unusable For Gaining Disk Space In Partitioned Tables

Why dedicate disk space for indexes that you are not using for data access in older partitions.

--index partition usable

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date('31.10.2011 23:45:00','dd.mm.yyyy hh24:mi:ss')

Plan
SELECT STATEMENT ALL_ROWS Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2
	3 PARTITION RANGE SINGLE Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 1 Partitions accessed #1
		2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 43.447 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 1.219.929.850 IO Cost: 43.396 Time: 2 Partition #: 2 Partitions accessed #1
			1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: "DATETIME"=TO_DATE(' 2011-10-31 23:45:00', 'syyyy-mm-dd hh24:mi:ss') Cost: 159 Cardinality: 79.677 CPU Cost: 17.926.550 IO Cost: 158 Time: 1 Partition #: 3 Partitions accessed #1

--index partition unusable, after

ALTER INDEX ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK

MODIFY PARTITION P20111031

UNUSABLE;

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date('31.10.2011 23:45:00','dd.mm.yyyy hh24:mi:ss')

Plan
SELECT STATEMENT ALL_ROWS Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9
	2 PARTITION RANGE SINGLE Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 1 Partitions accessed #1
		1 TABLE ACCESS FULL TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Filter Predicates: "DATETIME"=TO_DATE(' 2011-10-31 23:45:00', 'syyyy-mm-dd hh24:mi:ss') Cost: 257.057 Bytes: 667.772.937 Cardinality: 79.677 CPU Cost: 2.924.946.617.725 IO Cost: 135.767 Time: 9 Partition #: 2 Partitions accessed #1

--other partitions goes over indexes (usable index partitions) Beware that, if you are using bind variables, you may go with FTS over indexed partitions. You may use hints for a solution for access path, like INDEX(CELLHANDOVERS CELLHANDOVERS_PK).

 

select * from ADMIN1.RNC_CELLSTATS_DERYA_CFO where datetime=to_date('01.11.2011 23:45:00','dd.mm.yyyy hh24:mi:ss');

Plan
SELECT STATEMENT ALL_ROWS Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1
	3 PARTITION RANGE SINGLE Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 1 Partitions accessed #2
		2 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ADMIN1.RNC_CELLSTATS_DERYA_CFO Object Instance: 1 Cost: 1.810 Bytes: 24.472.520 Cardinality: 2.920 CPU Cost: 50.591.850 IO Cost: 1.808 Time: 1 Partition #: 2 Partitions accessed #2
			1 INDEX RANGE SCAN INDEX (UNIQUE) ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK Search Columns: 1 Access Predicates: "DATETIME"=TO_DATE(' 2011-11-01 23:45:00', 'syyyy-mm-dd hh24:mi:ss') Cost: 7 Cardinality: 2.920 CPU Cost: 749.500 IO Cost: 7 Time: 1 Partition #: 3 Partitions accessed #2

Unique constraint also worked for usable index partition.       

Inserting same row in the range usable index triggered below error:

ORA-00001: unique constraint (ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK) violated

Only thing to remember is: DML activity on unusable partitions is impossible, without rebuilding the index!

Inserting existing row, which must trigger unique constraint, gives unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date('31.10.2011 23:45:00','dd.mm.yyyy hh24:mi:ss'),'KRP1R01','YUNUP32');

ORA-01502: index 'ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK' or partition of such index is in unusable state

Inserting non-existent row, which must not trigger unique constraint, also gives  unusable state error:

insert into ADMIN1.RNC_CELLSTATS_DERYA_CFO(datetime,rnc,cell) values (to_date('31.10.2011 23:44:00','dd.mm.yyyy hh24:mi:ss'),'KRP1R01','YUNUP32');

ORA-01502: index 'ADMIN1.RNC_CELLSTATS_DERYA_CFO_PK' or partition of such index is in unusable state

In a 1TB sized table RNC_CELLSTATS, making unusable frees 15 GB. Thus, you may not gain big spaces from big tables. While selecting targets  focus on index segments having huge sizes.

SEGMENT_NAME	GB	# partitions
RNC_CELLSTATS	980	91
RNC_CELLSTATS_PK	15	91

Below, focusing on index segments sizes over 100G, you may earn 6 TB disk space. Data is extracted by using DBA_SEGMENTS dictionary view.

SEGMENT_NAME	Size GB	# partitions	Earned GB
CELLHANDOVERS_PK	2.666	1.100	2132
CELLHANDOVERS_IND1	859	1.100	686
CELLHANDOVERS_IND3	819	1.100	654
CELLHANDOVERS_IND2	811	1.100	648
SGSN_CDR_NDX03	725	65	362
RNC_CELLHO_PK	311	91	155
MGW_E1TTP_PK	271	91	216
RNC_GSMRELATION_PK	245	91	122
CELLIDENTITY_NDX	228	65	114
RNC_CELLHO_PK2	208	91	103
RECORDOPENINGDATE_NDX	193	65	96
INTERNAL_SOHO_DS_MISSING_NE_IX	188	31	93
ENUM_FULL_IND01	161	180	128
MGW_E1TTP_D_PK	146	91	72
NEIGHBOUR_CELL_PK	128	100	63
CELLHANDOVERS_CL_PK	123	1.100	98
CCNPERFCOUNTER_PK	122	31	60
MGW_TDMTERMGRP_PK	109	91	54
CELLHANDOVERS_CL_IND2	102	1.100	81
			6,280