oracle 压缩索引

最新推荐文章于 2023-06-08 11:30:19 发布
最新推荐文章于 2023-06-08 11:30:19 发布
阅读量2.3k 收藏
点赞数
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/feier7501/article/details/23705373
版权



创建数据：


SQL> create table tb_compress_index
  2  (
  3      user_year int not null,
  4      user_month int not null,
  5      user_date int not null,
  6      user_name varchar2(20) null
  7  );

表已创建。

SQL> DECLARE
  2    V_DATE INT;
  3    V_MONTH INT;
  4  BEGIN
  5    FOR I IN 1..100 LOOP
  6      V_DATE := I mod 30;
  7      V_DATE := V_DATE + 1;
  8      V_MONTH := I mod 12;
  9      V_MONTH := V_MONTH + 1;
 10      INSERT INTO tb_compress_index VALUES (2010, V_MONTH, V_DATE, 'user_' || I);
 11    END LOOP;
 12    COMMIT;
 13  END;
 14  /

PL/SQL 过程已成功完成。

查询： 

SQL> WITH compress_index AS (SELECT * FROM tb_compress_index) SELECT * FROM compress_index WHERE ROWNUM < 10;

 USER_YEAR USER_MONTH  USER_DATE USER_NAME
---------- ---------- ---------- --------------------
      2010          2          2 user_1
      2010          3          3 user_2
      2010          4          4 user_3
      2010          5          5 user_4
      2010          6          6 user_5
      2010          7          7 user_6
      2010          8          8 user_7
      2010          9          9 user_8
      2010         10         10 user_9

已选择9行。

SQL>

创建压缩索引，然后看执行计划： 

SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date) compress 3;

索引已创建。

SQL> set autotrace traceonly
SQL> set linesize 120
SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;

已选择8行。


执行计划
----------------------------------------------------------
Plan hash value: 2921924755

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USER_MONTH"=12)
       filter("USER_MONTH"=12)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

使用普通索引： 

SQL> drop index idx_compress_index;

索引已删除。

SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date);

索引已创建。

SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;

已选择8行。


执行计划
----------------------------------------------------------
Plan hash value: 2921924755

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USER_MONTH"=12)
       filter("USER_MONTH"=12)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         66  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL>

多测试几次


使用压缩索引

SQL> drop index idx_compress_index;

索引已删除。

SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date) compress 3;

索引已创建。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;

已选择8行。


执行计划
----------------------------------------------------------
Plan hash value: 2921924755

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USER_MONTH"=12)
       filter("USER_MONTH"=12)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
       1341  recursive calls
          0  db block gets
        264  consistent gets
         43  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;

已选择8行。


执行计划
----------------------------------------------------------
Plan hash value: 2921924755

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USER_MONTH"=12)
       filter("USER_MONTH"=12)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL>

使用普通索引 


SQL> drop index idx_compress_index;

索引已删除。

SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date);

索引已创建。

SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;

已选择8行。


执行计划
----------------------------------------------------------
Plan hash value: 2921924755

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USER_MONTH"=12)
       filter("USER_MONTH"=12)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
       1341  recursive calls
          0  db block gets
        264  consistent gets
         43  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         33  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;

已选择8行。


执行计划
----------------------------------------------------------
Plan hash value: 2921924755

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("USER_MONTH"=12)
       filter("USER_MONTH"=12)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

SQL>

所以，从性能上来看，压缩索引和普通索引没什么区别。


再看存储空间：

SQL> select index_name, compression, leaf_blocks, prefix_length from user_indexes where index_name='IDX_COMPRESS_INDEX';


INDEX_NAME                     COMPRESS LEAF_BLOCKS PREFIX_LENGTH
------------------------------ -------- ----------- -------------
IDX_COMPRESS_INDEX             ENABLED            1             3

SQL>
SQL> select segment_name, bytes from user_segments where segment_name='IDX_COMPRESS_INDEX';

