oracle 索引

最新推荐文章于 2024-07-26 07:30:00 发布

llxia_honey

最新推荐文章于 2024-07-26 07:30:00 发布

阅读量472

点赞数

分类专栏： oracle index 文章标签： oracle table 存储磁盘 user object

oracle index 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一．索引介绍

1.1 索引的创建语法：

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>

ON <schema>.<table_name>

相关说明

1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
4）STORAGE：可进一步设置表空间的存储参数
5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
6）COMPUTE STATISTICS：创建新索引时收集统计信息
7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

1.2 索引特点：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1.3 索引不足：

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

1.4 应该建索引列的特点：

1）在经常需要搜索的列上，可以加快搜索的速度；

2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

1.5 不应该建索引列的特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引rowid

存储了row在数据文件中的具体位置：64位编码的数据，A-Z, a-z, 0-9, +, 和 /，
　　row在数据块中的存储方式
　　SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;
　　比如：OOOOOOFFFBBBBBBRRR
　　OOOOOO：data object number, 对应dba_objects.data_object_id
　　FFF：file#, 对应v$datafile.file#
　　BBBBBB：block#
　　RRR：row#
　　Dbms_rowid包
　　SELECT dbms_rowid.rowid_block_number('AAAGFqAABAAAIWEAAA') from dual;
　　具体到特定的物理文件

通过 dbms_rowid包获得rowid的详细信息：创建get_rowid函数

create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,
object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;

1.10 选择性
使用USER_INDEXES视图，该视图中显示了一个distinct_keys列。比较一下唯一键的数量和表中的行数，就可以判断索引的选择性。选择性越高，索引返回的数据就越少。

1.11 群集因子(Clustering Factor)
Clustering Factor位于USER_INDEXES视图中。该列反映了数据相对于已建索引的列是否显得有序。如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目，表中的数据就越有序。如果它的值接近于表中的行数，则表中的数据就不是很有序。

1.12 二元高度(Binary height)
索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用。在对一个索引进行分析后，可以通过查询DBA_INDEXES的B- level列查看它的二元高度。二元高度主要随着表的大小以及被索引的列中值的范围的狭窄程度而变化。索引上如果有大量被删除的行，它的二元高度也会增加。更新索引列也类似于删除操作，因为它增加了已删除键的数目。重建索引可能会降低二元高度。

1.13 快速全局扫描
从Oracle7.3后就可以使用快速全局扫描(Fast Full Scan)这个选项。这个选项允许Oracle执行一个全局索引扫描操作。快速全局扫描读取B-树索引上所有树叶块。初始化文件中的 DB_FILE_MULTIBLOCK_READ_COUNT参数可以控制同时被读取的块的数目。

1.14 跳跃式扫描
从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多。

SQL> select /*+ index(tt TT_index )*/ count(areacode) from tt;

Oracle 索引组织表（IOT）

索引组织表（IOT）有一种类B树的存储组织方法。普通的堆组织表是以一种无序的集合存储。而IOT中的数据是按主键有序的存储在B树索引结构中。与一般B树索引不同的的是，在IOT中每个叶结点即有每行的主键列值，又有那些非主键列值。

在IOT所对应的B树结构中，每个索引项包括<主键列值，非主键列值>而不是ROWID，对于普通堆组织表，oracle会有对应的索引与之对应，且分开存储。换句话说，IOT即是索引，又是实际的数据。

索引组织表(IOT)不仅可以存储数据，还可以存储为表建立的索引。索引组织表的数据是根据主键排序后的顺序进行排列的，这样就提高了访问的速度。但是这是由牺牲插入和更新性能为代价的(每次写入和更新后都要重新进行重新排序)。

索引组织表的创建格式如下：

create table indexTable(

ID varchar2 ( 10 ),

NAME varchar2 ( 20 ),

constraint pk_id primary key ( ID )

)

organization index ;式

注意两点：

● 创建IOT时，必须要设定主键，否则报错。

● 索引组织表实际上将所有数据都放入了索引中。

索引组织表属性

1、OVERFLOW子句(行溢出)

