Displaying information about large object columns

The stored procedure sp_iqindexsize displays the size of an individual LONG BINARY and LONG VARCHAR column.

Size of a LONG BINARY column

This 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%.

Size of a LONG VARCHAR column

This 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%.