ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle的索引

Oracle的索引

原创 Linux操作系统 作者:hjianping 时间:2011-04-24 14:08:09 0 删除 编辑

Oracle的索引

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

索引创建策略
1、导入数据后再创建索引
2、不需要为很小的表创建索引
3、对于取值范围很小的字段(比如性别字段)应当建立位图索引
4、限制表中的索引的数目
5、为索引设置合适的pctfree值
6、存储索引的表空间最好单独设定

索引分类

逻辑分类
-单列索引和串接索引:
-唯一索引和非唯一索引:
-基于函数的索引:
-域索引:

物理分类
-分区索引和非分区索引
-B树索引
-位图索引

创建正常的B树索引
create index hr.employees_last_name_idx on hr.employees(last_name)
pctfree 30
storage(initial 200k next 200k pctincrease 0 maxextents 50)
tablespace indx;

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

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

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

创建非唯一索引
create index emp_ename on employees(ename)
tablespace users
storage(......)
pctfree 10;

创建唯一索引
create unique index emp_email on employees(email)
tablespace users;

创建位图索引
create bitmap index orders_region_id_idx
on orders(region_id)
pctfree 30
storage(initial 200k next 200k pctincrease 0 maxextents 50)
tablespace indx;

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

更改索引的存储参数
alter index employees_last_name_idx
storage(next 400k maxextents 100);

创建反序索引
create unique index order_reinx on orders(order_num,order_date)
tablespace users
reverse;

alter index tt_1 rebuild reverse;

创建函数索引(函数索引即可以是普通的B树索引,也可以是位图索引)
create index emp_substr_empno
on employees(substr(empno,1,2))
tablespace users;

清理索引碎片
1.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起,并不会改变索引的物理组织结构)
alter index emp_pk coalesce;

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

删除索引
drop index emp_ename;

分配和回收索引空间
alter index orders_region_id_idx
allocate extent (size 200k datafile '/disk6/indx01.dbf');

alter index orders_id_idx deallocate unused;

检查索引及其有效性
Analyze index scott.pk_emp validate structure;

查看收集的统计信息
select name,(del_lf_rows_len/lf_rows_len)*100 as wastage
from index_stats;

重建浪费大于20%的索引
alter index scott.pk_emp rebuild;

alter index scott.pk_emp coalesce;

监控索引
scott
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
select object_owner, object_name, options, count(*)
from v$sql_plan
where peration='INDEX'
and object_owner='SCOTT'
group by object_owner, object_name, operation, options
order by count(*) desc;


删除索引
drop index hr.deptartments_name_idx;

标识未用索引
alter index hr.dept_id_idx monitoring usage

alter index hr.dept_id_idx nomonitoring usage


可以通过查询以下视图来获取有关索引的信息:
dba_indexes:提供有关索引的信息
dba_ind_columns:提供有关索引列的信息
v$object_usage:提供有关索引使用情况的信息

select 
index_name,table_name,blevel,num_rows,distinct_keys,clustering_factor,leaf_blocks
from all_indexes;

select table_name,pct_free,num_rows,blocks,empty_blocks,avg_row_len,sample_size
from all_tables;

select table_name,column_name,num_nulls,num_distinct,density,low_value, high_value
from user_tab_columns;


索引成本计算的基础知识

索引访问路径的工作流程:
1 为定义的索引列准备相关的谓词
2 定位索引的根块
3 沿着索引分支层往下走,一直到叶块,这也是第一个符合谓词条件的索引项 start key
4 遍历整个叶块连表,一直到最后一个能与谓词匹配的索引项 stop key
5 对每一个索引项,确定是否访问表块

有以上可以知道和索引成本相关的3个因素:
1 按降续遍历的分之层数
2 遍历的叶快数目
3 访问过的表块数目

索引成本的基本公式:
cost = blevel+
       ceiling(leaf_blocks*effective index selectivity)+
       ceiling(clusting_factor*effective table selectivity)

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

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

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

 

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

上一篇: 分区表
下一篇: RMAN基础
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    75431