因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的设置有两种格式：

PCTTHRESHOLD n ：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段

INCLUDING column_name ：指定列之前的列都放入索引块，之后的列都放到溢出段

● 当行中某字段的数据量无法确定时使用PCTTHRESHOLD。

● 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。

create table t88(

ID varchar2 ( 10 ),

NAME varchar2 ( 20 ),

constraint pk_id primary key ( ID )

)

organization index

PCTTHRESHOLD 20

overflow tablespace users

INCLUDING name ;

● 如上例所示，name及之后的列必然被放入溢出列，而其他列根据 PCTTHRESHOLD 规则。

2、COMPRESS子句(键压缩)

与普通的索引一样，索引组织表也可以使用COMPRESS子句进行键压缩以消除重复值。

具体的操作是，在organization index之后加上COMPRESS n子句

● n的意义在于：指定压缩的列数。默认为无穷大。

例如对于数据(1,2,3)、(1,2,4)、(1,2,5)、(1,3,4)、(1,3,5)时

若使用COMPRESS则会将重复出现的(1,2)、(1,3)进行压缩

若使用COMPRESS 1时，只对数据(1)进行压缩

索引组织表的维护

索引组织表可以和普通堆表一样进行INSERT、UPDATE、DELETE、SELECT操作。

可使用ALTER TABLE ... OVERFLOW语句来更改溢出段的属性。

altertable t88 addoverflow; --新增一个overflow

● 要ALTER任何OVERVIEW的属性，都必须先定义overflow，若建表时没有可以新增

altertable t88 pctthreshold15includingname; --调整overflow的参数

altertable t88 initrans2overflowinitrans4; --修改数据块和溢出段的initrans特性

● 关于initrans的概念参考 http://space.itpub.net/265709/viewspace-166534

索引组织表的应用

Heap Table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。

IOT 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。
适用于信息检索、空间和OLAP程序。

    索引组织表的适用情况：
    1、代码查找表。
    2、经常通过主码访问的表。
    3、构建自己的索引结构。
    4、加强数据的共同定位，要数据按特定顺序物理存储。
    5、经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。

经常更新的表当然不适合IOT，因为oracle需要不断维护索引，而且由于字段多索引成本就大。

如果不是经常使用主键访问表，就不要使用IOT

IOT和普通表对于应用程序来说，例如sql查询语句，是没有区别的。也就是说oracle中对表的组织形式对应用来说是透明的。
使用IOT的好处：
1、由于索引项和数据存储在一起，所以无论是基于主键的等值查询还是范围查询都能大大节省磁盘访问时间。
2、为了能够更快地访问那些频繁访问的列，可以使用溢出存储选项将那些访问不频繁的列放在B树叶结点数据块之外的溢出堆空间中。这样一来便可以得到更小的B树，以及包含更多行的叶结点
3、和堆组织表和索引不同，主键不需要被存储两次。
4、ROWID伪列是基于主键值的逻辑rowid，而不是物理rowid，即使表被重新组织过，造成了基表行的迁移，二级索引仍然可用，不需要重建。

注：
1、Oracle使用rowid数据类型存储行地址,rowid可以分成两种，分别适于不同的对象，Physical rowids：存储ordinary table,clustered table,table partition and subpartition,indexe,index partition and subpartition；Logical rowids :存储IOT的行地址
2、每个表在oracle内部都有一个ROWID伪列,它在所有sql中无法显示，不占存储空间;它用于从表中查询行的地址或者在where中进行参照,一个例子如下：
SELECT ROWID, last_name FROM employees; Oracle内部使用保留在ROWID伪列中的值构建索引结构,rowid伪列不存储在数据库中,它不是数据库表的数据,（从database及table的逻辑结构来说）。事实上,在物理结构上,每行由一个或多个row pieces组成,每个row piece的头部包含了这个piece的address,即rowid.从这个意义上来说，rowid还是占了磁盘空间的.
3、二级索引：也可理解为聚集索引，好比是我们人查字典时自已会使用的索引。

2.5 反转键索引
当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。
为了解决这个问题，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。

