Oracle的索引

Oracle的索引

索引和对应的表应该位于不同的表空间中,oracle能够并行读取位于不同硬盘上的数据，可以避免产生I/O冲突。B树索引：在B树的叶节点中存储索引字段的值与ROWID。唯一索引和不唯一索引都只是针对B树索引而言。Oracle最多允许包含32个字段的复合索引。

索引创建策略
We y&Ug01、导入数据后再创建索引ITPUB个人空间 Pu.}] T
2、不需要为很小的表创建索引ITPUB个人空间1Q ?D TF%S#h4_Fi
3、对于取值范围很小的字段(比如性别字段)应当建立位图索引ITPUB个人空间nR@&d {#h
4、限制表中的索引的数目
-g#Y^}3B+Q)Vp05、为索引设置合适的pctfree值ITPUB个人空间G3e y%Zb!B"Sl;z
6、存储索引的表空间最好单独设定

索引分类

逻辑分类
2} p zMf6@9A0-单列索引和串接索引：
|)c*]0c#i+X2Pm0-唯一索引和非唯一索引：
)E4Mk n L&b8h@0-基于函数的索引：ITPUB个人空间4|Z)f.ueJL {k.\
-域索引：

物理分类
0a%{d"d:]1we0-分区索引和非分区索引
S'uG]w#UE%Y0-B树索引
%A1nJH5X6h,KGZiub0-位图索引

创建正常的B树索引ITPUB个人空间hbJe,g"Bc@ \
create index hr.employees_last_name_idx on hr.employees(last_name)ITPUB个人空间/O{\S%K }:K
pctfree 30ITPUB个人空间2sH4Rk8W E+qc}
storage(initial 200k next 200k pctincrease 0 maxextents 50)
5nL:|@y-pD7Z#[k0tablespace indx;

pctfree值指定为由下列等式所表示的值是非常有用的：

pctfree=((最大行数– 初始行数)/最大行数)x100

最大值可用于特定的时间周期，如一年。

创建非唯一索引
k R ZW6~4I;z#p:t0create index emp_ename on employees(ename)
x Iv S {:^d |0tablespace users
E w/l1B vf_J0storage(......)
(V7M2Sz/AR0BLA{8K0pctfree 10;

创建唯一索引ITPUB个人空间EDt9b3l4ovKox9|
create unique index emp_email on employees(email)ITPUB个人空间_2v.@0Z-n!SC5t'lO
tablespace users;

创建位图索引
I:sR:~+ZY-E0create bitmap index orders_region_id_idxITPUB个人空间 c7]8z9t j^
on orders(region_id)
u6l#}0q6p~:D'g0pctfree 30
1Sb _;a-m0storage(initial 200k next 200k pctincrease 0 maxextents 50)ITPUB个人空间]Mh|R&_W;x
tablespace indx;

初始化参数create_bitmap_area_size决定了内存中用于存储位图段的空间量。缺省值为8mb。

更改索引的存储参数ITPUB个人空间TR M#M8[
alter index employees_last_name_idx
xg RBYUAv }0storage(next 400k maxextents 100);

创建反序索引ITPUB个人空间)]9|GdQ-u
create unique index order_reinx on orders(order_num,order_date)ITPUB个人空间0Jll!L'rI
tablespace users
GN?!hq6?$T,sk0reverse;

alter index tt_1 rebuild reverse;

创建函数索引(函数索引即可以是普通的B树索引，也可以是位图索引)
dF ohw*W }0m A6P*V0create index emp_substr_empnoITPUB个人空间8q1s3h${MWV
on employees(substr(empno,1,2))ITPUB个人空间@9b C"f8{F&a#D
tablespace users;

清理索引碎片ITPUB个人空间J I2ocE"^
1.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起，并不会改变索引的物理组织结构)ITPUB个人空间;qTh y9D4I3C
alter index emp_pk coalesce;

2.重建索引(不仅能够消除存储碎片,还可以改变索引的全部存储参数设置，并且可以将索引移动到其它的表空间中,重建索引实际上就是再指定的表空间中重新建立一个新的索引,然后删除原来的索引)
6X)nI'rrJ6a!QS0alter index emp_pk rebuild;
K&wBv9L0alter index emp_pk rebuild online;

