oracle的索引有几种

发布网友发布时间：2022-04-21 21:41

共2个回答

懂视网时间：2022-04-30 11:04

索引是数据库管理系统提供的一种用来快速访问表中数据的机制，在数据库管理系统中，索引的意义非常重大，使用索引可以显著提高数据的查询效率，减少磁盘的io操作，提升整个数据库的性能。
使用索引具有如下优点:
1.索引可以大大加快检索数据的速度
2.使用唯一性索引可以保证数据表中每一行数据的唯一性
3.通过索引可以加快表与表之间的连接
4 在使用分组和排序子句进行数据检索时，使用索引可以显著地减少查询中分组查询和排序的时候

Oracle操作符两种形式:
1.顺序访问方式
2.索引访问方式

索引原理:
在Oracle数据表中，每一张都有一个ROWID伪列，这个ROWID是用来唯一标志一条所在物理位置的一个id号，每一行对应的ROWID值是固定而且唯一的，一量数据存入数据库就确定，不会在对数据
库表操作的过程中发生改变，只有在表发生移动或表空间变化等操作产生物理位置变化，才会发生改变。
注意:
在索引段中保存排序的索引列的值及代表着物理地址的ROWID值

创建索引:
索引的创建方式分为如下两种。
1.自动创建：在定义主键约束或唯一约束时，Oracle会自动在相应的约束列上建立唯一索引，Oracle不推荐人为地创建唯一性索引
2.手动创建:用户可以在其他列上创建唯一索引。
在Oracle中，索引根据其组织形式又可以分为多种类型，分别如下所示
1.单列索引:索引基于单个列创建
2.复合索引:索引基于多个列所创建
3.B树索引:这是Oracle默认使用的索引,B树索引可以是单列索引或复合索引,唯一索引或非唯一性索引，索引按B树结构组织并存放索引数据
4.位图索引:为索引列的每个取值创建一个位图，对表中的每行使用1位(bit,取值为0或为1)来给示该行是否包含该位图索引的取值
5.函数索引:索引的取值不直接来自列，而是来自包含有列的函数或表达式，这就是函数索引

create table emp_index as select * from emp;
create index idex_emp_name on emp_index(ename,empno);--B树索引
create index idex_emp_job on emp_index(job);--B树索引
create bitmap index idx_emp_job_bitmap on emp_index(job);--位图索引
create index idex_emp_name on emp(upper(ename));--函数索引
注意:当创建复合索引时，索引的顺序决定了索引的性能，通常要将最常查询的列放在前面，不常查询的列放在后面，两个具有不同名称的复合索引列，使用了相同字段但是顺序不同是合法

下面是创建索常见的10条原则
1.小表不需要创建索引,比如emp表只有数十条记录，可以不建立索引
2.对于大表而言，如果经常查询的记录数目少于表中总记录的15%，可以创建索引，这个比例并不绝对，它与全表扫描速度成反比
3.对于大部分列值不重复的列可以建立索引
4.对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引
5.对于列中有许多空值，但经常查询所有的非空值的记录列，应该建立索引
6.long与long ram列不能创建索引
7.经常进行连接查询的列应该建立索引
8.在使用credate index语句创建查询时，将最常查询的列放在其他列前面
9.维护索引需要开销，特别对表进行插入和删除操作时，因此要限制表中索引的数量，对于主要用于读的表，索引多就有好处，但是，如果一个表经常被更改，则索引应该少点
10.在表中插入数据后创建索引，如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改索引

修改索引:
--重命名索引
alter index IDEX_EMP_NAME rename to idx_ename_empno;
select * from user_indexes where index_name=upper(‘IDX_ENAME_EMPNO‘)
合并和重建索引
合并索引:合并索引并不改变索引的物理组织架构只是简单地将B树叶子节点中的存储碎片合并在一起
重建索引L重新创建一个新的索引删除原来的索引

alter index idx_ename_empno coalesce;
合并索引使用alter index coalesce;语法,合并只是简单地将B树中叶子节点的碎片合在一起,其实并没有改变索引的物理组织架构
合并前放在两个叶子节点中的碎片被合并到了1个叶子节点,而另一个叶子节点就被释放了

