[20131013]索引部分資料.txt
[20131013]索引部分資料.txt
在實際的生產系統中，比如一個標識狀態的欄位，'0'的行很少，'1'以及其他值的行很多，一個透過特殊的函式索引，
利用oracle索引不儲存NULL的特性（注意如果索引2個欄位，2個必須為NULL，索引才不會儲存），索引僅僅為'0'，
減少索引的大小，實際上網上有許多的例子，正好別人問到，順手寫一個小例子:
1.建立測試例子:
create table t as select rownum id,lpad('x',80,'x') pad, '1' flag from dual connect by level <=1e4 - 3;
insert into t values (9998,lpad('y',80,'y'),'0');
insert into t values (9999,lpad('y',80,'y'),'0');
insert into t values (1000,lpad('y',80,'y'),'0');
commit ;
2.建立函式索引:
create index if_t_flag on t (decode(flag,'0',flag));
validate index if_t_flag;
cat i.sql
set linesize 200;
set linesize 200;
column name format a10
/* select height, blocks, lf_blks, lf_rows_len, lf_blk_len, br_blks, br_rows, br_rows_len, br_blk_len, btree_space, used_space, pct_used from index_stats; */
select HEIGHT, BLOCKS, NAME, LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN, BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN,
DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS from index_stats;
select MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE, PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS, PRE_ROWS, PRE_ROWS_LEN,
OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;
--查詢index_stats內容太多，分2次查詢
--可以發現LF_ROWS＝3，也就是僅僅索引3個值。
3.測試：
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T', method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
--可以發現並沒有使用我們建立的函式索引。當然這些寫sql沒有問題。
4.看看加入hint如何?
5.在謂詞是flag='0'，不會使用函式索引。在測試使用case函式。
create index if1_t_flag on t( case flag when '0' then flag end);
SCOTT@test01p> validate index if1_t_flag ;
Index analyzed.
SCOTT@test01p> @i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
1 8 IF1_T_FLAG 3 1 39 8000 0 0 0 0 0 0 1
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
3 8000 39 1 3 3 0 0 0 0
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T', method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
--依舊沒使用函式索引。
6.加提示看看:
--依舊不行。
7.加入約束後：
SCOTT@test01p> alter table t modify flag not null;
Table altered.
--結果也一樣，不再另行測試。
也就是講要使用這種方式，需要開發與DBA的配合來完成。
8.在11G後，可以建立分割槽索引解決這個問題：
create index IF2_T_FLAG on T(flag) global partition by range (flag) (
partition p_flag0 values less than ('1'),
partition p_OTHER values less than (MAXVALUE)
) unusable;
--注意我使用unusable引數，沒有空間的使用。
9.12c下方法更多，看的blog有許多介紹。
這裡不再重複了。
在實際的生產系統中，比如一個標識狀態的欄位，'0'的行很少，'1'以及其他值的行很多，一個透過特殊的函式索引，
利用oracle索引不儲存NULL的特性（注意如果索引2個欄位，2個必須為NULL，索引才不會儲存），索引僅僅為'0'，
減少索引的大小，實際上網上有許多的例子，正好別人問到，順手寫一個小例子:
1.建立測試例子:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t as select rownum id,lpad('x',80,'x') pad, '1' flag from dual connect by level <=1e4 - 3;
insert into t values (9998,lpad('y',80,'y'),'0');
insert into t values (9999,lpad('y',80,'y'),'0');
insert into t values (1000,lpad('y',80,'y'),'0');
commit ;
2.建立函式索引:
create index if_t_flag on t (decode(flag,'0',flag));
validate index if_t_flag;
cat i.sql
set linesize 200;
set linesize 200;
column name format a10
/* select height, blocks, lf_blks, lf_rows_len, lf_blk_len, br_blks, br_rows, br_rows_len, br_blk_len, btree_space, used_space, pct_used from index_stats; */
select HEIGHT, BLOCKS, NAME, LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN, BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN,
DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS from index_stats;
select MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE, PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS, PRE_ROWS, PRE_ROWS_LEN,
OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;
--查詢index_stats內容太多，分2次查詢
SCOTT@test01p> @i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
1 8 IF_T_FLAG 3 1 39 8000 0 0 0 0 0 0 1
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
3 8000 39 1 3 3 0 0 0 0
--可以發現LF_ROWS＝3，也就是僅僅索引3個值。
3.測試：
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T', method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2puf1jju69u3q, child number 0
-------------------------------------
select * from t where flag='0'
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='0')
--可以發現並沒有使用我們建立的函式索引。當然這些寫sql沒有問題。
SCOTT@test01p> select * from t where decode(flag,'0',flag)='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fh0kpz3trj8m7, child number 0
-------------------------------------
select * from t where decode(flag,'0',flag)='0'
Plan hash value: 3731093196
-------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 3 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IF_T_FLAG | 3 | 1 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."SYS_NC00004$"='0')
4.看看加入hint如何?
SCOTT@test01p> select /*+ index(t if_t_flag) */ * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gzdxqc9dgz0vv, child number 0
-------------------------------------
select /*+ index(t if_t_flag) */ * from t where flag='0'
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='0')
5.在謂詞是flag='0'，不會使用函式索引。在測試使用case函式。
create index if1_t_flag on t( case flag when '0' then flag end);
SCOTT@test01p> validate index if1_t_flag ;
Index analyzed.
SCOTT@test01p> @i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
1 8 IF1_T_FLAG 3 1 39 8000 0 0 0 0 0 0 1
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
3 8000 39 1 3 3 0 0 0 0
SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T', method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2puf1jju69u3q, child number 0
-------------------------------------
select * from t where flag='0'
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"='0')
--依舊沒使用函式索引。
6.加提示看看:
SCOTT@test01p> select /*+ index(t if1_t_flag) */ count(*) from t where flag='0';
COUNT(*)
----------
3
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6hu6t6w2f8gdg, child number 0
-------------------------------------
select /*+ index(t if1_t_flag) */ count(*) from t where flag='0'
Plan hash value: 2966233522
---------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| T | 5000 | 53 (0)|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"='0')
--依舊不行。
7.加入約束後：
SCOTT@test01p> alter table t modify flag not null;
Table altered.
--結果也一樣，不再另行測試。
也就是講要使用這種方式，需要開發與DBA的配合來完成。
8.在11G後，可以建立分割槽索引解決這個問題：
create index IF2_T_FLAG on T(flag) global partition by range (flag) (
partition p_flag0 values less than ('1'),
partition p_OTHER values less than (MAXVALUE)
) unusable;
--注意我使用unusable引數，沒有空間的使用。
SCOTT@test01p> select table_name ,index_name,segment_created from user_indexes where table_name='T';
TABLE_NAME INDEX_NAME SEG
---------- -------------------- ---
T IF2_T_FLAG N/A
T IF1_T_FLAG YES
T IF_T_FLAG YES
SCOTT@test01p> alter index if2_t_flag rebuild partition p_flag0;
Index altered.
SCOTT@test01p> select segment_name ,PARTITION_NAME,segment_type ,bytes from user_segments where segment_name='IF2_T_FLAG' ;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES
-------------------- -------------------- ------------------ ----------
IF2_T_FLAG P_FLAG0 INDEX PARTITION 65536
SCOTT@test01p> select * from t where flag='0';
ID PAD F
---------- -------------------------------------------------------------------------------- -
9998 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
9999 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
1000 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy 0
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gjpvzujvambrj, child number 0
-------------------------------------
select * from t where flag='0'
Plan hash value: 2463861812
---------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | PARTITION RANGE SINGLE | | 3 | 1 (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 3 | 1 (0)|
|* 3 | INDEX RANGE SCAN | IF2_T_FLAG | 3 | 1 (0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"='0')
9.12c下方法更多，看的blog有許多介紹。
這裡不再重複了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-774304/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫的部分索引資料庫索引
- 僅對部分資料構建索引索引
- Oracle 對某列的部分資料建立索引Oracle索引
- [20131013]rman 在12c的預設設定.txt
- [20170623]利用傳輸表空間恢復部分資料.txt
- 【Mysql】資料庫索引，百萬資料測試索引效果MySql資料庫索引
- 資料庫索引資料庫索引
- solr總結 第五部分：solr為資料庫建立索引Solr資料庫索引
- 【Oracle】-【索引】先查資料再建索引，還是先建索引再插資料？Oracle索引
- Python連線mongodb提取部分欄位內資料並寫入txt檔案PythonMongoDB
- 【資料庫】mysql資料庫索引資料庫MySql索引
- indexedDB 資料庫 索引Index資料庫索引
- 資料庫索引原理資料庫索引
- 資料庫索引《二》資料庫索引
- 資料庫索引《一》資料庫索引
- 索引資料結構索引資料結構
- Postgresql匯出部分資料SQL
- mysql資料庫的索引MySql資料庫索引
- MySQL資料庫之索引MySql資料庫索引
- 資料庫之建立索引資料庫索引
- 概覽資料庫索引資料庫索引
- 資料庫索引層級資料庫索引
- chinaunix-索引資料索引
- 資料庫補丁索引資料庫索引
- 資料庫索引原理-轉資料庫索引
- SQL Server 資料庫索引SQLServer資料庫索引
- [轉]Mysql資料庫相關資料索引MySql資料庫索引
- 分割槽表、分割槽索引和全域性索引部分總結索引
- flask框架資料庫部分(四)Flask框架資料庫
- JAVA面試二（資料庫部分）Java面試資料庫
- struts的資料封裝部分封裝
- 資料庫表,索引(索引組織表,聚簇表,聚簇索引,)資料庫索引
- 資料庫索引背後的資料結構資料庫索引資料結構
- oracle資料庫使用sqlldr命令匯入txt資料Oracle資料庫SQL
- mongodb資料庫如何建立索引？MongoDB資料庫索引
- pandas索引和選擇資料索引
- 資料庫——對索引的理解資料庫索引
- MySQL資料庫索引簡介MySql資料庫索引