删除索引
(a8q~"m-ReGW#_k0drop index emp_ename;

分配和回收索引空间
a/Ej,X#eQ0alter index orders_region_id_idxITPUB个人空间2t[ ~+nF
allocate extent (size 200k datafile '/disk6/indx01.dbf');

alter index orders_id_idx deallocate unused;

检查索引及其有效性ITPUB个人空间l3jB'Nti `@
Analyze index scott.pk_emp validate structure;

查看收集的统计信息ITPUB个人空间'D7G D6IUg/R
select name,(del_lf_rows_len/lf_rows_len)*100 as wastageITPUB个人空间 }&^`)D @`#lk n
from index_stats;

重建浪费大于20%的索引ITPUB个人空间L ~]"[R+}9U
alter index scott.pk_emp rebuild;ITPUB个人空间@`Q'|;w
或
/oZ\S;i@u m*b0alter index scott.pk_emp coalesce;

监控索引ITPUB个人空间3L(?5Z2R.nv({
scottITPUB个人空间'|w:[`&w$l,}7VH
alter index scott.pk_emp monitoring usage;

select * from scott.emp where empno=8000;

alter index scott.pk_emp nomonitoring usage;

select * from v$object_usage;

sys
%[J*PTaRHu(}8y0select object_owner, object_name, options, count(*)ITPUB个人空间r_/Nc/UC.C&rlV
from v$sql_planITPUB个人空间*F&a._'u-[J
where peration='INDEX'
0wB2t6@ h2es4C*dQ"?0and object_owner='SCOTT'ITPUB个人空间NO*gP [U#X/OC2M
group by object_owner, object_name, operation, optionsITPUB个人空间2{|l?`$j
order by count(*) desc;

va&~-P0RM0删除索引
t7Jj^fA0drop index hr.deptartments_name_idx;

标识未用索引
*A'Ac'u {!K,I;qD$r/P0alter index hr.dept_id_idx monitoring usage

alter index hr.dept_id_idx nomonitoring usage

1[ gM/S:Th0可以通过查询以下视图来获取有关索引的信息：ITPUB个人空间:bGw$tOQ!bh
dba_indexes：提供有关索引的信息
Xb)j.d*F(b P!`0dba_ind_columns：提供有关索引列的信息
-^^?Um zj x0v$object_usage：提供有关索引使用情况的信息

select ITPUB个人空间h|4KP8Hh#[$UY'`
index_name,table_name,blevel,num_rows,distinct_keys,clustering_factor,leaf_blocksITPUB个人空间;sG]"V2Pz
from all_indexes;

select table_name,pct_free,num_rows,blocks,empty_blocks,avg_row_len,sample_sizeITPUB个人空间0m^qfA/YerD3W
from all_tables;

select table_name,column_name,num_nulls,num_distinct,density,low_value, high_value
&c p@rr0from user_tab_columns;

2Mal*`?T1{,^0索引成本计算的基础知识

索引访问路径的工作流程：ITPUB个人空间,TUBl0[4O5I-\_x
1 为定义的索引列准备相关的谓词ITPUB个人空间-T)d^ddj$V/i$S#QD0U
2 定位索引的根块
`1CF2D\&N.K03 沿着索引分支层往下走，一直到叶块，这也是第一个符合谓词条件的索引项 start key
1B[v1J;e4E8cY-C04 遍历整个叶块连表，一直到最后一个能与谓词匹配的索引项 stop key
tqX|F05 对每一个索引项，确定是否访问表块

有以上可以知道和索引成本相关的3个因素：ITPUB个人空间G'F%cE`!m;I'K|
1 按降续遍历的分之层数ITPUB个人空间/u*p8k"f9m u%`
2 遍历的叶快数目
6T+jt2esm6{03 访问过的表块数目

索引成本的基本公式：
SI1p ND0cost = blevel+ITPUB个人空间 c1@!]@*E
       ceiling(leaf_blocks*effective index selectivity)+ITPUB个人空间f?lRs
       ceiling(clusting_factor*effective table selectivity)

第一行：blevel由于平衡二叉树的原来，根块到每个叶块的距离是相等的，也就是说无论遍历哪个叶块，降序遍历访问的层数是相等的

第二行 ：leaf_blocks*effective index selectivity表示的遍历的叶块数目

第三行 ：clusting_factor*effective table selectivity表示表块访问的数目

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25957310/viewspace-700664/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25957310/viewspace-700664/