The stored procedure sp_iqindexsize displays the size of an individual LONG BINARY and LONG VARCHAR column.
The following output shows a LONG BINARY column with approximately 42GB of data. The page size is 128KB. The largelob Info type is in the last row:
Username Indexname Type Info KBytes Pages Compressed Pages
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP Total 42953952 623009 622923
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP vdo 0 0 0
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP bt 0 0 0
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP garray 0 0 0
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP bm 136 2 1
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP barray 2312 41 40
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP dpstore 170872 2551 2549
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP largelob 42780632 620415 620333
In this example, the compression ratio is 42953952/(623009*128) = 53.9%.
The following output shows a LONG VARCHAR column with approximately 42GB of data. The page size is 128KB. The largelob Info type is in the last row:
Username Indexname Type Info KBytes Pages Compressed Pages
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP Total 42953952 623009 622923
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP vdo 0 0 0
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP bt 0 0 0
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP garray 0 0 0
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP bm 136 2 1
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP barray 2312 41 40
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP dpstore 170872 2551 2549
DBA test10.DBA.ASIQ_IDX_T128_C3_FP FP largelob 42780632 620415 620333
In this example, the compression ratio is 42953952/(623009*128) = 53.9%.