重建索引实际上就是对原有的索引的删除，再重新建一个新的索引因为这个原因，所以在使用alter index时，可以使用各种存储参数，比如使用STORAGE指定存储参数,使用table space指定表空间或利用nologging选项避免产生重做日志信息
--重建索引
alter index idx_ename_empno rebuild;
--使用存储语句更改索引的所在的表空间
alter index idx_ename_empno rebuild tablespace users;
上面语句在重建索引时的时候，使用tablespace选项将索引移到了users空间中，合并索引和重建索引都能消除索引碎片，但二者使用上有明显的区别
1.合并索引不能将索引移动到其他索引空间，但重建索引可以
2.合并索引代价较低，无须额外存储空间，但重建索引恰恰相反
3.合并索引只能在B树的同一子树中合并，不改变树的高度，但重建索引重建整个B树，可能降低树的高度

分配和释放索引空间
在插入或者加载数据时，如果表具有索引，会同时在索引中添加数据，如果索引段空间不足，为了能够向索引段添加数据将导致动态地扩展索引段，从降低了数据的装载速度，为了避免这个问题，可以在执行装载或大批量插入之前为索引段分配足够的空间

--分配索引空间
alter index idx_ename_empno allocate extent(size 200k)
上述语法首先装饰idx_ename_empno索引段的索引扩容200kb,以便能容纳所插入的索引数据
当索引段占用了过多的空间，而实际上用不这样多的空间时，可以deallcate unused来释放多余的空间，
--释放多余的索引空间
alter index idx_ename_empno deallocate unused;
上述语句执行后，将释放未曾使用的索引空间

删除索引
drop index idx_ename_empno
对于唯一索引，如果是在列定义约束时由Oracle自动建立的，可以通过使用Disable禁用约束或删除约束的方法来删除对应的索引

注意;在删除表时，所有基于该表的索引也会自动删除
当以下情况发生时，需要从数据库中移除索引
1.索引不再需要时，应该删除以释放所占用的空间
2.索引没有经常使用时，只是极少数查询会使用到该索引时。
3.如果索引中包含损坏的数据块，或者是索引碎片过多时，应删除该索引，然后重建索引
4.如果表数据被移动后导致索引无效,此时应删除该索引,然后重建
5.当使用SQL*Loader给表中装载大量数据时，系统也会给表的索引增加数据，为了加快装载速度，可以在装载之前删除索引，在装载之后重新创建索引

5.3.索引
5.3.1.索引说明
一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引
在删除一个表时, 所有基于该表的索引会自动被删除
通过指针加速 Oracle 服务器的查询速度
通过快速定位数据的方法，减少磁盘 I/O

5.3.2.创建索引
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
create Index index on table (column...)
在表employees的列last_name上创建索引
create index emp_last_name_idx
on employees(lasy_name)
index created

5.3.3.什么时候创建索引
以下情况可以创建索引:
列中数据值分布范围很广
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
5.3.4.下列情况不要创建索引:
表很小
列不经常作为连接条件或出现在WHERE子句中
查询的数据大于2%到4%
表经常更新

5.3.5.查询索引
可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
删除索引:
drop index index;

索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：
1如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
　注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
　　2.对于多列索引，不是使用的第一部分，则不会使用索引
　　3.like查询是以%开头

　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
此外，查看索引的使用情况
show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效
查询索引
select object_name, object_type
from user_objects
where object_type = upper(‘index‘);
SQL> conn as1/as1
Connected.

创建表：
SQL> create table dex (id int,sex char(1),name char(10));
Table created.

向表中插入1000条数据
SQL> begin
2 for i in 1..1000
3 loop
4 insert into dex values(i,‘M‘,‘chongshi‘);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

查看表记录
SQL> select * from dex;
        ID SE NAME
---------- -- --------------------       ... . .....
       991 M chongshi
       992 M chongshi
       993 M chongshi
       994 M chongshi
       995 M chongshi
       996 M chongshi
       997 M chongshi
       998 M chongshi
       999 M chongshi
      1000 M chongshi

