当前所在位置: 首页 > 儿童教育 > 正文

oracle有哪些索引

2023-02-02 餐饮美食网 【 字体:

oracle的索引类型有:非唯一索引、唯一索引、位图索引、局部有前缀分区索引、局部无前缀分区索引、全局有前缀分区索引、散列分区索引、基于函数的索引。索引需在表中插入数据后创建,唯一索引可用“create unique index”语句创建。

本教程操作环境:Windows7系统、Oracle 11g版、Dell G3电脑。

什么是索引?

  • 索引是建立在表的一列或多个列上的辅助对象,目的是加快访问表中的数据;
  • Oracle存储索引的数据结构是B*树(平衡树),位图索引也是如此,只不过是叶子节点不同B*数索引;
  • 索引由根节点、分支节点和叶子节点组成,上级索引块包含下级索引块的索引数据,叶节点包含索引数据和确定行实际位置的rowid。

索引说明

1)索引是数据库对象之一,用于加快数据的检索,类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量,类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

2)索引是建立在表上的可选对象;索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式,从而提高检索效率

3)索引在逻辑上和物理上都与的表和数据无关,当创建或者删除一个索引时,不会影响基本的表;

4)索引一旦建立,在表上进行DML操作时(例如在执行插入、修改或者删除操作时),oracle会自动管理索引,索引删除,不会对表产生影响

5)索引对用户是透明的,无论表上是否有索引,sql语句的用法不变

6)oracle创建主键时会自动在该列上创建索引

使用索引的目的:

  • 加快查询速度
  • 减少I/O操作
  • 消除磁盘排序(索引能加快排序速度)

何时使用索引:

  • 查询返回的记录数 排序表<40%,对非排序表<7%
  • 表的碎片较多(频繁增加、删除)

索引的种类

  • 非唯一索引(最常用)
  • 唯一索引
  • 位图索引
  • 局部有前缀分区索引
  • 局部无前缀分区索引
  • 全局有前缀分区索引
  • 散列分区索引
  • 基于函数的索引

管理索引的准则

  • 在表中插入数据后创建索引
  • 在用SQL*Loader或import工具插入或装载数据后,建立索引比较有效;

索引正确的表和列

  • 经常检索排序大表中40%或非排序表7%的行,建议建索引;
  • 为了改善多表关联,索引列用于联结;
  • 列中的值相对比较唯一;
  • 取值范围(大:B*树索引,小:位图索引);
  • Date型列一般适合基于函数的索引;
  • 列中有许多空值,不适合建立索引

为性能而安排索引列

  • 经常一起使用多个字段检索记录,组合索引比单索引更有效;
  • 把最常用的列放在最前面,例:dx_groupid_serv_id(groupid,serv_id),在where条件中使用groupidgroupid,serv_id,查询将使用索引,若仅用到serv_id字段,则索引无效;

合并/拆分不必要的索引。

限制每个表索引的数量

  • 一个表可以有几百个索引(你会这样做吗?),但是对于频繁插入和更新表,索引越多系统CPU,I/O负担就越重;

  • 建议每张表不超过5个索引。

删除不再需要的索引

  • 索引无效,集中表现在该使用基于函数的索引或位图索引,而使用了B*树索引;

  • 应用中的查询不使用索引;

  • 重建索引之前必须先删除索引,若用alter index … rebuild重建索引,则不必删除索引。

索引数据块空间使用

  • 创建索引时指定表空间,特别是在建立主键时,应明确指定表空间;
  • 合理设定pctfress,注意:不能给索引指定pctused;
  • 估计索引的大小和合理地设置存储参数,默认为表空间大小,或initial与next设置成一样大。

考虑并行创建索引

  • 对大表可以采用并行创建索引,在并行创建索引时,存储参数被每个查询服务器进程分别使用,例如:initial1M,并行度为8,则创建索引期间至少要消耗8M空间;

考虑用nologging创建索引

  • 对大表创建索引可以使用nologging来减少重做日志;
  • 节省重做日志文件的空间;
  • 缩短创建索引的时间;
  • 改善了并行创建大索引时的性能。

怎样建立最佳索引?

明确地创建索引

create index index_name on table_name(field_name)
  tablespace tablespace_name
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
  minextents 1
  maxextents 16382
  pctincrease 0
  );

创建基于函数的索引:

