Oracle数据库中的索引详解

最新推荐文章于 2024-09-11 08:43:55 发布

海鲨数据库架构师_小凡仙.曾.姒

最新推荐文章于 2024-09-11 08:43:55 发布

阅读量2.4k

点赞数

分类专栏： Oracle 管理文章标签： oracle 数据库 table logging 存储 c

Oracle 管理专栏收录该内容

184 篇文章 1 订阅

订阅专栏

一、 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;

　　具体到特定的物理文件

　　二、索引的概念

　　1、类似书的目录结构

　　2、 Oracle 的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度

　　3、索引直接指向包含所查询值的行的位置，减少磁盘I/O

　　4、与所索引的表是相互独立的物理结构

　　5、 Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引

--工作感觉中所谓oracle自动维护在插入后，其实好像并非如此！不是oracle 独立的进程在插入后开始维护

而是在插入这个SESSION中维护，在insert into commit; 语句后维护。

　　6、语法：CREATE INDEX index ON table (column[, column]...);

　　7、 B-tree结构(非bitmap)：

　　[一]了解索引的工作原理：

　　表：emp

　　目标：查询Frank的工资salary

　　建立索引：create index emp_name_idx on emp(name);

以下图说明节点和叶节点的关系，节点负责二叉树的路径，如同书目录左边的目录列表；

叶节点就是目录列表右边的页码

索引也会很大化因为它也要存储些字段和字段的值

三、唯一索引

　　1、何时创建：当某列任意两行的值都不相同

　　2、当建立Primary Key(主键)或者Unique constraint(唯一约束)时，唯一索引将被自动建立

　　3、语法：CREATE UNIQUE INDEX index ON table (column);

　　4、演示

　　四、组合索引

　　1、何时创建：当两个或多个列经常一起出现在where条件中时，则在这些列上同时创建组合索引

　　2、组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面

　　3、演示(组合列，单独列)

　　五、位图索引

　　1、何时创建：

　　列中有非常多的重复的值时候。例如某列保存了 “性别”信息。

　　Where 条件中包含了很多OR操作符。

　　较少的update操作，因为要相应的跟新所有的bitmap

　　2、结构：位图索引使用位图作为键值，对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

　　3、优点：位图以一种压缩格式存放，因此占用的磁盘空间比标准索引要小得多

　　4、语法：CREATE BITMAP INDEX index ON table (column[, column]...);

　　5、掩饰：

　　create table bitmaptable as select * from indextable where owner in('SYS','PUBLIC');

　　分析，查找，建立索引，查找

　　六、基于函数的索引

　　1、何时创建：在WHERE条件语句中包含函数或者表达式时

　　2、函数包括：算数表达式、PL/SQL函数、程序包函数、SQL函数、用户自定义函数。

　　3、语法：CREATE INDEX index ON table (FUNCTION(column));

　　4、演示

　　必须要分析表，并且query_rewrite_enabled=TRUE

　　或者使用提示/*+ INDEX(ic_index)*/

七、反向键索引

　　目的：比如索引值是一个自动增长的列：

　　多个用户对集中在少数块上的索引行进行修改，容易引起资源的争用，比如对数据块的等待。此时建立反向索引。

　　性能问题：

　　语法：

　　重建为标准索引：反之不行

　　八、键压缩索引

　　比如表landscp的数据如下：

　　site feature job

　　Britten Park, Rose Bed 1, Prune

　　Britten Park, Rose Bed 1, Mulch

　　Britten Park, Rose Bed 1,Spray

　　Britten Park, Shrub Bed 1, Mulch

　　Britten Park, Shrub Bed 1, Weed

　　Britten Park, Shrub Bed 1, Hoe

　　……

　　查询时，以上3列均在where条件中同时出现，所以建立基于以上3列的组合索引。但是发现重复值很多，所以考虑压缩特性。

　　Create index zip_idx

　　on landscp(site, feature, job)

　　compress 2;

　　将索引项分成前缀(prefix)和后缀(postfix)两部分。前两项被放置到前缀部分。

　　Prefix 0: Britten Park, Rose Bed 1

　　Prefix 1: Britten Park, Shrub Bed 1

　　实际所以的结构为：

　　0 Prune

　　0 Mulch

　　0 Spray

　　1 Mulch

　　1 Weed

　　1 Hoe

　　特点：组合索引的前缀部分具有非选择性时，考虑使用压缩。减少I/O,增加性能。

　　九、索引组织表(IOT)

　　将表中的数据按照索引的结构存储在索引中，提高查询速度。

　　牺牲插入更新的性能，换取查询性能。通常用于数据仓库，提供大量的查询，极少的插入修改工作。

