oracle索引压缩

有的时候索引占用的空间很大，cpu使用不是很高的情况下，可以考虑压缩索引

“`
create table tmp_tables as select * from dba_tables;
create index idx_table_name on tmp_tables(table_name) online nologging;

SQL> create table tmp_tables_2 as select * from tmp_tables;

Table created.

SQL> create index idx_table_name_compress on tmp_tables_2(table_name) online nologging compress 1;

SQL> select index_name,compression,leaf_blocks from user_indexes where index_name in(‘IDX_TABLE_NAME_NOCOMPRESS’,’IDX_TABLE_NAME_COMPRESS’);

INDEX_NAME COMPRESS LEAF_BLOCKS

---

IDX_TABLE_NAME_COMPRESS ENABLED 1265
IDX_TABLE_NAME_NOCOMPRESS DISABLED 3561

看到压缩的情况还是比较可观的。
SQL> create index idx_name_rows_nocompress on tmp_tables(table_name,num_rows) online nologging;

Index created.

SQL> create index idx_name_rows_compress on tmp_tables_2(table_name,num_rows) online nologging compress 1;

Index created.

SQL> select index_name,compression,leaf_blocks from user_indexes where index_name in(‘IDX_NAME_ROWS_NOCOMPRESS’,’IDX_NAME_ROWS_COMPRESS’);

INDEX_NAME COMPRESS LEAF_BLOCKS

---

IDX_NAME_ROWS_COMPRESS ENABLED 1540
IDX_NAME_ROWS_NOCOMPRESS DISABLED 3836

“

`