技巧：如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。
不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

一般一张表超过2G的大小，ORACLE是推荐使用分区表的，分区一般都需要创建索引，说到分区索引，就可以分为：全局索引、分区索引，即：global索引和local索引，前者为默认情况下在分区表上创建索引时的索引方式，并不对索引进行分区（索引也是表结构，索引大了也需要分区，关于索引以后专门写点）而全局索引可修饰为分区索引，但是和local索引有所区别，前者的分区方式完全按照自定义方式去创建，和表结构完全无关，所以对于分区表的全局索引有以下两幅网上常用的图解：

2.1、对于分区表的不分区索引（这个有点绕，不过就是表分区，但其索引不分区）：

分区表默认全局（不分区索引结构）

创建语法(直接创建即可)：

CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);

2.2、对于分区表的分区索引:

全局分区索引

创建语法为：

CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1)
GLOBAL PARTITION BY RANGE(COL1)(
         PARTITION IDX_P1 values less than (1000000),
         PARTITION IDX_P2 values less than (2000000),
         PARTITION IDX_P3 values less than (MAXVALUE)
);

2.3、LOCAL索引结构：

LOCAL分区索引

创建语法为：

CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;

也可按照分区表的的分区结构给与一一定义，索引的分区将得到重命名。

分区上的位图索引只能为LOCAL索引，不能为GLOBAL全局索引。

2.4、对比索引方式：

一般使用LOCAL索引较为方便，而且维护代价较低，并且LOCAL索引是在分区的基础上去创建索引，类似于在一个子表内部去创建索引，这样开销主要是区分分区上，很规范的管理起来，在OLAP系统中应用很广泛；而相对的GLOBAL索引是全局类型的索引，根据实际情况可以调整分区的类别，而并非按照分区结构一一定义，相对维护代价较高一些，在OLTP环境用得相对较多，这里所谓OLTP和OLAP也是相对的，不是特殊的项目，没有绝对的划分概念，在应用过程中依据实际情况而定，来提高整体的运行性能。

3、常用视图：

1、查询当前用户下有哪些是分区表：

SELECT * FROM USER_PART_TABLES;

2、查询当前用户下有哪些分区索引：

SELECT * FROM USER_PART_INDEXES;

3、查询当前用户下分区索引的分区信息：

SELECT * FROM USER_IND_PARTITIONS T
WHERE T.INDEX_NAME=?

4、查询当前用户下分区表的分区信息：

SELECT * FROM USER_TAB_PARTITIONS T
WHERE T.TABLE_NAME=?;

5、查询某分区下的数据量：

SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);

6、查询索引、表上在那些列上创建了分区：

SELECT * FROM USER_PART_KEY_COLUMNS;

7、查询某用户下二级分区的信息（只有创建了二级分区才有数据）：

SELECT * FROM USER_TAB_SUBPARTITIONS;

4、维护操作：

4.1、删除分区

ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;

如果是全局索引，因为全局索引的分区结构和表可以不一致，若不一致的情况下，会导致整个全局索引失效，在删除分区的时候，语句修改为：

ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATE GLOBAL INDEXES;

4.2、分区合并（从中间删除掉一个分区，或者两个分区需要合并后减少分区数量）

合并分区和删除中间的RANGE有点像，但是合并分区是不会删除数据的，对于LIST、HASH分区也是和RANGE分区不一样的，其语法为：

ALTER TABLE TABLE_PARTITION MERGE PARTITIONS TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION;

4.3、分隔分区（一般分区从扩展分区从分隔）

ALTER TABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000)
INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE)

4.4、创建新的分区（分区数据若不能提供范围，则插入时会报错，需要增加分区来扩大范围）

一般有扩展分区的是都是用分隔的方式，若上述创建表时没有创建TAB_PARTOTION_OTHER分区时，在插入数据较大时（按照上述建立规则，超过1800000就应该创建新的分区来存储），就可以创建新的分区，如：

为了试验，我们将扩展分区先删除掉再创建新的分区（因为ORACLE要求，分区的数据不允许重叠，即按照分区字段同样的数据不能同时存储在不同的分区中）：

ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_OTHER;