　　必须指定主键。插入数据时，会根据主键列进行B树索引排序，写入磁盘。

　　十、分区索引

语法：

Table Index






   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name






ON [schema.]table_name


 [tbl_alias]



(col


 [ASC | DESC]) index_clause





 index_attribs

index_clauses:
分以下两种情况

1. Local Index

   就是索引信息的存放位置依赖于父表的Partition信息，换句话说创建这样的索引必须保证父表是Partition

1.1 索引信息存放在父表的分区所在的表空间。但是仅可以创建在父表为HashTable或者composite分区表的。

    LOCAL STORE IN (tablespace


)

1.2 仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致



LOCAL STORE IN


 (tablespace)



  (PARTITION


 [partition



       [LOGGING|NOLOGGING


]



       [TABLESPACE


 {tablespace|DEFAULT}]



       [PCTFREE


 int]



       [PCTUSED


 int]



       [INITRANS


 int]



       [MAXTRANS


 int]



       [STORAGE


 storage_clause]



       [STORE IN


 {tablespace_name|DEFAULT


]



       [SUBPARTITION


 [subpartition [TABLESPACE


 tablespace]]]])

1.3 索引信息存放在父表的分区所在的表空间，这种语法最简单，也是最常用的分区索引创建方式。

    Local










1.4 并且指定的Partition 数目要与父表的Partition要一致



LOCAL (PARTITION


 [partition






[LOGGING|NOLOGGING


]



[TABLESPACE


 {tablespace


|DEFAULT}]



[PCTFREE


 int


]



[PCTUSED


 int


]



[INITRANS


 int


]



[MAXTRANS


 int


]



[STORAGE


 storage_clause


]



[STORE IN


 {tablespace_name


|DEFAULT


]



[SUBPARTITION


 [subpartition


 [TABLESPACE tablespace


]]]])

Global Index

  索引信息的存放位置与父表的Partition信息完全不相干。甚至父表是不是分区表都无所谓的。语法如下：






GLOBAL PARTITION BY RANGE (col_list)



( PARTITION partition


 VALUES LESS THAN (value_list


)



[LOGGING|NOLOGGING]



[TABLESPACE {tablespace


|DEFAULT}]



[PCTFREE int


]



[PCTUSED int


]



[INITRANS int


]



[MAXTRANS int


]



[STORAGE storage_clause


] )

但是在这种情况下，如果父表是分区表，要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确



ALTER TABLE


 TableName





 DROP PARTITION


 PartitionName


  


Update Global Indexes





分区表、分区索引和全局索引：

　　在一个表的数据超过过2000万条或占用2G空间时，建议建立分区表。

create table ta(c1 int,c2 varchar2(16),c3 varchar2(64),c4 int constraint pk_ta primary key (c1)) partition by range(c1)(partition p1 values less than (10000000),partition p2 values less than (20000000),partition p3 values less than (30000000),partition p4 values less than (maxvalue));

　　分区索引和全局索引：

　　分区索引就是在所有每个区上单独创建索引，它能自动维护，在drop或truncate某个分区时不影响该索引的其他分区索引的使用，也就是索引不会失效，维护起来比较方便，但是在查询性能稍微有点影响。

create index idx_ta_c2 on ta(c2) local (partition p1,partition p2,partition p3,partition p4); 或者 create index idx_ta_c2 on ta(c2) local ;

　　另外在create unique index idx_ta_c2 on ta(c2) local ;系统会报ORA-14039错误，这是因为ta表的分区列是c1，oracle不支持在分区表上创建PK主键时主键列不包含分区列，创建另外的约束 (unique)也不可以。

　　全局索引就是在全表上创建索引，它可以创建自己的分区，可以和分区表的分区不一样，也就是它是独立的索引。在drop或truncate某个分区时需要创建索引alter index idx_xx rebuild，也可以alter table table_name drop partition partition_name update global indexes;实现，但是要花很长时间在重建索引上。可以通过查询user_indexes、user_part_indexes和 user_ind_partitions视图来查看索引是否有效。

create index idx_ta_c3 on ta(c3);

　　或者把全局索引分成多个区(注意和分区表的分区不一样)：

create index idx_ta_c4 on ta(c4) global partition by range(c4)(partition ip1 values less than(10000),partition ip2 values less than(20000),partition ip3 values less than(maxvalue));

　　注意索引上的引导列要和range后列一致，否则会有ORA-14038错误。

　　oracle会对主键自动创建全局索引

　　如果想在主键的列上创建分区索引，除非主键包括分区键，还有就是主键建在两个或以上列上。

　　在频繁删除表的分区且数据更新比较频繁时为了维护方便避免使用全局索引。