oracle dml与索引index(一)
测试dml与索引的关系
SQL> desc user_indexes;
Name Type Nullable Default Comments
----------------------- -------------- -------- ------- --------------------------------------------------------------------------------------
INDEX_NAME VARCHAR2(30) Name of the index
INDEX_TYPE VARCHAR2(27) Y
TABLE_OWNER VARCHAR2(30) Owner of the indexed object
TABLE_NAME VARCHAR2(30) Name of the indexed object
TABLE_TYPE VARCHAR2(11) Y Type of the indexed object --可知是b-tree index or bitmap index or function-based index
UNIQUENESS VARCHAR2(9) Y Uniqueness status of the index: "UNIQUE", "NONUNIQUE", or "BITMAP"
COMPRESSION VARCHAR2(8) Y Compression property of the index: "ENABLED", "DISABLED", or NULL --是否压缩标识
PREFIX_LENGTH NUMBER Y Number of key columns in the prefix used for compression
TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace containing the index
INI_TRANS NUMBER Y Initial number of transactions --索引的存储参数ini_trans与max_trans与表独立配置
MAX_TRANS NUMBER Y Maximum number of transactions
INITIAL_EXTENT NUMBER Y Size of the initial extent in bytes
NEXT_EXTENT NUMBER Y Size of secondary extents in bytes
MIN_EXTENTS NUMBER Y Minimum number of extents allowed in the segment
MAX_EXTENTS NUMBER Y Maximum number of extents allowed in the segment
PCT_INCREASE NUMBER Y Percentage increase in extent size --在extent每次增加的比例
PCT_THRESHOLD NUMBER Y Threshold percentage of block space allowed per index entry --为每个索引行在数据块中分配的最大空间比例
INCLUDE_COLUMN NUMBER Y User column-id for last column to be included in index-only table top index --
FREELISTS NUMBER Y Number of process freelists allocated in this segment
FREELIST_GROUPS NUMBER Y Number of freelist groups allocated to this segment
PCT_FREE NUMBER Y Minimum percentage of free space in a block
LOGGING VARCHAR2(3) Y Logging attribute
BLEVEL NUMBER Y B-Tree level --blevel索引树的高度；
LEAF_BLOCKS NUMBER Y The number of leaf blocks in the index --叶块的个数
DISTINCT_KEYS NUMBER Y The number of distinct keys in the index --每个索引中不同键的数量
AVG_LEAF_BLOCKS_PER_KEY NUMBER Y The average number of leaf blocks per key --每个键叶块的平均数量
AVG_DATA_BLOCKS_PER_KEY NUMBER Y The average number of data blocks per key --每个键数据块的平均数量
CLUSTERING_FACTOR NUMBER Y A measurement of the amount of (dis)order of the table this index is for --索引与表匹配比对的排序数量
STATUS VARCHAR2(8) Y Whether the non-partitioned index is in USABLE or not
NUM_ROWS NUMBER Y Number of rows in the index --索引记录数
SAMPLE_SIZE NUMBER Y The sample size used in analyzing this index －－采样比例；
LAST_ANALYZED DATE Y The date of the most recent time this index was analyzed
DEGREE VARCHAR2(40) Y The number of threads per instance for scanning the partitioned index --扫描分区索引每个实例所使用的线程数
INSTANCES VARCHAR2(40) Y The number of instances across which the partitioned index is to be scanned --扫描扫区索引所跨越的实例个数
PARTITIONED VARCHAR2(3) Y Is this index partitioned? YES or NO --partitioned表明是否为分区索引
TEMPORARY VARCHAR2(1) Y Can the current session only see data that it place in this object itself? --当前会话是否仅能查看属于它自己的对象
GENERATED VARCHAR2(1) Y Was the name of this index system generated? --索引名称是否由系统自动产生
SECONDARY VARCHAR2(1) Y Is the index object created as part of icreate for domain indexes? --此索引是否为域索引的一部分
BUFFER_POOL VARCHAR2(7) Y The default buffer pool to be used for index blocks --索引块的默认缓冲池
FLASH_CACHE VARCHAR2(7) Y The default flash cache hint to be used for index blocks --索引块的默认flash cache
CELL_FLASH_CACHE VARCHAR2(7) Y The default cell flash cache hint to be used for index blocks --索引块的默认cell flash cache，用于exetrada
USER_STATS VARCHAR2(3) Y Were the statistics entered directly by the user? --统计是否由用户直接输入
DURATION VARCHAR2(15) Y If index on temporary table, then duration is sys$session or sys$transaction else NULL --如索引基于临时表创建，则为sys$session或sys$transaction；否则为null
PCT_DIRECT_ACCESS NUMBER Y If index on IOT, then this is percentage of rows with Valid guess --如基于iot创建索引，此值为在有效猜想下的行数比例
ITYP_OWNER VARCHAR2(30) Y If domain index, then this is the indextype owner --如为域索引，则为indextype所有者
ITYP_NAME VARCHAR2(30) Y If domain index, then this is the name of the associated indextype --同上，则为indextype的名称
PARAMETERS VARCHAR2(1000) Y If domain index, then this is the parameter string --同上，参数字符串
GLOBAL_STATS VARCHAR2(3) Y Are the statistics calculated without merging underlying partitions? --计算出来的统计信息未合并基本的分区
DOMIDX_STATUS VARCHAR2(12) Y Is the indextype of the domain index valid --域索引的indextype是否有效
DOMIDX_OPSTATUS VARCHAR2(6) Y Status of the operation on the domain index --域索引的操作状态
FUNCIDX_STATUS VARCHAR2(8) Y Is the Function-based Index DISABLED or ENABLED? --基于函数的索引是否启用
JOIN_INDEX VARCHAR2(3) Y Is this index a join index? --是否为连接index
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3) Y Were redundant primary key columns eliminated from iot secondary index? --是否自iot二级索引中移除多余的主键列
DROPPED VARCHAR2(3) Y Whether index is dropped and is in Recycle Bin --是否删除索引或是否在回收池中
VISIBILITY VARCHAR2(9) Y Whether the index is VISIBLE or INVISIBLE to the optimizer --优化器是否可见索引
DOMIDX_MANAGEMENT VARCHAR2(14) Y If this a domain index, then whether it is system managed or user managed --如为域索引，索引由系统或用户管理标识
SEGMENT_CREATED VARCHAR2(3) Y Whether the index segment has been created --索引段是否已创建标识
SQL> create table t_index(a int,b int);
Table created
SQL> create index idx_t_index on t_index(a);
Index created
--未插入数据之前的索引
SQL> select * from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ----------- ---------------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- ------- ---------- ----------------- ---------------
IDX_T_INDEX NORMAL SCOTT T_INDEX TABLE NONUNIQUE DISABLED TBS_HANG 2 255 10 YES 0 0 0 0 0 0 VALID 0 0 2013/3/10 14: 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO NO NO NO NO VISIBLE NO
SQL>
SQL> insert into t_index select mod(level,3),level from dual connect by level<=1000000;
1000000 rows inserted
SQL> commit;
Commit complete
--插入数据后的索引信息
SQL> select * from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ----------- ---------------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- ------- ---------- ----------------- ---------------
IDX_T_INDEX NORMAL SCOTT T_INDEX TABLE NONUNIQUE DISABLED TBS_HANG 2 255 65536 1048576 1 2147483645 10 YES 0 0 0 0 0 0 VALID 0 0 2013/3/10 14: 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO NO NO NO NO VISIBLE YES
--上述索引分析时间未变更，有些信息未实时反映，采集统计信息
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed
--索引相关的信息已变更正确
SQL> select * from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS JOIN_INDEX IOT_REDUNDANT_PKEY_ELIM DROPPED VISIBILITY DOMIDX_MANAGEMENT SEGMENT_CREATED
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---------- ----------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- ----------- ------------- ---------------------------------------- ---------------------------------------- ----------- --------- --------- --------- ----------- ----------- ---------------- ---------- --------------- ----------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ------------- --------------- -------------- ---------- ----------------------- ------- ---------- ----------------- ---------------
IDX_T_INDEX NORMAL SCOTT T_INDEX TABLE NONUNIQUE DISABLED TBS_HANG 2 255 65536 1048576 1 2147483645 10 YES 2 2931 3 977 1744 5232 VALID 1000000 1000000 2013/3/10 14: 1 1 NO N N N DEFAULT DEFAULT DEFAULT NO YES NO NO NO VISIBLE YES
--总记录数为1000000,而不同键值数为3,上述user_indexes也为DISTINCT_KEYS，即不同的列值数
SQL> select count(a),count(distinct a) from t_index;
COUNT(A) COUNT(DISTINCTA)
---------- ----------------
1000000 3
--表占用的数据块个数,此值与上述user_indexes的AVG_DATA_BLOCKS_PER_KEY对应，可理解每个键值共占用多少个数据块（即每个不同的列值）
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t_index;
COUNT(DISTINCTDBMS_ROWID.ROWID
------------------------------
1744
--我分析 AVG_LEAF_BLOCKS_PER_KEY对应每个键值占用的索引块的个数
SQL> select segment_name,blocks,segment_type,bytes/1024/1024 mb from user_segments where segment_name in('T_INDEX','IDX_T_INDEX');
SEGMENT_NAME BLOCKS SEGMENT_TYPE MB
-------------------------------------------------------------------------------- ---------- ------------------ ----------
IDX_T_INDEX 3072 INDEX 24
T_INDEX 1792 TABLE 14
--说明有1个根块和140个分支块
SQL> select 3072-2931 from dual;
3072-2931
----------
141
--总共有2层高索引
SQL> select blevel from user_indexes where index_name='IDX_T_INDEX';
BLEVEL
----------
2
--如下计算即每个键值占用的索引块为977,刚好等于叶块个数2931
SQL> select distinct a from t_index;
A
---------------------------------------
1
2
0
SQL> select LEAF_BLOCKS,AVG_LEAF_BLOCKS_PER_KEY from user_indexes where index_name='IDX_T_INDEX';
LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
----------- -----------------------
2931 977
SQL> select 977*3 from dual;
977*3
----------
2931
--我分析索引比表大，与二者存储参数分配有关，即索引的pctfree大于表的pctfree
--二者的存储参数分配是一样的，看来与此无关；自网查如表列很少，因为索引会固定存储索引列值与6 byte的rowid，故索引大小大于表大小；
SQL> select index_name,PCT_FREE,SAMPLE_SIZE from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME PCT_FREE SAMPLE_SIZE
------------------------------ ---------- -----------
IDX_T_INDEX 10 1000000
SQL> select table_name,PCT_FREE,SAMPLE_SIZE from user_tables where table_name='T_INDEX';
TABLE_NAME PCT_FREE SAMPLE_SIZE
------------------------------ ---------- -----------
T_INDEX 10 1000000
--我们看看表有很多列，表是否大于索引；
SQL> drop table t_index purge;
Table dropped
--创建一个3列的表
SQL> create table t_index(a int,b int,c varchar2(100));
Table created
SQL> insert into t_index select mod(level,3),level,to_char(level) from dual connect by level<=1000000;
1000000 rows inserted
SQL> commit;
Commit complete
PL/SQL procedure successfully completed
SEGMENT_NAME BLOCKS SEGMENT_TYPE MB
-------------------------------------------------------------------------------- ---------- ------------------ ----------
T_INDEX 2816 TABLE 22
SQL> create index idx_t_index on t_index(a);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed
--这下表大于索引了
SQL> select segment_name,blocks,segment_type,bytes/1024/1024 mb from user_segments where segment_name in('T_INDEX','IDX_T_INDEX');
SEGMENT_NAME BLOCKS SEGMENT_TYPE MB
-------------------------------------------------------------------------------- ---------- ------------------ ----------
IDX_T_INDEX 2048 INDEX 16
T_INDEX 2816 TABLE 22
SQL>
--如下也说明为何在表列少的情况下为何索引大于表；因为a列仅占用1个字节，而rowid占用10byte;索引的强构是索引列加上rowid和索引其它的结构；
SQL> select a,dump(a),dump(rowid) from t_index where rownum<=3;
A DUMP(A) DUMP(ROWID)
--------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2 Typ=2 Len=2: 193,3 Typ=69 Len=10: 0,1,13,47,2,131,135,187,0,0
0 Typ=2 Len=1: 128 Typ=69 Len=10: 0,1,13,47,2,131,135,187,0,1
1 Typ=2 Len=2: 193,2 Typ=69 Len=10: 0,1,13,47,2,131,135,187,0,2
--如下为是否函数索引
SQL> create index idx_t_index on t_index(upper(a));
Index created
SQL> select FUNCIDX_STATUS from user_indexes where index_name='IDX_T_INDEX';
FUNCIDX_STATUS
--------------
ENABLED
--测试下列值重复高构建索引
SQL> create index idx_t_index on t_index(a);
Index created
SQL> select distinct a from t_index;
A
---------------------------------------
1
2
0
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed
SQL> select segment_name,blocks,segment_type,bytes/1024/1024 mb from user_segments where segment_name in('T_INDEX','IDX_T_INDEX');
SEGMENT_NAME BLOCKS SEGMENT_TYPE MB
-------------------------------------------------------------------------------- ---------- ------------------ ----------
IDX_T_INDEX 2048 INDEX 16
T_INDEX 2816 TABLE 22
--执行计划花费的成本，成本为343，而逻辑读为1920
SQL> explain plan for select count(a) from t_index where a=0;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3008215149
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 343 (5)| 00:00
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | INDEX FAST FULL SCAN| IDX_T_INDEX | 333K| 976K| 343 (5)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=0)
14 rows selected
SQL> set autot trace exp stat
--
SQL> select count(a) from t_index where a=0;
Execution Plan
----------------------------------------------------------
Plan hash value: 3008215149
------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
|
------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | 3 | 343 (5)| 00:
:05 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|
|* 2 | INDEX FAST FULL SCAN| IDX_T_INDEX | 333K| 976K| 343 (5)| 00:
:05 |
------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=0)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1920 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
---看看压缩索引后的情况
SQL> drop index idx_t_index;
Index dropped.
SQL> create index idx_t_index on t_index(a) compress;
Index created.
--索引由16变成了13，空间占用变小了
SQL> select segment_name,blocks,segment_type,bytes/1024/1024 mb from user_segmen
ts where segment_name in('T_INDEX','IDX_T_INDEX');
SEGMENT_NAME
--------------------------------------------------------------------------------
BLOCKS SEGMENT_TYPE MB
---------- ------------------------------------ ----------
IDX_T_INDEX
1664 INDEX 13
T_INDEX
2816 TABLE 22
--cpu成本由原343变为280,而逻辑读由原1920变为1549；有了明显的提升；
--所以在查询方面采用压缩还是很不错的；
SQL> select count(a) from t_index where a=0;
Execution Plan
----------------------------------------------------------
Plan hash value: 3008215149
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | 3 | 280 (6)| 00:00
:04 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|
|* 2 | INDEX FAST FULL SCAN| IDX_T_INDEX | 334K| 978K| 280 (6)| 00:00
:04 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1549 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--未压缩索引插入用时15秒左右
SQL> create table t_index(a int,b int,c varchar2(100));
Table created.
SQL> create index idx_t_index on t_index(a);
Index created.
SQL> set timing on time on
15:50:35 SQL> insert into t_index select mod(level,3),level,to_char(level) from
dual connect by level<=1000000;
1000000 rows created.
Elapsed: 00:00:15.55
---压缩比非压缩insert快一些，降到13秒左右
15:55:34 SQL> create table t_index(a int,b int,c varchar2(100));
Table created.
Elapsed: 00:00:00.31
15:55:42 SQL> create index idx_t_index on t_index(a) compress;
Index created.
Elapsed: 00:00:00.07
15:55:58 SQL> insert into t_index select mod(level,3),level,to_char(level) from
dual connect by level<=1000000;
1000000 rows created.
Elapsed: 00:00:13.46
--为了公平用2000000数据再测试对比下，未压缩用时31秒左右
15:59:51 SQL> create table t_index(a int,b int,c varchar2(100));
Table created.
Elapsed: 00:00:00.34
15:59:57 SQL> create index idx_t_index on t_index(a);
Index created.
Elapsed: 00:00:00.09
16:00:06 SQL> insert into t_index select mod(level,3),level,to_char(level) from
dual connect by level<=2000000;
2000000 rows created.
Elapsed: 00:00:31.70
16:00:49 SQL>
16:01:08 SQL> rollback;
Rollback complete.
Elapsed: 00:00:31.57
16:01:47 SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:01:05.80
--数据量一大，就显现出来了，压缩用时由未压缩的31秒升为46秒；所以在使用压缩时，一定要分析业务情景
--可见压缩索引比未压缩索引插入花时更多
16:03:38 SQL> drop table t_index purge;
Table dropped.
Elapsed: 00:00:02.10
16:04:51 SQL> create table t_index(a int,b int,c varchar2(100));
Table created.
Elapsed: 00:00:00.17
16:04:58 SQL> create index idx_t_index on t_index(a) compress;
Index created.
Elapsed: 00:00:00.06
16:05:12 SQL> insert into t_index select mod(level,3),level,to_char(level) from
dual connect by level<=2000000;
2000000 rows created.
Elapsed: 00:00:46.61
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-755693/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