ALTER TABLE TABLE_PARTITION ADD PARTITION TAB_PARTOTION_06 VALUES LESS THAN(2500000);

在分区下创建新的子分区大致如下（RANGE分区，若为LIST或HASH分区，将创建方式修改为对应的方式即可）：

ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADD SUBPARTITION <user_define_subpartition_name> VALUES LESS THAN(....);

4.5、修改分区名称（修改相关的属性信息）

ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;

4.6、交换分区（快速交换数据，其实是交换段名称指针）

首先创建一个交换表，和原表结构相同，如果有数据，必须符合所交换对应分区的条件：

CREATE TABLE TABLE_PARTITION_2
AS SELECT * FROM TABLE_PARTITION WHERE 1=2;

然后将第一个分区的数据交换出去：

ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01
WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES;

此时会发现第一个分区的数据和表TABLE_PARTITION_2做了瞬间交换，比TRUNCATE还要快，因为这个过程没有进行数据转存，只是段名称的修改过程，和实际的数据量没有关系。

如果是子分区也可以与外部的表进行交换，只需要将关键字修改为：SUBPARTITION 即可。

4.7、清空分区数据

ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;

ALTER TABLE <table_name> TRUNCATE subpartition <subpartition_name>;

9、磁盘碎片压缩

对分区表的某分区进行磁盘压缩，当对分区内部数据进行了大量的UPDATE、DELETE操作后，一定时间需要进行磁盘压缩，否则在查询时，若通过FULL SCAN扫描数据，将会把空块也会扫描到，对表进行磁盘压缩需要进行行迁移操作，所以首先需要操作：

ALTER TABLE <table_name> ENABLE ROW MOVEMENT ;

对分区表的某分区压缩语法为：

ALTER TABLE <table_name>

modify partition <partition_name> shrink space;

对普通表压缩：

ALTER TABLE <table_name> shrink space;

对于索引也需要进行压缩，索引也是表：

ALTER INDEX <index_name> shrink space;

10、分区表重新分析以及索引重新分析

对表进行压缩后，需要对表和索引进行重新分析，对表进行重新分析，一般有两种方式：

在ORACLE 10G以前，使用：

  BEGIN
     dbms_stats.gather_table_stats(USER,UPPER('<table_name>'));
  END;

ORACLE 10G后，可以使用：

ANALYZE TABLE <table_name> COMPUTE STATISTICS;

索引重新分析，将上述两种方式分别修改一下，如第一种可以使用：gather_index_stats，而第二种修改为：ANALYZE INDEX即可，不过一般比较常用的是重新编译：

对于分区表并进行了索引分区的情况，需要对每个分区的索引进行重新编译,这里以LOCAL索引为例子（其每个索引的分区和表分区结构相同，默认分区名称和表分区名称相同）：

ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;

对于全局索引，根据全局索引锁定义的分区名称修改即可，若没有分区，和普通单表索引重新编译方式相同：

ALTER INDEX <index_name> REBUILD;

11、关联对象重新编译

上述对表、索引进行重新编译，尤其对表进行了压缩后会产生行迁移，这个过程可能会导致一些视图、过程对象的失效，此时要将其重新编译一次。

12、扩展：HASH分区中，如果创建了新的分区，可以将其进行重新HASH分布：

ALTER TABLE <table_name> COALESCA PARTITION

部分信息来自：

http://tech.it168.com/a2009/0603/582/000000582525.shtml

http://database.51cto.com/art/200805/73789.htm

http://blog.csdn.net/tianlesoftware/article/details/5347098

http://blog.csdn.net/dnnyyq/article/details/5195472

http://blog.csdn.net/xieyuooo/article/details/5437126

llxia_honey

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle 索引

一．索引介绍 1.1 索引的创建语法： CREATE UNIUQE | BITMAP INDEX . ON . ( | ASC | DESC, | ASC | DESC,...) TABLESPACE STORAGE LOGGING | NOLOGGING
复制链接

扫一扫