oracle index_stats,oracle的index索引

select * from emp where ename='KING';

selext index_name,column_name from user_ind_columns where

table_name='EMP';

create index emp_name_i on emp(ename)

set autotrace trace exp;

select * from emp where ename='KING';

set autotrace off;

--升序排列

select * From emp where sal>26000 order by sal asc;

create index emp_sal_i on emp(sal asc)

--不排序索引

create index emp1_empno_i on emp1(empno) nosort;

--组合索引，重复越少的列放前面

select * from emp where deptno=20 and job='CLERK';

create index emp_deptno_job_i on emp(deptno,job);

--索引不启用unusable

create index emp_job_i on emp(job) unusable;

alter index emp_ename_i unusable;

alter index emp_job_i rebuild;

--隐藏索引11G

create index emp_ename_i on emp(ename) invisible;

select index_name,visibility from user_indexes;

走隐藏索引

alter session set optimizer_use_invisible_indexes=true;

alter index emp_ename_i visiable;

alter index emp_ename_i

invisiable;--暂时不用的索引不用删除索引隐藏掉

--函数索引

create index emp_ename_i on emp(reverse(ename));

select empno,ename,sal from emp where reverse(ename) like

'A%'

reverse反查询

SQL> select reverse('123456') from dual;

REVERSE('123456')

-----------------

654321

--位图索引

create bitmap index emp_job_i on emp(job);

--强制走索引

select * from emp where job='CLERK'

--文本索引

create table scott.test as select * from tab;

select tname from test where contains(tname,'%SEG%')

create index test__tname_i on test(tname) indextype is

ctxsys.context;

contains

select * From students where contains(

address, 'beijing' or 'shanghai' )

SELECT SCORE(1) title from news WHERE

CONTAINS(text，'oracle'，1)> 0；

知识点:

index block 热块 访问比较多

alter index empq_empno_i rebuild reverse;--反向存储，分散到不同的块

show parameter que 以下参数才能创建函数索引有效

query_rewrite_enabled:TRUE

query_rewrite_integrity:enforced/truseted

--表分析

exec

dbms_stats.gather_table_stats('SCOTT','EMP');

exec

dbms_stats.gather_index_stats('SCOTT','EMP_JOB_I');

--创建函数\调用函数

create or replace function gsal(vsal number)

return number

as

begin

if vsal<2000 then return 1;

elsif vsal between 2000 and 3000 then return 2;

else return 3;

end if;

end gsal;

/

select gsal(800) from dual;

select count(*) from emp where gsal(sal)=1;

--索引监控

select index_name from indexes;

alter index emp_job_i monitoring usage;

select * from v$object_usage;

alter index emp_job_i nomonitoring

usage;

--修改索引名字

alter index emp_job_i rename to emp_job_index;

--分区表索引

全局索引，若加分区删分区需要重建索引

global index---segment one

本地索引,减少维护索引开销，减少索引的热点块

local index ---partition number

desc user_part_key_columns

select name ,column_name from user_part_key_columns;

create index empnew_deptno_i on empnew(deptno) global;

select * from user_ind_partitions

create index empnew_ename_i on empnew(ename) local;

详细见

http://blog.sina.com.cn/s/blog_b56640170102yapq.html

alter index empnew_ename_i rebuild partition

empnew_p5;