MIKE@ PROD> create index idx_t_compress_id on
t_compress(object_id) compress;
Index created.
MIKE@ PROD> create index idx_t_compress_type on
t_compress(object_type) compress;
Index created.
MIKE@ PROD> create index idx_t_nocompress_id on
t_nocompress(object_id);
Index created.
MIKE@ PROD> create index idx_t_nocompress_type
on t_nocompress(object_type);
Index created.
MIKE@ PROD> select segment_name,count(extent_id)
extent_num,sum(bytes) space_size
2 from user_extents
3 where segment_name in
('IDX_T_COMPRESS_ID','IDX_T_NOCOMPRESS_ID','IDX_T_COMPRESS_TYPE','IDX_T_NOCOMPRESS_TYPE')
4 group by segment_name;
SEGMENT_NAME EXTENT_NUM SPACE_SIZE
------------------------- ---------- ----------
IDX_T_COMPRESS_TYPE 28 13631488 #object_type重复率非常高
IDX_T_NOCOMPRESS_TYPE 37 23068672
IDX_T_NOCOMPRESS_ID 33 18874368 #通过前面的代码可以看出,ID是没有重复值的
IDX_T_COMPRESS_ID 40 26214400