举例:
如果只是%abcde ,反向索引肯定是好用的
但存在%abcde%或者abcde%就不好用了
我们经常用的btree索引是将列的值分散到不同的叶子上(叶子上会存在n行的),反向就会安装反向的列值分散到叶子上,比如原来的值是abcde,反向建索引就会用值edcba来把其放到相应的索引树的某个叶子上
最典型的情况用反向索引,是当这列值都非常的接近,然后直接建索引,会造成大量的行(说白了就是rowid)放到一个叶子上,导致索引会慢,还会形成hot block
但你的这种%abcde肯定也是适用的
我们知道Oracle会自动为表的主键列建立索引,这个默认的索引是普通的B-Tree索引。对于主键值是按顺序(递增或递减)加入的情况,默认的B-Tree索引并不理想。这是因为如果索引列的值具有严格顺序时,随着数据行的插入,索引树的层级增长很快。搜索索引发生的I/O读写次数和索引树的层级数成正比,也就是说,一棵具有5个层级的B-Tree索引,在最终读取到索引数据时最多可能发生多达5次I/O操作。因而,减少索引的层级数是索引性能调整的一个重要方法。
如果索引列的数据以严格的有序的方式插入,那么B-Tree索引树将变成一棵不对称的"歪树",如图 5所示:
而如果索引列的数据以随机值的方式插入,我们将得到一棵趋向对称的索引树,如图 6所示
比较图 5和图 6,在图 5中搜索到A块需要进行5次I/O操作,而图 6仅需要3次I/O操作。
既然索引列数据从序列中获取,其有序性无法规避,但在建立索引时,Oracle允许对索引列的值进行反向,即预先对列值进行比特位的反向,如1000,10001,10011,10111,1100经过反向后的值将是0001,1001,1101,0011。显然经过位反向处理的有序数据变得比较随机了,这样所得到的索引树就比较对称,从而提高表的查询性能。
但反向键索引也有它局限性:如果在WHERE语句中,需要对索引列的值进行范围性的搜索,如BETWEEN、<、>等,其反向键索引无法使用,此时,Oracle将执行全表扫描;只有对反向键索引列进行 <>和 = 的比较操作时,其反向键索引才会得到使用。
----------------------------------------------------------------------
1、将表数据和索引数据分开表空间存储
1.1 表数据和索引为何需要使用独立的表空间
Oracle强烈建立,任何一个应用程序的库表至少需要创建两个表空间,其中之一用于存储表数据,而另一个用于存储表索引数据。因为如果将表数据和索引数据放在一起,表数据的I/O操作和索引的I/O操作将产生影响系统性能的I/O竞争,降低系统的响应效率。将表数据和索引数据存放在不同的表空间中(如一个为APP_DATA,另一个为APP_IDX),并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上,就可以避免这种竞争了。
拥有独立的表空间,就意味着可以独立地为表数据和索引数据提供独立的物理存储参数,而不会发生相互影响,毕竟表数据和索引数据拥有不同的特性,而这些特性又直接影响了物理存储参数的设定。
此外,表数据和索引数据独立存储,还会带来数据管理和维护上的方面。如你在迁移一个业务数据库时,为了降低数据大小,可以只迁出表数据的表空间,在目标数据库中通过重建索引的方式就可以生成索引数据了。
1.2 表数据和索引使用不同表空间的SQL语法
指定表数据及索引数据存储表空间语句最简单的形式如下。
将表数据存储在APP_DATA表空间里:
create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace APP_DATA;
将索引数据存储在APP_IDX表空间里:
create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC)tablespace APP_IDX;
----------------------------------------------------------------------
1. CREATE INDEX命令语法:
CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced
其中:
schema ORACLE模式,缺省即为当前帐户
index 索引名
table 创建索引的基表名
column 基表中的列名,一个索引最多有16列,long列、long raw
列不能建索引列
DESC、ASC 缺省为ASC即升序排序
CLUSTER 指定一个聚簇(Hash cluster不能建索引)
INITRANS、MAXTRANS 指定初始和最大事务入口数
Tablespace 表空间名
STORAGE 存储参数,同create table 中的storage.
PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)
NOSORT 不(能)排序(存储时就已按升序,所以指出不再排序)
与普通的索引不一样,索引组织表(Index_Organized Table)是根据表来存储数据,即将索引和表存储在一起。这样的索引结构表(Index_organized table—IOT)的特点是:对表数据的改变,如插入一新行、删除某行都引起索引的更新。
索引组织表就象带一个或多个列所有的普通表一样,但索引组织表在B-树索引结构的叶节点上存储行数据。通过在索引结构中存储数据,索引组织表减少了总的存储量,此外,索引组织表也改善访问性能。
由于表中的行与B_树索引存放在一起,每个行都没有ROWID,而是用主键来标识。但是Oracle会“猜”这些行的位置并为每个行分配逻辑的ROWID。此外,你可以为这样的表建立第二个索引。
创建索引结构表也是用CREATE TABLE 命令加ORGANIZATION INDEX关键字来实现。但是,这样的表在创建完后,你还必须为该表建立一个主键。
例子:
CREATE TABLE IOT_EXPAMPLE
(
Pk_col1 number(4),
Pk_col2 varchar2(10),
Non_pk_col1 varchar2(40),
Non_pk_col2 date,
CONSTRAINT pk_iot PRIMARY KEY
( pk_col1, pk_col2)
)
ORGANIZATION INDEX
TABLESPACE INDEX
STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );
索引组织表有些限制:
l 不能使用唯一约束;
l 必须具有一个主键;
l 不能建立簇;
l 不能包含LONG类型列;
l 不支持分布和复制。
提示:如果建立了索引组织表,则会在DBA_TABLES中的IOT_TYPE和IOT_NAME列上记录有索引组织表的信息。
分享到:
相关推荐
数据库索引设计与优化是数据库管理系统中至关重要的一个环节,它直接影响到数据查询的效率、存储空间的使用以及系统的整体性能。在这个主题中,我们将深入探讨数据库索引的基础概念、设计原则、优化策略以及实际应用...
《数据库索引设计与优化》提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地...
数据库索引重建及修复语句
数据库索引设计原则 数据库索引设计原则是 Oracle 数据库管理系统中的一项重要技术,旨在提高数据库的查询效率和性能。以下是数据库索引设计原则的详细解释。 一、基本原则 数据库索引设计原则的基本原则是确保...
高清完整版 数据库索引设计与优化 高清完整版 数据库索引设计与优化
《数据库索引设计与优化》提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地...
数据库索引技术是数据库管理系统中的核心组成部分,它极大地提高了数据查询效率,使得在海量数据中查找特定信息变得迅速。下面将分别对标题和描述中提到的各个知识点进行详细阐述。 首先,我们来看“文件记录的组织...
### 数据库索引及优化详解 #### 一、数据库索引的重要性 数据库索引就像是图书中的目录,能够显著提升查询速度。例如,在执行查询 `SELECT * FROM table1 WHERE id = 44` 时,如果没有索引,系统需要逐行扫描整个...
数据库索引是数据库管理系统中用于加速数据检索的一种数据结构,它的设计目的是为了提高查询效率,减少数据访问的时间。本文将深入探讨数据库索引的概念、B-Tree数据结构以及索引的分类和作用。 首先,B-Tree是...
【数据库索引】是数据库管理系统中用于加速数据检索的一种数据结构。索引好比书籍的目录,能够快速定位到所需的数据行。根据物理存储方式,索引分为【聚簇索引】和【非聚簇索引】。聚簇索引是按照数据的实际存储顺序...
《Oracle与MySQL数据库索引设计与优化》这本书深入探讨了两个主流关系型数据库管理系统——Oracle和MySQL中的索引设计和优化策略。索引是数据库性能的关键因素,它们能够加速数据检索,提高系统效率,尤其在大数据量...
数据库索引作为数据库管理系统(DBMS)中的关键技术之一,其作用在于优化数据的检索速度和确保数据的唯一性。本文将深入探讨数据库索引的优缺点,以及如何在实际操作中合理地运用索引来提升数据库性能。 ### 数据库...
### 空间数据库索引技术的深度剖析 #### 核心知识点提炼: - **空间数据库索引技术的重要性**:空间数据库索引技术是提升空间数据库存储效率与空间检索性能的关键,尤其在处理大规模空间数据时更为显著。传统索引...
### Oracle数据库索引的维护 在Oracle数据库管理与优化的过程中,索引的维护是非常关键的一环。合理地创建、管理和优化索引能够显著提高查询性能,降低系统的响应时间,从而提升整个应用程序的效率。本文将从Oracle...