常用与UPPER、LOWER、TO_CHAR(date)等函数分类上,例:

create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

创建位图索引:

对基数较小,且基数相对稳定的列建立索引时,首先应该考虑位图索引,例:

create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

明确地创建唯一索引

可以用create unique index语句来创建唯一索引,例:

create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

创建与约束的索引

可以用using index字句,为与uniqueprimary key约束的字段创建索引,例如:

alter table table_name
  add constraint PK_primary_keyname primary key (field_name)
  using index tablespace tablespace_name;

如何创建局部分区索引

  • 基础表必须是分区表;
  • 分区数量与基础表相同;
  • 每个索引分区的子分区数量与相应的基础表分区相同;
  • 基础表的子分区中的行的索引项,被存储在该索引的相应的子分区中,例如:
  Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
  Pctfree 5  Tablespace TBS_AK01_IDX
  Storage (
  MaxExtents 32768  PctIncrease 0  FreeLists 1  FreeList Groups 1  )
  local  /

如何创建范围分区的全局索引

基础表可以是全局表和分区表。

create index idx_start_date on tg_cdr01(start_date)
  global partition by range(start_date)
  (partition p01_idx vlaues less than (‘0106’)
  partition p01_idx vlaues less than (‘0111’)
  …
  partition p01_idx vlaues less than (‘0401’ ))
  /

  重建现存的索引
  重建现存的索引的当前时刻不会影响查询;

  重建索引可以删除额外的数据块;
  提高索引查询效率;

alter index idx_name rebuild nologging;

  对于分区索引:

alter index idx_name rebuild partition partiton_name nologging;

要删除索引的原因

  • 不再需要的索引;
  • 索引没有针对其的表所发布的查询提供所期望的性能改善;
  • 应用没有用该索引来查询数据;
  • 该索引无效,必须在重建之前删除该索引;
  • 该索引已经变的太碎了,必须在重建之前删除该索引;
  • 语句:
    drop index idx_name;
    drop index idx_name drop partition partition_name;

建立索引的代价

基础表维护时,系统要同时维护索引,不合理的索引将严重影响系统资源,主要表现在CPU和I/O上;

插入、更新、删除数据产生大量db file sequential read锁等待;

一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle的索引限制造成的。

oracle的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。

扩展知识:常见的索引限制问题

1、使用不等于操作符(<>, !=)

下面这种情况,即使在列dept_id有一个索引,查询语句仍然执行一次全表扫描

select * from dept where staff_num <> 1000;

但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?

有!

通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。

select * from dept shere staff_num < 1000 or dept_id > 1000;

2、使用 is null 或 is not null

使用 is nullis nuo null也会限制索引的使用,因为数据库并没有定义null值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null会造成很多麻烦。

解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null)

3、使用函数

如果没有使用基于函数的索引,那么where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。下面的查询就不会使用索引:

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

4、比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中,dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198,就是3所说的情况,这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

select * from dept where dept_id = '900198';

5、使用like子句

使用like子句查询时,数据需要把所有的记录都遍历来进行判断,索引不能发挥作用,这种情况也要尽量避免。

Like 的字符串中第一个字符如果是‘%’则用不到索引

Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到

6、使用IN

尽管In写法要比exists简单一些,exists一般来说性能要比In要高的多

In还是用Exists的时机

当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候,用In要好,否则就要用Exists
例:

select count(*) from person_info where xb in (select xb_id from dic_sex);

Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);

Select * from person_info where zjhm=3101….;--将会对person_info全表扫描

Select * from person_info where zjhm =‘3101…’;--才能用到索引

假定TEST表的dt字段是date类型的并且对dt建了索引。
如果要查‘20041010’一天的数据.下面的方法用不到索引

Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

而以下将会用到索引。

select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

7、如果能不用到排序,则尽量避免排序。

用到排序的情况有
集合操作。Union ,minus ,intersect等,注:union all 是不排序的。

Order byGroup byDistinctIn

有时候也会用到排序
确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行,有文章说,内存排序的速度是硬盘排序的1万倍。

在排序的字段上创建索引,让排序在内存中执行,加快排序速度。

8、在基于CBO的优化器(花费)下,表的统计数据过期。也可能导致不使用索引。

解决:执行表分析。获取表的最新信息。

9、获取的数据量过大,全部扫描效率更高

