1.返回行比较小时使用index 10g大表的4%左右,小表1%左右

2.indexing the wrong columns 会导致很大的问题,如在区分度很差的大表上建index且强制走这个index

3.建立concatenated index 加快查询

select ename from employees where deptno=10;

create index emp_idx on employee (deptno,ename).

 4.The Fast Full Scan  只读取index,以全表扫描的形式扫描index.所以速度会很快

select  /*+ index_ffs(employees emp_idx) */ ename from employees where deptno=10;

5.Caching a table in Memory

cache small and more often used tables in memory.

alter table customer cache;

表会被放在'most recently used" ed of the cache. 要是全表扫描的表被pushed out后会把这些给pushed out.

cache table会影响执行计划.

6.当有多个index时,选择selectivity 高的

7.the index merge

select  /*+ index_join(test2 year_idx state_idx) */

state,year

from test2 where year='1972' and stat='MA'

二.indexes that get suppressed

1.a math function

select * from product where product_id+12=166

改成select * from product where prodcut_id=154.

2.function

select * from product where substr(product_id,1,1)=1;

改成select * from produt where product_id like '1%';

或者建function-based index

SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
SQL> show parameter query_rewrite_integrity

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_integrity              string      enforced
SQL>
两个参数需设置

查看有那些function-based index

select * from dba_ind_expressions;

3.function-based indexes

create index emp_idx on emp(upper(ename));

select * from emp where upper(ename)='ELLISON';