索引的相关创建与使用
创建普通索引
|
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")<hextoraw('3df3ff') )<="" span="" style="word-wrap: break-word;"> filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SAL"))>1100 AND SYS_OP_DESCEND("SAL")<hextoraw('3df3ff') )<="" span="" style="word-wrap: break-word;">
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>
|
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