1000 rows selected.

创建索引：
SQL> create index dex_idx1 on dex(id);
Index created.
注：对表的第一列（id）创建索引。

查看创建的表与索引
SQL> select object_name,object_type from user_objects;

OBJECT_NAME                  OBJECT_TYPE
--------------------------------------------------------------------------------
DEX                           TABLE
DEX_IDX1                      INDEX
索引分离于表，作为一个单独的个体存在，除了可以根据单个字段创建索引，也可以根据多列创建索引。Oracle要求创建索引最多不可超过32列。

SQL> create index dex_index2 on dex(sex,name);
Index created.

SQL> select object_name,object_type from user_objects;

OBJECT_NAME                           OBJECT_TYPE
--------------------------------------------------------------------------------
DEX                                       TABLE
DEX_IDX1                                 INDEX
DEX_INDEX2                               INDEX
创建位图索引：

查看表记录
SQL> select * from dex;
...................
        ID SEX NAME
---------- -- --------------------
       991 M chongshi
       992 M chongshi
       993 G chongshi
       994 G chongshi
       995 G chongshi
       996 M chongshi
       997 G chongshi
       998 G chongshi
       999 G chongshi
      1000 M chongshi

1000 rows selected.

对于上面表来说sex（性别）只有两种值，最适合用来创建位图所引
创建索引：
SQL> create bitmap index my_bit_idx on dex(sex);

Index created.

查看创建的所引
SQL> select object_name,object_type from user_objects;

OBJECT_NAME                           OBJECT_TYPE
--------------------------------------------------------------------------------
MY_BIT_IDX                               INDEX
创建索引的一些规则

1、权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。

这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。

2、把索引与对应的表放在不同的表空间。

当读取一个表时表与索引是同时进行的。如果表与索引和在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。

3、最好使用一样大小是块。

Oracle默认五块，读一次I/O，如果你定义6个块或10个块都需要读取两次I/O。最好是5的整数倍更能提高效率。

4、如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的redo信息，所以在创建索引时可以设置不产生或少产生redo信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生redo信息。

5、建索引的时候应该根据具体的业务SQL来创建，特别是where条件，还有where条件的顺序，尽量将过滤大范围的放在后面，因为SQL执行是从后往前的。（小李飛菜刀）

索引常见操作

改变索引：

SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);索引创建后，感觉不合理，也可以对其参数进行修改。详情查看相关文档

调整索引的空间：

新增加空间
SQL> alter index orders_region_id_idx allocate extent (size 200K datafile ‘/disk6/index01.dbf‘);

释放空间
SQL> alter index oraers_id_idx deallocate unused;
索引在使用的过程中可能会出现空间不足或空间浪费的情况，这个时候需要新增或释放空间。上面两条命令完成新增与释放操作。关于空间的新增oracle可以自动帮助，如果了解数据库的情况下手动增加可以提高性能。

重新创建索引：

所引是由oracle自动完成，当我们对数据库频繁的操作时，索引也会跟着进行修改，当我们在数据库中删除一条记录时，对应的索引中并没有把相应的索引只是做一个删除标记，但它依然占据着空间。除非一个块中所有的标记全被删除的时，整个块的空间才会被释放。这样时间久了，索引的性能就会下降。这个时候可以重新建立一个干净的索引来提高效率。

SQL> alter index orders_region_id_idx rebuild tablespace index02;通过上面的命令就可以重现建立一个索引，oracle重建立索引的过程：

1、锁表，锁表之后其他人就不能对表做任何操作。

2、创建新的（干净的）临时索引。

3、把老的索引删除掉

4、把新的索引重新命名为老索引的名字

5、对表进行解锁。

移动所引：

其实，我们移动索引到其它表空间也同样使用上面的命令，在指定表空间时指定不同的表空间。新的索引创建在别位置，把老的干掉，就相当于移动了。

SQL> alter index orders_region_id_idx rebuild tablespace index03;

在线重新创建索引：

上面介绍，在创建索引的时候，表是被锁定，不能被使用。对于一个大表，重新创建索引所需要的时间较长，为了满足用户对表操作的需求，就产生的这种在线重新创建索引。

