oracle 索引的建立與管理
索引的相關建立與使用
建立普通索引
|
SQL> select * from temp;
ID NAME ---------- -------------------- 1456 sony 1379 sony 1568 lenovo 1788 lenovo
SQL> desc user_ind_columns Name Null? Type ----------------------------------------- -------- ---------------------------- INDEX_NAME VARCHAR2(30) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) COLUMN_POSITION NUMBER COLUMN_LENGTH NUMBER CHAR_LENGTH NUMBER DESCEND VARCHAR2(4) 查詢表是否有索引 SQL> select index_name,column_name from user_ind_columns where table_name='TEMP';
no rows selected 建立索引 SQL> create index temp_id_i on temp(id);
Index created.
開啟追蹤 SQL> set autot trace exp SQL> select * from temp where id=1379;
Execution Plan ---------------------------------------------------------- Plan hash value: 3945629271
-------------------------------------------------------------------------------- ---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime |
-------------------------------------------------------------------------------- ---------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 0 0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 25 | 2 (0)| 0 0:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_ID_I | 1 | | 1 (0)| 0 0:00:01 |
-------------------------------------------------------------------------------- ---------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("ID"=1379)
Note ----- - dynamic sampling used for this statement
SQL> 刪除索引在查詢 SQL> drop index temp_id_i;
Index dropped.
SQL> select * from temp where id=1379;
Execution Plan ---------------------------------------------------------- Plan hash value: 1896031711
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEMP | 1 | 25 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("ID"=1379)
Note ----- - dynamic sampling used for this statement
SQL>
|
建立排序索引
|
SQL> select * from temp;
ID NAME SAL ---------- -------------------- ---------- 1456 sony 1254 1379 sony 1106 1568 lenovo 1018 1788 lenovo 1758
SQL> 想要查詢的語句 SQL> select * from temp where sal>1000 order by sal asc;
ID NAME SAL ---------- -------------------- ---------- 1568 lenovo 1018 1379 sony 1106 1456 sony 1254 1788 lenovo 1758
SQL> 建立升序索引 QL> create index temp_sal_i on temp(sal asc);
Index created.
SQL> 檢視執行計劃 SQL> set autot trace exp SQL> select * from temp where sal>1100 order by sal asc;
Execution Plan ---------------------------------------------------------- Plan hash value: 1493261289
-------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ----------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_SAL_I | 3 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- ----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("SAL">1100) filter("SAL">1100)
SQL> 如果變為降序 SQL> select * from temp where sal>1100 order by sal desc;
Execution Plan ---------------------------------------------------------- Plan hash value: 3579856106
-------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| TEMP_SAL_I | 3 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- -----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("SAL">1100) filter("SAL">1100)
SQL> 使索引不對錶進行排序可以建立降序索引 SQL> drop index temp_sal_i;
Index dropped.
SQL> create index temp_sal_i on temp(sal desc);
Index created.
SQL> select * from temp where sal>1100 order by sal desc;
Execution Plan ---------------------------------------------------------- Plan hash value: 1493261289
-------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ----------
| 0 | SELECT STATEMENT | | 3 | 114 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 3 | 114 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_SAL_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- ----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access(SYS_OP_DESCEND("SAL") filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SAL"))>1100 AND
SYS_OP_DESCEND("SAL")
Note ----- - dynamic sampling used for this statement
SQL>
|
建立不排序索引
建立不排序索引的前提是在要建索引的列必須是已經升序排序過的了
|
SQL> create table temp1 as select * from temp;
Table created. 截斷表 SQL> truncate table temp1;
Table truncated. 插入資料並排序 SQL> insert into temp1 select * from temp order by id; 4 rows created.
SQL> SQL> select * from temp1;
ID NAME SAL ---------- -------------------- ---------- 1379 sony 1106 1456 sony 1254 1568 lenovo 1018 1788 lenovo 1758
SQL> 建立不排序索引 SQL> create index temp1_id_i on temp1(id) nosort;
Index created.
SQL>
|
建立組合索引
注意事項：
要把去掉重複行之後個數少的列放前面 （如果是空值，就把空值最多的放在前面）
例：
|
SQL> select count(distinct(name)) from temp;
COUNT(DISTINCT(NAME)) --------------------- 2
SQL> select count(distinct(sal)) from temp;
COUNT(DISTINCT(SAL)) -------------------- 4
SQL> 所以 name列應放在sal列的前面 |
|
QL> select * from temp where name='sony' and sal=1106;
ID NAME SAL ---------- -------------------- ---------- 1379 sony 1106
SQL> create index temp_name_sal_i on temp(name,sal);
Index created.
SQL> set autot trace exp SQL> select * from temp where name='sony' and sal=1106;
Execution Plan ---------------------------------------------------------- Plan hash value: 1649455405
-------------------------------------------------------------------------------- ---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time |
-------------------------------------------------------------------------------- ---------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_NAME_SAL_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- ---------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("NAME"='sony' AND "SAL"=1106) filter(SYS_OP_DESCEND("SAL")=HEXTORAW('3DF3F8FF') )
SQL> 如果索引分開建在查詢時只能用到一個索引 SQL> drop index temp_name_sal_i;
Index dropped.
SQL> SQL> create index temp_name_i on temp(name);
Index created.
SQL> SQL> create index temp_sal_i on temp(sal);
Index created.
SQL> SQL> select * from temp where name='sony' and sal=1106;
Execution Plan ---------------------------------------------------------- Plan hash value: 1493261289
-------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ----------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_SAL_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- ----------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("NAME"='sony') 2 - access("SAL"=1106) |
建立無效索引
|
SQL> create index temp_name_i on temp(name) unusable;
Index created.
SQL> SQL> select index_name,status from user_indexes;
INDEX_NAME STATUS ------------------------------ -------- TEMP_NAME_I UNUSABLE TEMP1_ID_I VALID SYS_C005242 VALID
SQL> SQL> set autot trace exp SQL> select * from temp where name='sony';
Execution Plan ---------------------------------------------------------- Plan hash value: 1896031711
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 76 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEMP | 2 | 76 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("NAME"='sony')
SQL>
重建索引可以使其有效 SQL> alter index temp_name_i rebuild;
Index altered.
SQL> select * from temp where name='sony';
Execution Plan ---------------------------------------------------------- Plan hash value: 1257893614
-------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 2 | 76 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 2 | 76 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_NAME_I | 2 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- -----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("NAME"='sony')
SQL> |
建立反鍵索引
|
普通索引 SQL> create index temp_name on temp(name);
Index created.
SQL> 反鍵索引 SQL> create index temp_name_i on temp( reverse(name));
Index created.
SQL> 查詢以a開頭的走的是普通索引 SQL> select * from temp where name like 'A%';
Execution Plan ---------------------------------------------------------- Plan hash value: 953236590
-------------------------------------------------------------------------------- ---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime |
-------------------------------------------------------------------------------- ---------
| 0 | SELECT STATEMENT | | 1 | 38 | 0 (0)| 0 0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 38 | 0 (0)| 0 0:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_NAME | 1 | | 0 (0)| 0 0:00:01 |
查詢以什麼結尾的會走索引全表掃描 SQL> select * from temp where name like '%Y';
Execution Plan ---------------------------------------------------------- Plan hash value: 3226310466
-------------------------------------------------------------------------------- ---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime |
-------------------------------------------------------------------------------- ---------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 0 0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 38 | 2 (0)| 0 0:00:01 |
|* 2 | INDEX FULL SCAN | TEMP_NAME | 1 | | 1 (0)| 0 0:00:01 |
-------------------------------------------------------------------------------- ---------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%Y')
SQL>
如果不想索引全表掃描那麼可以使用反鍵 SQL> select * from temp where reverse(name) like 'Y%';
Execution Plan ---------------------------------------------------------- Plan hash value: 1257893614
-------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_NAME_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- -----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access(REVERSE("NAME") LIKE 'Y%') filter(REVERSE("NAME") LIKE 'Y%')
SQL> |
對於熱點塊就可以使用反鍵索引（將儲存的值分散到不同的塊中。）
|
將普通索引重建為反鍵索引 SQL>alter index temp1_sal_i rebuild reverse;
如果以前列中的值為 0001,0002,0003,0004… 這種連續的 那麼使用反鍵索引就會將值變為1000,2000,3000,4000這種不連續的達到儲存到不同塊中的目的。 |
建立文字索引
|
SQL> conn / as sysdba
SQL> create table u1.test1 as select * from tab;
SQL> conn u1/u1 建立普通索引 SQL>create index test1_tname_i on test1(tname); SQL>set autot trace exp 查詢在tname列包含SEG的所有值 發現是不走索引的 SQL> select tname from test1 where tname like '%SEG%';
Execution Plan ---------------------------------------------------------- Plan hash value: 1751340739
-------------------------------------------------------------------------------- ------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ------
| 0 | SELECT STATEMENT | | 25 | 425 | 6 (0)| 00:0 0:01 |
|* 1 | INDEX FAST FULL SCAN| TEST1_TNAME_I | 25 | 425 | 6 (0)| 00:0 0:01 |
建立文字索引 SQL> drop index test1_tname_i;
SQL> create index test1_tname_i on test1(tname) indextype is ctxsys.context;
Index created.
SQL> SQL> select tname from test1 where contains(tname, '%SEG%')>0;
Execution Plan ---------------------------------------------------------- Plan hash value: 2080224863
-------------------------------------------------------------------------------- -------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU )| Time |
-------------------------------------------------------------------------------- -------------
| 0 | SELECT STATEMENT | | 2 | 58 | 4 (0 )| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 58 | 4 (0 )| 00:00:01 |
|* 2 | DOMAIN INDEX | TEST1_TNAME_I | | | 4 (0 )| 00:00:01 |
-------------------------------------------------------------------------------- -------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("TNAME",'%SEG%')>0)
SQL> |
建立函式索引
要使用函式索引必須保證sysdba使用者以下值是正確的
|
SQL> conn / as sysdba Connected. SQL> show parameter que
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string fengzi job_queue_processes integer 10 query_rewrite_enabled string TRUE （query_rewrite_enabled 引數為true， query_rewrite_integrity string enforced query_rewrite_integrity數為enforced或者為truseted 函式索引就有效） SQL>
|
|
SQL> select * from temp where sal+nvl(comm,0)>4000;
ID NAME SAL COMM ---------- -------------------- ---------- ---------- 1568 lenovo 1018 3000 1788 lenovo 1758 3100
SQL>
SQL> create index temp_sal_nvl on temp (sal+nvl(comm,0));
Index created.
SQL> SQL> set autot trace exp SQL> select * from temp where sal+nvl(comm,0)>4000;
Execution Plan ---------------------------------------------------------- Plan hash value: 2060561913
-------------------------------------------------------------------------------- ------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------- ------------
| 0 | SELECT STATEMENT | | 2 | 102 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 2 | 102 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_SAL_NVL | 1 | | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------- ------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("SAL"+NVL("COMM",0)>4000)
SQL>
如果資料庫採用了加密技術如何實現以下語句走索引（採用加密技術的資料庫不等式是不走索引的） SQL> select * from temp where sal<2000; SQL> select * from temp where sal between 2000 and 3000 ;
SQL> select * from temp where sal>3000; 可以自己定義函式建立函式索引 SQL> create or replace function gsal(vsal number) 2 return number deterministic 3 as 4 begin 5 if vsal<2000 then return 1; 6 elsif vsal between 2000 and 3000 then return 2; 7 else return 3; 8 end if; 9 end gsal; 10 / Function created.
SQL> 驗證函式是否有效 SQL> select gsal(500) from dual;
GSAL(500) ---------- 1
SQL> select gsal(2500) from dual;
GSAL(2500) ---------- 2
SQL> select gsal(3200) from dual;
GSAL(3200) ---------- 3
SQL> 建立函式索引 SQL> create index temp_sal_f on temp(gsal(sal));
Index created.
SQL> 檢視執行計劃 SQL> set autot trace exp SQL> select * from temp where gsal(sal)=1;
Execution Plan ---------------------------------------------------------- Plan hash value: 1671644050
-------------------------------------------------------------------------------- ----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ----------
| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 51 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_SAL_F | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- ----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("U1"."GSAL"("SAL")=1) |
建立空值索引
|
建立普通索引 SQL> create index temp_comm_i on temp(comm); Index created. SQL> SQL> set autot trace exp SQL> select * from temp where comm is null;
Execution Plan ---------------------------------------------------------- Plan hash value: 1896031711
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 204 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEMP | 4 | 204 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
建立空值索引 SQL> drop index temp_comm_i;
Index dropped.
SQL> SQL> create index temp_comm_i on temp(sys_op_map_nonnull(comm));
Index created.
SQL> select * from temp where sys_op_map_nonnull(comm)=sys_op_map_nonnull(null);
Execution Plan ---------------------------------------------------------- Plan hash value: 285497915
-------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 1 | 51 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 1 | 51 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_COMM_I | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
還可以建立組合索引，將有空值的列放在組合索引的前端。 |
建立隱藏索引
在11g以前的版本是不能建立的。
|
SQL> conn scott/tiger Connected. SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE
SQL> create index emp_ename_i on emp(ename) invisible;
Index created.
SQL> 檢視索引狀態 SQL> select index_name,visibility from user_indexes;
INDEX_NAME VISIBILIT ------------------------------ --------- PK_EMP VISIBLE EMP_ENAME_I INVISIBLE PK_DEPT VISIBLE
SQL> 查詢一下執行計劃看是否走索引 SQL> set autot trace exp SQL> select * from emp where ename='KING';
Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("ENAME"='KING')
實現隱藏索引正常使用需要sysdba使用者修改optimizer_use_invisible_indexes引數為true SQL> conn / as sysdba Connected. SQL> show parameter optim
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ object_cache_optimal_size integer 102400 optimizer_capture_sql_plan_baselines boolean FALSE optimizer_dynamic_sampling integer 2 optimizer_features_enable string 11.2.0.2 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE optimizer_use_invisible_indexes boolean FALSE optimizer_use_pending_statistics boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ plsql_optimize_level integer 2 SQL> SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> set autot trace exp SQL> select * from scott.emp where ename='KING';
Execution Plan ---------------------------------------------------------- Plan hash value: 549418132
-------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_I | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- -----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("ENAME"='KING')
SQL> 可以修改隱藏索引為正常索引 SQL> alter index scott.emp_ename_i visible;
Index altered.
SQL> 同樣也可以修改正常索引為隱藏索引 SQL> alter index scott.emp_ename_i invisible;
Index altered.
SQL>
|
建立點陣圖索引
|
建立普通索引 SQL> create index temp_name_i on temp(name);
Index created. 檢視執行計劃 SQL> set autot trace exp SQL> select * from temp where name='sonny';
Execution Plan ---------------------------------------------------------- Plan hash value: 1257893614
-------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 4 | 204 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 4 | 204 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEMP_NAME_I | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------- -----------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("NAME"='sonny')
建立點陣圖索引 SQL> drop index temp_name_i;
Index dropped.
SQL> create bitmap index temp_name_i on temp(name);
Index created.
SQL> select * from temp where name='sonny';
Execution Plan ---------------------------------------------------------- Plan hash value: 1986849256
-------------------------------------------------------------------------------- ------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------- ------------
| 0 | SELECT STATEMENT | | 4 | 204 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEMP | 4 | 204 | 1 (0) | 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | TEMP_NAME_I | | | | |
-------------------------------------------------------------------------------- ------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("NAME"='sonny')
Note ----- - dynamic sampling used for this statement
SQL>
如果發現不走索引可以強制讓語句走索引 SQL> select /*+ index(temp,temp_name_i) */ * from temp where name='sonny';
Execution Plan ---------------------------------------------------------- Plan hash value: 1986849256
-------------------------------------------------------------------------------- ------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------- ------------
| 0 | SELECT STATEMENT | | 4 | 204 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEMP | 4 | 204 | 1 (0) | 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | TEMP_NAME_I | | | | |
-------------------------------------------------------------------------------- ------------ |
建立點陣圖連線索引
|
SQL> set autot trace exp SQL> select count(*) from temp t,coty c where t.id=c.id and c.name='sonny';
Execution Plan ---------------------------------------------------------- Plan hash value: 570564326
-------------------------------------------------------------------------------- ---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ime |
-------------------------------------------------------------------------------- ---------
| 0 | SELECT STATEMENT | | 1 | 38 | 4 (0)| 0 0:00:01 |
| 1 | SORT AGGREGATE | | 1 | 38 | | |
| 2 | NESTED LOOPS | | 1 | 38 | 4 (0)| 0 0:00:01 |
| 3 | TABLE ACCESS FULL | TEMP | 8 | 104 | 3 (0)| 0 0:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| COTY | 1 | 25 | 1 (0)| 0 0:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_COTY | 1 | | 0 (0)| 0 0:00:01 |
-------------------------------------------------------------------------------- ---------
Predicate Information (identified by operation id): ---------------------------------------------------
4 - filter("C"."NAME"='sonny') 5 - access("T"."ID"="C"."ID")
Note ----- - dynamic sampling used for this statement
SQL>
建立點陣圖連線索引 SQL> create bitmap index temp_bit on temp(c.name) from temp t,coty c where t.id=c.id;
Index created.
SQL> 檢視索引狀態 SQL> select index_name,index_type from user_indexes;
INDEX_NAME INDEX_TYPE ------------------------------ --------------------------- TEST1_TNAME_I DOMAIN SYS_IOT_TOP_51560 IOT - TOP SYS_IL0000051557C00002$$ LOB SYS_IOT_TOP_51555 IOT - TOP SYS_IL0000051552C00006$$ LOB DR$TEST1_TNAME_I$X NORMAL TEMP_BIT BITMAP SYS_C005242 NORMAL PK_COTY NORMAL
9 rows selected.
SQL> 檢視執行計劃 SQL> set autot trace exp SQL> select count(*) from temp t,coty c where t.id=c.id and c.name='sonny';
Execution Plan ---------------------------------------------------------- Plan hash value: 2037991701
-------------------------------------------------------------------------------- --------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti me |
-------------------------------------------------------------------------------- --------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00 :00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | BITMAP CONVERSION COUNT | | 4 | 52 | 1 (0)| 00 （發現使用索引資源消耗大大減小） :00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| TEMP_BIT | | | | |
-------------------------------------------------------------------------------- -------- |
建立虛擬索引
虛擬索引沒有段 是不佔用儲存空間的
|
SQL> create index temp_name_i on temp(name) nosegment;
Index created.
SQL> 收集統計資訊
SQL> exec dbms_stats.gather_table_stats('U1','TEMP',cascade=>true);
PL/SQL procedure successfully completed.
SQL> 檢視統計資訊 SQL> set autot trace exp SQL> select name from temp where name='sonny';
Execution Plan ---------------------------------------------------------- Plan hash value: 1896031711
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 24 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEMP | 4 | 24 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("NAME"='sonny')
SQL> 需要使用隱藏引數才可以正常使用
SQL> alter session set "_use_nosegment_indexes"=true;
Session altered.
SQL> SQL> select * from temp where name='sonny';
Execution Plan ---------------------------------------------------------- Plan hash value: 1896031711
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| TEMP_NAME_I | 1| 6 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("NAME"='sonny')
SQL> 建隱藏引數設為false虛擬索引就會無效 SQL> alter session set "_use_nosegment_indexes"=false;
Session altered.
SQL> |
監控索引
|
首先要知道索引的名字 SQL> select index_name from user_indexes;
INDEX_NAME ------------------------------ TEST1_TNAME_I SYS_IOT_TOP_51560 SYS_IL0000051557C00002$$ SYS_IOT_TOP_51555 SYS_IL0000051552C00006$$ DR$TEST1_TNAME_I$X TEMP_NAME_I SYS_C005242 PK_COTY
9 rows selected.
開啟索引監控 SQL> alter index temp_name_i monitoring usage; Index altered. 執行跟索引有關查詢語句 SQL> select * from temp where name='sonny';
ID NAME SAL COMM ---------- -------------------- ---------- ---------- 1534 sonny 1542 1534 sonny 1542 1534 sonny 1542 1534 sonny 1542
查詢檢視 SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE ------------------------------ ------------------------------ --- --- START_MONITORING END_MONITORING ------------------- ------------------- TEMP_NAME_I TEMP YES YES 04/28/2014 14:33:57
關閉監控 SQL> alter index temp_name_i nomonitoring usage;
Index altered.
SQL>
|
索引重新命名
|
SQL> alter index temp_name_i rename to temp_name_index;
Index altered.
SQL>
|
對於分割槽表的索引
全域性索引—只有一個根是一個段
|
檢視分割槽表是根據那個列分的區 SQL> select name,column_name from user_part_key_columns;
NAME COLUMN_NAME --------------------------------------------------------- EMPL DEPTNO SQL>
全域性索引的建立 SQL> create index empl_deptno_i on empl(deptno);
SQL> 如果對這個分割槽進行修改，那麼所有的索引都將無效
可以建立一個本地索引這樣對分割槽修改時不會對索引有影響的
SQL> create index empl_deptno_ix on empl(deptno) local;
如果我新增一個分割槽 SQL> alter table empl add partition empl_p5 values(50); 只需要對新加分割槽重建索引 SQL> alter index empl_deptno_i rebuild partition empl_p5; |
查詢無效的物件
|
SQL> select object_name,object_type,status from dba_objects where owner='SCOTT' and status <> 'VALID';
no rows selected
SQL>
|
查詢索引無效的
|
SQL> select index_name,status from dba_indexes where owner='SCOTT' and status='UNUSABLE';
no rows selected
SQL>
|
分割槽表無效索引
|
SQL> select index_name,status from dba_ind_partitions where status='UNUSABLE';
no rows selected
SQL>
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29532781/viewspace-1174694/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle】-【建立索引】-建立索引的操作原理與一些體會Oracle索引
- ORACLE索引的管理Oracle索引
- oracle 索引 管理Oracle索引
- 【Mongo】MongoDB索引管理－索引的建立、檢視、刪除MongoDB索引
- Oracle 找出需要建立索引的表Oracle索引
- 【eygle】Oracle的分割槽表和Local索引建立與維護Oracle索引
- Mysql索引的建立與刪除MySql索引
- Oracle如何建立B樹索引Oracle索引
- Oracle大表快速建立索引Oracle索引
- oracle 繁忙時候建立索引Oracle索引
- oracle 建立所有分割槽索引Oracle索引
- Oracle表與索引的分析及索引重建Oracle索引
- oracle如何估算即將建立的索引大小Oracle索引
- 【oracle 】如何估算即將建立的索引大小Oracle索引
- Oracle表與索引的分析及索引重建(轉）Oracle索引
- Oracle 對某列的部分資料建立索引Oracle索引
- Oracle複合索引的建立和注意事項Oracle索引
- 【轉載】oracle全文索引的建立和使用Oracle索引
- elasticsearch(三)----索引建立與刪除Elasticsearch索引
- 關於 Oracle 分割槽索引的建立和維護Oracle索引
- oracle 序列的建立與使用Oracle
- 【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響Oracle索引
- Oracle如何管理帶約束的B樹索引Oracle索引
- oracle dml與索引index(一)Oracle索引Index
- 【資料庫使用-oracle索引的建立和分類】二資料庫Oracle索引
- 【資料庫使用-oracle索引的建立和分類】一資料庫Oracle索引
- Oracle建立二進位制檔案索引的方法(轉)Oracle索引
- Oracle如何預估將要建立的索引和表的大小Oracle索引
- mysql 建立索引的方法--建立檢視MySql索引
- Mysql多列索引建立與優化.mdMySql索引優化
- Oracle資料庫索引管理規範Oracle資料庫索引
- Oracle 41億資料量表建立索引記錄Oracle索引
- oracle資料庫建立、刪除索引等操作Oracle資料庫索引
- MySQL建立索引、修改索引、刪除索引的命令語句MySql索引
- Oracle 12c新特性之：使用高階索引壓縮建立索引Oracle索引
- ElasticSearch建立索引Elasticsearch索引
- DocumentDB 建立索引索引
- 【原創】ORACLE 分割槽與索引Oracle索引