10、索引字段的值分散率太低,值太集中,如类型字段都是1,2, 状态类型Y-有效/N-无效。这类型的字段最好别建索引。

尽管在这些字段上建立了索引,但对全表数据区分度不大。最后还是会全表扫描。

教程:《Oracle教程》

以上就是oracle有哪些索引的详细内容

阅读全文
相关推荐

如何自制蛋挞

如何自制蛋挞
1、倒入低筋面、黄油和水,揉成面团状,藏一小时后取出,抹上黄油卷起,包上保鲜膜冷藏半小时。2、取出,将面团切成一厘米的面团,放入模具内,用力按压,中间稍薄,外缘要比模具高,将蛋挞液倒入蛋挞皮中,放入烤箱里烤30分钟即可。

肉火烧面怎么和面

肉火烧面怎么和面
1、首先用温水把酵母融开,加入面粉,用手把面粉揉成非常软的面团,可以放一点熬好的猪油在面团里面。2、然后放在温暖的地方,盖上保鲜膜发酵至2倍大,发好的面团用手插入不回缩,就说明面团发酵好了。

奶茶几分糖好喝

奶茶几分糖好喝
1、不同的人对奶茶的口感要求是不一样的,一般来说,奶茶有三分糖、五分糖、七分糖三种不同的口味。2、女生一般都喜欢喝甜一点的,而男生则喜欢喝不那么甜的,七分糖的奶茶最好喝。

过桥米线是哪里的?

过桥米线是哪里的?
过桥米线是来自云南省滇南地区的一种有名特色小吃。过桥米线最早是在清朝就已经出现,距离现在至少有一百多年的历史啦!起源于建水县东城外锁龙桥西侧的鸡市街头处,有建水的特产草芽、地椒作配料,风味独特而远近闻名。深受广大美食爱好者的喜欢,不少人为此专门长途跋涉,只为尝

面霜和乳液的区别

面霜和乳液的区别
面霜和乳液的区别,相较而言乳液的水分含量要比面霜高,乳液质地要轻薄一些,乳液主要作用是保湿,滋润可以隔离外界干燥的气候,面霜既可保湿,又可美白,还能抗衰老,乳液的吸收快一些,而面霜吸收比较慢一些,因为液体的吸收速度都比较快。

怎么摘隐形眼镜

怎么摘隐形眼镜
在摘隐形眼镜之前,先用洗手液将手清洗干净,以免将细菌带入眼睛内,对着镜子,用右手中指轻拉眼睛下眼睑,左手中指轻拉眼睛上眼睑,让黑色眼球暴露在空气中,用右手食指和拇指轻触镜片的两边缘部分,向中间推使镜片拱起,再用两手指轻轻捏出镜片即可。

粉饼和散粉的区别

粉饼和散粉的区别
粉饼是呈压缩固体状态,多呈圆形或者方形,散粉则是细腻的粉末状,粉饼遮瑕力会比较强一些,可以湿用做粉底,或者用来补妆,而散粉则是定妆的效果,粉饼通常用在底妆的第一步,而散粉通常用在底妆最后一步。

冷烫和热烫的区别

冷烫和热烫的区别
冷烫和热烫的区别:冷烫对头发的要求是要在保温状态下才能给卷有个好的效果,而热烫是在干和湿的情况下都行,热烫烫出来的头发比较自然明显而有弹力,冷烫的头发风干后基本看不出来,并且发质会有点干。

高品质香水如何鉴别?用三步就可以解决

高品质香水如何鉴别?用三步就可以解决
1、看色泽以天然香料调制而成的高级香水,都有它本来的颜色,且大都是琥珀色或褐色,看起来很像宝石,比如,从茉莉、玫瑰或水仙等天然鲜花中所萃取的精油都呈黄色、褐白或绿褐色;此外,香水中所添加的魅惑香气物性香料也是褐色,苔类中的橡树苔是绿色,从树根或树根脂类中萃取的

如何去除黑眼圈

如何去除黑眼圈
去除黑眼圈首先可以用热毛巾敷眼,然后再用冷毛巾敷,十分钟就能让黑眼圈淡化的不那么严重,其次将去壳后的鸡蛋用无菌纱布包裹住,敷于眼部轻轻转动,可以急救去除黑眼圈,另外抹完眼霜后,用双手顺时针按摩,可以促进血液循环消除黑眼圈。
本文Tag