SQL> alter index orders_id_idx rebuild online;创建过程：

1、锁住表

2、创建立临时的和空的索引和IOT表用来存在on-going DML。普通表存放的键值，IOT所引表直接存放的表中数据；on-gong DML也就是用户所做的一些增删改的操作。

3、对表进行解锁

4、从老的索引创建一个新的索引。

5、IOT表里存放的是on-going DML信息，IOT表的内容与新创建的索引合并。

6、锁住表

7、再次将IOT表的内容更新到新索引中，把老的索引干掉。

8、把新的索引重新命名为老索引的名字

9、对表进行解锁

如上图，在很多索引中有剩余的空间，可以通过一个命令把剩余空间整合到一起。　　

SQL> alter index orders_id_idx coalesce;

删除索引：

SQL> drop index hr.departments_name_idx;
可以通过一条命令来完成分析索引，分析的结果会存放在在index_stats表中。

查看存放分析数据的表：
SQL> select count(*) from index_stats;

COUNT(*)
----------
0
执行分析索引命令：
SQL> analyze index my_bit_idx validate structure;

Index analyzed.

再次查看 index_stats 已经有了一条数据
SQL> select count(*) from index_stats;

COUNT(*)
----------
1

把数据查询出来：
SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------------------------------------------------------------------- ---------- -----------
2 MY_BIT_IDX 1000 3 100
分析数据分析：

（HEIGHT）这个所引高度是2 ，（NAME）索引名为MY_BIT_IDX ，（LF_ROWS）所引表有1000行数据，（LF_BLKS）占用3个块，（DEL_LF_ROWS）删除100条记录。

　　这里也验证了前面所说的一个问题，删除的100条数据只是标记为删除，因为总的数据条数依然为1000条，占用3个块，那么每个块大于333条记录，只有删除的数据大于333条记录，这时一个块被清空，总的数据条数才会减少。

Oracle索引

标签：oracle 索引

热心网友时间：2022-04-30 08:12

Oracle 提供了多种不同类型的索引以供使用。简单地说， Oracle 中包括如下索引：
1、 B* 树索引
这些是我所说的 “ 传统 “ 索引。到目前为止，这是 Oracle 和大多数其他数据库中最常用的索引。 B* 树的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需很少的读操作就能找到正确的行。不过，需要注意重要的一点， ” B* 树 “ 中的 ” B “ 不代表二叉（ binary ），而代表平衡（ b alanced ）。B* 树索引并不是一颗二叉树，这一点在介绍如何在磁盘上物理地存储 B* 树时就会了解到。 B* 树索引有以下子类型：
索引组织表（ index organized table ）：索引组织表以 B* 树结构存储。堆表的数据行是以一种无组织的方式存储的（只要有可用的空间，就可以放数据），而 IOT 与之不同， IOT 中的数据要按主键的顺序存储和排序。对应用来说， IOT 表现得与 “ 常规 “ 表并无二致；需要使用 SQL 来正确地访问 IOT 。 IOT 对信息获取、空间系统和 OLAP 应用最为有用。 IOT 在上一章已经详细地讨论过。
B*树聚簇索引（ B*tree cluster index ）这些是传统 B* 树索引的一个变体（只是稍有变化）。 B* 树聚簇索引用于对聚簇键建立索引（见第 11. 章中 “ 索引聚簇表 “ 一节），所以这一章不再讨论。在传统 B* 树中，键都指向一行；而 B* 树聚簇不同，一个聚簇键会指向一个块，其中包含与这个聚簇键相关的多行。
降序索引（ descending index ）：降序索引允许数据在索引结构中按 “ 从大到小 “ 的顺序（降序）排序，而不是按 ” 从小到大 “ 的顺序（升序）排序。我们会解释为什么降序索引很重要，并说明降序索引如何工作。
反向键索引（ reverse key index ）：这也是 B* 树索引，只不过键中的字节会 “ 反转 “ 。利用反向键索引，如果索引中填充的是递增的值，索引条目在索引中可以得到更均匀的分布。例如，如果使用一个序列来生成主键，这个序列将生成诸如 987500 、 987501 、 987502 等值。这些值是顺序的，所以倘若使用一个传统的 B* 树索引，这些值就可能放在同一个右侧块上，这就加剧了对这一块的竞争。利用反向键， Oracl e则会逻辑地对 205789 、 105789 、 005789 等建立索引。 Oracle 将数据放在索引中之前，将先把所存储数据的字节反转，这样原来可能在索引中相邻放置的值在字节反转之后就会相距很远。通过反转字节，对索引的插入就会分布到多个块上。