SEGMENT_NAME                                                                           BYTES
--------------------------------------------------------------------------------- ----------
IDX_COMPRESS_INDEX                                                                     65536

SQL> alter index idx_compress_index rebuild nocompress;

索引已更改。

SQL> select index_name, compression, leaf_blocks, prefix_length from user_indexes where index_name='IDX_COMPRESS_INDEX';


INDEX_NAME                     COMPRESS LEAF_BLOCKS PREFIX_LENGTH
------------------------------ -------- ----------- -------------
IDX_COMPRESS_INDEX             DISABLED           1

SQL> select segment_name, bytes from user_segments where segment_name='IDX_COMPRESS_INDEX';

SEGMENT_NAME                                                                           BYTES
--------------------------------------------------------------------------------- ----------
IDX_COMPRESS_INDEX                                                                     65536

SQL>

压缩前和压缩后的BYTES，没变。


所以，压缩索引不一定能节省空间，这就像平时用rar来压缩一本pdf，从43MB压缩到42MB，压缩了没多少。


压缩索引也有几个限制条件。例如，对于非唯一索引的情况，所有列都可以进行压缩；对于唯一索引，除了最后一列以外都可以进行压缩。



feier7501
关注
专栏目录
Oracle索引3--索引键压缩
chuan_day的专栏
03-07 1049
Oracle索引3--索引键压缩 索引键压缩的是可以减小索引的大小，从而减少IO量，使扫描索引时效率更高，压缩的条件是索引的前导列或者前几个索引字段是有重复的(当然这个索引肯定是多键值索引)。比如我在这篇博客中创建的索引：http://blog.csdn.net/chuan_day/article/details/60606480。在记录行数比较大的情况下，索引键压缩是提高SQL语句性能
oracle分区表索引的压缩,ORA FAQ 性能调整系列之——压缩索引会提高性能么？-数据库专栏,ORACLE...
weixin_29972373的博客
04-04 344
will compressing my indexes improve performance ?压缩索引会提高性能么？authors name: jonathan lewisauthors email: jonathan@jlcomp.demon.co.ukdate written: 26th feb 2003oracle version(s): 8.1 – 9.2compressed inde...
oracle 索引压缩
08-03 272
oracle 索引压缩 oracle 索引压缩（key compression）是oracle 9i 中引入的一项新特性。该特性可以压缩索引或者索引组织表中的重复键值，从而节省存储空间。非分区的unique 索引和no...
oracle索引压缩
敖尔其楞的专栏
02-24 568
有的时候索引占用的空间很大，cpu使用不是很高的情况下，可以考虑压缩索引 “` create table tmp_tables as select * from dba_tables; create index idx_table_name on tmp_tables(table_name) online nologging; SQL&gt; create table tmp_tables...
oracle index压缩,Oracle索引3--索引键压缩
weixin_29585583的博客
04-04 537
Oracle索引3--索引键压缩索引键压缩的是可以减小索引的大小，从而减少IO量，使扫描索引时效率更高，压缩的条件是索引的前导列或者前几个索引字段是有重复的(当然这个索引肯定是多键值索引)。比如我在这篇博客中创建的索引：http://blog.csdn.net/chuan_day/article/details/60606480。在记录行数比较大的情况下，索引键压缩是提高sql语句性能的很有效的手...
oracle创建表联合索引,Oracle创建索引的基本规则
weixin_36316642的博客
04-04 8800
创建索引的基本规则一、B-Tree索引1. 选择索引字段的原则:在WHERE子句中最频繁使用的字段联接语句中的联接字段选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)Oracle在UNIQUE和主键字段上自动建立索引在选择性很差的字段上建索引只有在这个字段的值分布非常倾斜的情况下才有益(在这种情况下,某一,两个字段值比其它字段值少出现很多)不要在很少独特值的字段上建B-...
Oracle 创建索引的基本规则
10-23
- **索引压缩**: 索引压缩可以有效减少存储空间的占用，但在某些操作上可能会增加CPU开销。 综上所述，正确创建和维护索引是Oracle数据库性能调优的重要组成部分。通过遵循上述基本原则，可以根据具体的应用场景...
oracle 索引组织表的深入详解
12-15
Oracle 索引组织表（Index-Organized Table, IOT）是一种特殊的表类型，它将数据和索引紧密地结合在一起，以提供快速的数据访问。这种设计使得IOT特别适用于那些通过主键频繁进行查询的场景，因为它利用了主键的排序...
oracle 对表或者索引进行压缩
最新发布
SuperCrrazy的博客
06-08 387
对分区表的某分区压缩语法为：对普通表压缩：对于索引也需要进行压缩，索引也是表：
oracle index压缩,Oracle Index Key Compression索引压缩
weixin_33273250的博客
04-04 189
Index KeyCompressionOracle Database can use to compressportions of the primary key column values in a B-treeindex or an index-organized table. Key compression can greatly reduce the spaceconsumed by ...
Oracle 索引 详解
无双小宝
01-09 3843
<br />一．索引介绍<br /> 1.1 索引的创建语法： <br />CREATE UNIUQE | BITMAP INDEX <schema>.<index_name><br />      ON <schema>.<table_name><br />           (<column_name> | <expression> ASC | DESC,<br />            <column_name> | <expression> ASC | DESC,...)<br />     T
oracle压缩索引
weixin_30609287的博客
03-29 109
今天看了简单的了解了一下压缩索引，写写，为了以后更好的学习，当然肯定写的不是很全面，还得继续努力了 压缩索引顾名思义就是对索引进行压缩，对组合索引，特别是重复率高的进行压缩，那么它能带来什么好处呢？首先它占用的磁盘空间少，其次了缓冲区就可以存放更多的索引条目了，而且缓冲命中率较高，并且物理I/O 较少。也就是可以使用更少的叶块，但是同样也有缺点的，不正确的压缩会带来很大的麻烦，它需要更多的从cp...
oracle index压缩,oracle表及索引压缩
weixin_33658509的博客
04-04 191
系统内发现应用人员备份的两张历史表，通过压缩释放了近30GB的空间。Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> alter table TB_CNB...
oracle表及索引压缩
congshanlan2358的博客
05-09 279
系统内发现应用人员备份的两张历史表，通过压缩释放了近30GB的空间。Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit P...
oracle index compress,oracle12c新特性索引压缩 COMPRESS ADVANCED LOW
weixin_35886058的博客
04-04 229
从oracle 12.1.0.2版本起，创建索引时可以通过COMPRESS ADVANCED LOW对index进行压缩语法create index index_name on table_name(col_name)COMPRESS ADVANCED LOW;alter index index_nameREBUILD COMPRESS ADVANCED LOW;压缩空间对比createta...
oracle 12.1.0.2 新特性高级索引压缩
Brighter_Xiao的博客
05-30 629
oracle 12.1.0.2 新特性高级索引压缩 1 高级的索引压缩    1.1  创建前缀压缩索引 (该方法应用于索引列重复值比较多、非唯一性的情形 非12c 新功能) 1.1.1  创建索引 SQL> create  index ind_MT_AS_MSG_30_spnumber on MT_AS_MSG_30(Spnumber) ; 1.1.2  修改索引为压缩
Oracle 12c新特性之：使用高级索引压缩创建索引
congbaifu8214的博客
12-09 251
使用高级索引压缩创建索引减少了所有支持的唯一和非唯一索引的大小。 高级索引压缩显着提高了压缩率，同时仍提供对索引的有效访问。 因此，高级索引压缩在所有支持的索引上工作良好。 注: 此功能自Ora...
01_oracle知识1-压缩索引基本介绍
ligdjn的博客
10-09 138
select * from index_stats; create table t_objects as select * from all_objects; –1.索引不压缩 create index t_objects_idx on t_objects(owner,object_type,object_name); analyze index t_objects_idx validate st...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值