Oracle创建索引SQL简单的例子，在表中的指定字段和如何使用索引呢？

create index index_name on table_name(column_name) \x0d\x0a只要你查询使用到建了索引的字段，一般都会用到索引。 \x0d\x0a \x0d\x0a--创建表\x0d\x0acreate table aaa\x0d\x0a(\x0d\x0a a number,\x0d\x0a b number\x0d\x0a)\x0d\x0a--创建索引\x0d\x0acreate index idx_a on aaa (a)\x0d\x0a--使用索引\x0d\x0aselect * from aaa where a=1\x0d\x0a这句查询就会使用索引 idx_a

当where子句对某一列使用函数时 除非利用这个简单的技术强制索引 否则Oracle优化器不能在查询中使用索引

通常情况下 如果在WHERE子句中不使用诸如UPPER REPLACE 或SUBSTRD等函数 就不能对指定列建立特定的条件 但如果使用了这些函数 则会出现一个问题 这些函数会阻碍Oracle优化器对列使用索引 因而与采用索引的情况相比较 查询会花费更多的时间

庆幸的是 如果在使用函数的这些列中包含了字符型数据 可以用这样一种方法修改查询语句 以达到强制性使用索引 更有效地运行查询 这篇文章介绍了涉及的技术 并说明了在两种典型情况下怎样实现

大小写混合情况

在讨论由于函数修改了列的内容 如何强制使用索引前 让我们首先看看为什么Oracle优化器在这种情况下不能使用索引 假定我们要搜寻包含了大小写混合的数据 如在表 中ADDRESS表的NAME列 因为数据是用户输入的 我们无法使用已经统一改为大写的数据 为了找到每一个名为john的地址 我们使用包含了UPPER子句的查询语句 如下所示

SQL>select address from address where upper(name) like JOHN

在运行这个查询语句前 如果我们运行了命令 set autotrace on 将会得到下列结果 其中包含了执行过程

ADDRESS cleveland row selected Execution Plan SELECT STATEMENT TABLE ACCESS FULL ADDRESS

可以看到 在这种情况下 Oracle优化器对ADDRESS 表作了一次完整的扫描 而没有使用NAME 列的索引 这是因为索引是根据列中数据的实际值建立的 而UPPER 函数已经将字符转换成大写 即修改了这些值 因此该查询不能使用这列的索引 优化器不能与索引项比较 JOHN 没有索引项对应于 JOHN 只有 john

值得庆幸的是 如果在这种情况下想要强制使用索引 有一种简便的方法 只要在WHERE 子句中增加一个或多个特定的条件 用于测试索引值 并减少需要扫描的行 但这并没有修改原来SOL 编码中的条件 以下列查询语句为例

SQL>select address from address where upper(name) like JO% AND (name like J% or name like j% )

使用这种查询语句（已设置AUTOTRACE） 可得到下列结果

ADDRESS cleveland row selected Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I

现在 优化器为WHERE 子句中AND 联结的两个语句中每一个语句确定的范围进行扫描 第二个语句没有引用函数 因而使用了索引 在两个范围扫描后 将运行结果合并

在这个例子中 如果数据库有成百上千行 可以用下列方法扩充WHERE 子句 进一步缩小扫描范围

select address from address where upper(name) like JOHN AND (name like JO% or name like jo% or name like Jo or name like jO )

得到的结果与以前相同 但是 其执行过程如下所示 表明有 个扫描范围

Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I

如果试图进一步提高查询速度 我们可以在特定的 name like 条件中指明 个或更多的字符 然而 这样做会使得WHERE子句十分笨重 因为需要大小写字符所有可能的组合 joh Joh jOh joH等等 除此之外 指定一个或两个字符已足以加快查询的运行速度了

现在让我们看看 当我们引用不同的函数时 怎样运用这个基本技术

使用REPLACE的情况

正如名字不总是以大写输入一样 电话号码也会以许多格式出现 如 （ ） 等等

如果在列名为 PHONE_NUMBER中搜寻上述号码时 可能需要使用函数REPLACE以保证统一的格式 如果在PHONE_NUMBER列中只包含空格 连字符和数字 where 子句可以如下所示

WHERE replace(replace(phone_number ) ) =

WHERE子句两次使用REPLACE 函数去掉了连字符和空格 保证了电话号码是简单的数字串 然而 该函数阻止了优化器在该列使用索引 因此 我们按如下方法修改WHERE子句 以强制执行索引

WHERE replace(replace(phone_number ) ) = AND phone_number like %

如果我们知道数据中可能包含圆括号 WHERE 子句会稍微复杂一点 我们可以再增加REPLACE 函数（去掉圆括号 连字符和空格） 按如下所示扩充增加的条件

WHERE replace(replace(replace(replace(phone_number ) ) ( ) ) ) = AND (phone number like % or phone_number like ( % )

该例强调了巧妙地选用WHERE 子句条件的重要性 而且 这些条件不会改变查询结果 你的选择应基于完全了解该列中存在的信息类型 在该例中 我们需要知道 PHONE_NUMBER 数据中存在几种不同的格式 这样 我们能够修改WHERE 子句而不会影响查询结果

正确的条件 lishixinzhi/Article/program/Oracle/201311/18519

一 ROWID的概念

存储了row在数据文件中的具 *** 置 位编码的数据 A Z a z + 和 /

row在数据块中的存储方式

SELECT ROWID last_name FROM hr employees WHERE department_id =

比如 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

具体到特定的物理文件

二 索引的概念

类似书的目录结构

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

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

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

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

语法 CREATE INDEX index ON table (column[ column] )

B tree结构(非bitmap)

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

表 emp

目标 查询Frank的工资salary

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

　[试验]测试索引的作用

运行/rdbms/admin/utlxplan 脚本

建立测试表

create table t as select * from dba_objects

insert into t select * from t

create table indextable

as select rownum id owner object_name subobject_name

object_id data_object_id object_type created

from t

set autotrace trace explain

set timing on

分析表 可以得到cost

查询 object_name= DBA_INDEXES

在object_name列上建立索引

再查询

[思考]索引的代价

插入 更新

　三 唯一索引

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

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

语法 CREATE UNIQUE INDEX index ON table (column)

演示

四 组合索引

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

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

演示(组合列 单独列)

　五 位图索引

何时创建

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

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

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

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

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

语法 CREATE BITMAP INDEX index ON table (column[ column] )

掩饰

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

分析 查找 建立索引 查找

　六 基于函数的索引

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

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

语法 CREATE INDEX index ON table (FUNCTION(column))

演示

必须要分析表 并且query_rewrite_enabled=TRUE

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

七 反向键索引

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

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

性能问题

语法

重建为标准索引 反之不行

　八 键压缩索引

比如表landscp的数据如下

site feature job

Britten Park Rose Bed Prune

Britten Park Rose Bed Mulch

Britten Park Rose Bed Spray

Britten Park Shrub Bed Mulch

Britten Park Shrub Bed Weed

Britten Park Shrub Bed Hoe

……

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

Create index zip_idx

on landscp(site feature job)

press

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

Prefix : Britten Park Rose Bed

Prefix : Britten Park Shrub Bed

实际所以的结构为

Prune

Mulch

Spray

Mulch

Weed

Hoe

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

九 索引组织表(IOT)

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

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

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

　十 分区索引

簇:

A cluster is a group of tables that share the same data blocks because they share mon columns and are often used together

lishixinzhi/Article/program/Oracle/201311/17769

---