2、位图索引（ bitmap index ）
在一颗 B* 树中，通常索引条目和行之间存在一种一对一的关系：一个索引条目就指向一行。而对于位图索引，一个索引条目则使用一个位图同时指向多行。位图索引适用于高度重复而且通常只读的数据（高度重复是指相对于表中的总行数，数据只有很少的几个不同值）。考虑在一个有 100 万行的表中，每个列只有 3 个可取值： Y 、 N 和 NULL 。举例来说，如果你需要频繁地统计多少行有值Y ，这就很适合建立位图索引。不过并不是说如果这个表中某一列有 11.000 个不同的值就不能建立位图索引，这一列当然也可以建立位图索引。在一个 OLTP 数据库中，由于存在并发性相关的问题，所以不能考虑使用位图索引（后面我们就会讨论这一点）。注意，位图索引要求使用 Oracle 企业版或个人版。
位图联结索引（ bitmap join index ）：这为索引结构（而不是表）中的数据提供了一种逆规范化的方法。例如，请考虑简单的 EMP 和 DEPT 表。有人可能会问这样一个问题： “ 多少人在位于波士顿的部门工作？“ EMP 有一个指向 DEPT 的外键，要想统计 LOC 值为 Boston 的部门中的员工人数，通常必须完成表联结，将 LOC 列联结至 EMP 记录来回答这个问题。通过使用位图联结索引，则可以在 EMP 表上对 LOC 列建立索引。
3、基于函数的索引（ function-based index ）
这些就是 B* 树索引或位图索引，它将一个函数计算得到的结果存储在行的列中，而不是存储列数据本身。可以把基于函数的索引看作一个虚拟列（或派生列）上的索引，换句话说，这个列并不物理存储在表中。基于函数的索引可以用于加快形如 SELECT * FROM T W HERE FUNCTION(DATABASE_COLUMN) = SAME_VALUE 这样的查询，因为值 FUNCTION(DATABASE_COLUMN) 已经提前计算并存储在索引中。
4、应用域索引（ application domain index ）
应用域索引是你自己构建和存储的索引，可能存储在Oracle 中，也可能在 Oracle 之外。你要告诉优化器索引的选择性如何，以及执行的开销有多大，优化器则会根据你提供的信息来决定是否使用你的索引。 Oracle 文本索引就是应用域索引的一个例子；你也可以使用构建 Oracle 文本索引所用的工具来建立自己的索引。需要指出，这里创建的 “ 索引 “ 不需要使用传统的索引结构。例如， Oracle 文本索引就使用了一组表来实现其索引概念。

5、HASH索引
使用HASH索引必须要使用HASH群集。建立一个群集或HASH群集的同时，也就定义了一个群集键。这个键告诉Oracle如何在群集上存储表。在存储数据时，所有与这个群集键相关的行都被存储在一个数据库块上。若数据都存储在同一个数据库块上，并且使用了HASH索引，Oracle就可以通过执行一个HASH函数和I/O来访问数据——而通过适用一个二元高度为4的B-树索引来访问数据，则需要在检索数据时使用4个I/O。
技巧：HASH索引在有*条件（需要指定一个确定的值而不是一个值范围）的情况下非常有用。
6、分区索引
分区索引就是简单地把一个索引分成多个片断，这样可以访问更小的片断，并且可以把这些片断分别存放在不同的硬盘上（避免I/O问题）。B-数索引和位图索引都可以被分区，HASH索引不可以被分区。
有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。如果使用了位图索引就必须是本地索引。
把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。