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部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-755693/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 點陣圖索引（Bitmap Index）——點陣圖索引與資料DML鎖定索引Index
- oracle index索引結構（一）OracleIndex索引
- oracle全文索引之commit與DML操作Oracle索引MIT
- oracle index索引原理OracleIndex索引
- 唯一性索引（Unique Index）與普通索引（Normal Index）差異（上）索引IndexORM
- 唯一性索引（Unique Index）與普通索引（Normal Index）差異（中）索引IndexORM
- 唯一性索引（Unique Index）與普通索引（Normal Index）差異（下）索引IndexORM
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index
- 【INDEX】Oracle中主鍵、唯一約束與唯一索引之區別IndexOracle索引
- Oracle索引分裂（Index Block Split）Oracle索引IndexBloC
- Oracle筆記 之 索引(index)Oracle筆記索引Index
- 清理Oracle中無用索引改善DML效能Oracle索引
- 清除Oracle中無用索引 改善DML效能Oracle索引
- 清除Oracle中無用索引,改善DML效能Oracle索引
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- oracle 點陣圖索引（bitmap index）Oracle索引Index
- oracle index索引相關筆記OracleIndex索引筆記
- oracle全文索引之STOPLIST_ CTXCAT 索引_INDEX SETOracle索引Index
- index索引Index索引
- 【INDEX】注意：不可見索引在表DML操作過程中依然被維護Index索引
- 【INDEX】Oracle 索引常見知識梳理IndexOracle索引
- Oracle Index Key Compression索引壓縮OracleIndex索引
- oracle點陣圖索引對DML操作的影響Oracle索引
- Oracle表table與索引index的分析及索引重建及統計資訊匯入匯出Oracle索引Index
- Oracle中的虛擬列索引-nosegment indexOracle索引Index
- 【INDEX】Oracle分割槽索引技術詳解IndexOracle索引
- Oracle index索引塊分裂split資訊彙總OracleIndex索引
- 介紹Oracle Virtual Index虛擬索引（上）OracleIndex索引
- 分割槽索引之本地(local index)索引和全域性索引(global index)索引Index
- 不可見索引(invisible index)在表DML操作過程中依然被維護索引Index
- bitmap index點陣圖索引系列(一)Index索引
- unusable index對DML/QUERY的影響Index
- 主鍵(PK)與非空唯一索引(Unique Index)的區別索引Index
- 原創：oracle DML介紹與使用Oracle
- oracle分割槽及分割槽索引partition_partition index_維護(一)Oracle索引Index
- Mysql——index(索引)使用MySqlIndex索引
- mysql 索引( mysql index )MySql索引Index
- 【INDEX】使用“alter index ××× monitoring usage;”語句監控索引使用與否Index索引