Oracle数据库中的索引详解

一 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

dbms_stats能良好地估计统计数据（尤其是针对较大的分区表） 并能获得更好的统计结果 最终制定出速度更快的SQL执行计划

exec dbms_stats gather_schema_stats(

ownname          => SCOTT

options          => GATHER AUTO

estimate_percent => dbms_stats auto_sample_size

method_opt       => for all columns size repeat

degree           =>

)       为了充分认识dbms_stats的好处 需要仔细体会每一条主要的预编译指令（directive） 下面让我们研究每一条指令 并体会如何用它为基于代价的SQL优化器收集最高质量的统计数据

options参数

使用 个预设的方法之一 这个选项能控制Oracle统计的刷新方式

gather——重新分析整个架构（Schema）

gather empty——只分析目前还没有统计的表

gather stale——只重新分析修改量超过 %的表（这些修改包括插入 更新和删除）

gather auto——重新分析当前没有统计的对象 以及统计数据过期（变脏）的对象 注意 使用gather auto类似于组合使用gather stale和gather empty

注意 无论gather stale还是gather auto 都要求进行监视 如果你执行一个alter table xxx monitoring命令 Oracle会用dba_tab_modifications视图来跟踪发生变动的表 这样一来 你就确切地知道 自从上一次分析统计数据以来 发生了多少次插入 更新和删除 *** 作

estimate_percent选项

estimate_percent参数是一种比较新的设计 它允许Oracle的dbms_stats在收集统计数据时 自动估计要采样的一个segment的最佳百分比

estimate_percent => dbms_stats auto_sample_size

要验证自动统计采样的准确性 你可检视dba_tables sample_size列 一个有趣的地方是 在使用自动采样时 Oracle会为一个样本尺寸选择 到 的百分比 记住 统计数据质量越好 CBO做出的决定越好

method_opt选项

method_opt for table 只统计表

for all indexed columns 只统计有索引的表列

for all indexes 只分析统计相关索引

for all columns

dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据 method_opt参数也适合用于判断哪些列需要直方图（histograms）

某些情况下 索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策 例如 假如在where子句中指定的值的数量不对称 全表扫描就显得比索引访问更经济

如果你有一个高度倾斜的索引（某些值的行数不对称） 就可创建Oracle直方图统计 但在现实世界中 出现这种情况的机率相当小 使用CBO时 最常见的错误之一就是在CBO统计中不必要地引入直方图 根据经验 只有在列值要求必须修改执行计划时 才应使用直方图

为了智能地生成直方图 Oracle为dbms_stats准备了method_opt参数 在method_opt子句中 还有一些重要的新选项 包括skewonly repeat和auto

method_opt=> for all columns size skewonly

method_opt=> for all columns size repeat

method_opt=> for all columns size auto

skewonly选项会耗费大量处理时间 因为它要检查每个索引中的每个列的值的分布情况

假如dbms_stat发现一个索引的各个列分布得不均匀 就会为那个索引创建直方图 帮助基于代价的SQL优化器决定是进行索引访问 还是进行全表扫描访问 例如 在一个索引中 假定有一个列在 %的行中 如清单B所示 那么为了检索这些行 全表扫描的速度会快于索引扫描

SKEWONLY option—Detailed ysis

Use this method for a first time ysis for skewed indexes

This runs a long time because all indexes are examined

begin

dbms_stats gather_schema_stats(

ownname          => SCOTT

estimate_percent => dbms_stats auto_sample_size

method_opt       => for all columns size skewonly

degree           =>

);

end;

重新分析统计数据时 使用repeat选项 重新分析任务所消耗的资源就会少一些 使用repeat选项（清单C）时 只会为现有的直方图重新分析索引 不再搜索其他直方图机会 定期重新分析统计数据时 你应该采取这种方式

REPEAT OPTION Only re yze histograms for indexes

that have histograms

Following the initial ysis the weekly ysis

job will use the repeat option The repeat option

tells dbms_stats that no indexes have changed and

it will only re yze histograms for

indexes that have histograms

begin

dbms_stats gather_schema_stats(

ownname          => SCOTT

estimate_percent => dbms_stats auto_sample_size

method_opt       => for all columns size repeat

degree           =>

);

end;

使用alter table xxx monitoring;命令来实现Oracle表监视时 需要使用dbms_stats中的auto选项 如清单D所示 auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直方图 使用method_opt=> auto 类似于在dbms_stats的option参数中使用gather auto

begin

dbms_stats gather_schema_stats(

ownname          => SCOTT

estimate_percent => dbms_stats auto_sample_size

method_opt       => for all columns size auto

degree           =>

);

end;

并行统计收集degree参数

Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS AUTO_DEGREE 该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度

聚簇索引 域索引 位图连接索引不能并行收集

如何使用dbms_stats分析统计信息？

创建统计信息历史保留表

sql> exec dbms_stats create_stat_table(ownname => scott stattab => stat_table ) ;

导出整个scheme的统计信息

sql> exec dbms_stats export_schema_stats(ownname => scott stattab => stat_table ) ;

分析scheme

Exec dbms_stats gather_schema_stats(

ownname => scott

options => GATHER AUTO

estimate_percent => dbms_stats auto_sample_size

method_opt => for all indexed columns

degree => )

分析表

sql> exec dbms_stats gather_table_stats(ownname => scott tabname => work_list estimate_percent => method_opt=> for all indexed columns ) ;

分析索引

SQL> exec dbms_stats gather_index_stats(ownname => crm indname => IDX_ADM_PERMISSION_PID_MID estimate_percent => degree => ) ;

如果发现执行计划走错 删除表的统计信息

SQL>dbms_stats delete_table_stats(ownname => scott tabname => work_list ) ;

导入表的历史统计信息

sql> exec dbms_stats import_table_stats(ownname => scott tabname => work_list stattab => stat_table ) ;

如果进行分析后 大部分表的执行计划都走错 需要导回整个scheme的统计信息

sql> exec dbms_stats import_schema_stats(ownname => scott stattab => stat_table );

导入索引的统计信息

SQL> exec dbms_stats import_index_stats(ownname => crm indname => IDX_ADM_PERMISSION_PID_MID stattab => stat_table )

检查是否导入成功

SQL> select table_name num_rows a blocks a last_ yzed from all_tables a where a table_name= WORK_LIST ;

分析数据库（包括所有的用户对象和系统对象） gather_database_stats

分析用户所有的对象（包括表 索引 簇） gather_schema_stats

分析表 gather_table_stats

分析索引 gather_index_stats

删除数据库统计信息 delete_database_stats

删除用户方案统计信息 delete_schema_stats

删除表统计信息 delete_table_stats

删除索引统计信息 delete_index_stats

删除列统计信息 delete_column_stats

设置表统计信息 set_table_stats

设置索引统计信息 set_index_stats

设置列统计信息 set_column_stats

从Oracle Database g开始 Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务 用于自动收集CBO的统计信息

这个自动任务默认情况下在工作日晚上 和周末全天开启 调用DBMS_STATS GATHER_DATABASE_STATS_JOB_PROC收集统计信息

该过程首先检测统计信息缺失和陈旧的对象 然后确定优先级 再开始进行统计信息

可以通过以下查询这个JOB的运行情况

select from Dba_Scheduler_Jobs where JOB_NAME = GATHER_STATS_JOB

其实同在 点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB

SQL> select JOB_NAME LAST_START_DATE from dba_scheduler_jobs;

JOB_NAME                       LAST_START_DATE

AUTO_SPACE_ADVISOR_JOB        DEC PM + :

GATHER_STATS_JOB              DEC PM + :

FGR$AUTOPURGE_JOB

PURGE_LOG                      DEC AM PRC

然而这个自动化功能已经影响了很多系统的正常运行 晚上 点对于大部分生产系统也并非空闲时段

而自动分析可能导致极为严重的闩锁竞争 进而可能导致数据库Hang或者Crash

所以建议最好关闭这个自动统计信息收集功能

方法之一:

exec dbms_scheduler disable( SYS GATHER_STATS_JOB );

恢复自动分析:

exec dbms_scheduler enable( SYS GATHER_STATS_JOB );

方法二

alter system set _optimizer_autostats_job =false scope=spfile;

alter system set _optimizer_autostats_job =true scope=spfile;

lishixinzhi/Article/program/Oracle/201311/18864

ALTER
TABLE
DROP
PARTITION
用于删除table中某个PARTITION和其中的数据，主要是用于历史数据的删除。如果还想保留数据，就需要合并到另一个partition中。
删除该partition之后，如果再insert该partition范围内的值，要存放在更高的partition中。如果你删除了最大的partition，就会出错。
删除table
partition的同时，删除相应的local
index。即使该index是IU状态。
如果table上有global
index，且该partition不空，drop
partition会使所有的global
index
为IU状态。如果不想REBUIL
INDEX，可以用SQL语句手工删除数据，然后再DROP
PARTITION
例子：
ALTR
ATBEL
sales
DROP
PARTITION
dec96;
到底是DROP
PARTITION或者是DELETE？
如果GLOBAL
INDEX是最重要的，就应该先DELETE
数据再DROP
PARTITION。
在下面情况下，手工删除数据的代价比DROP
PARTITION要小
-
如果要删除的数据只占整个TABLE的小部分
-
在TABLE中有很多的GLOBAL
INDEX。
在下面情况下，手工删除数据的代价比DROP
PARTITION要大
-
如果要删除的数据占整个TABLE的绝大部分
-
在TABLE中没有很多的GLOBAL
INDEX。
如果在TABLE是父TABLE，有被引用的约束，且PARTITION不空，DROP
PARTITION时出错。
如果要删除有数据的PARTITION，应该先删除引用约束。或者先DELETE,然后再DROP
PARTITION。
如果TABLE只有一个PARTITON,不能DROP
PARTITION，只能DROP
TABLE。

1 应该是可行的， 具体 会不会节省时间 试一下就可以了。

2 大概每个月存储四五十万的数据，里面只保存最新四个月的数据

每次create这7个索引用时都特别长，大概需要三四个小时；

200万的数据，重建索引花费的时间太长了；很奇怪。

3 估计之前的 先drop掉索引，然后插入数据完毕后create索引 也是为了避免 插入数据时，索引对插入效率的影响。

表创建标准语法:
CREATE TABLE [schema]table
(column datatype [DEFAULT expr] , …);
--设计要求：建立一张用来存储学生信息的表，表中的字段包含了学生的学号、姓名、年龄、入学日期、年级、班级、email等信息，
--并且为grade指定了默认值为1，如果在插入数据时不指定grade得值，就代表是一年级的学生
--DML是不需要commit的，隐式事务
create table student
(
stu_id number(10),
name varchar2(20),
age number(2),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50)
);

-- 注意日期格式要转换，不能是字符串，varchar2类型要用引号，否则出现类型匹配
--DML 需要收到commit
insert into student values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'2','1',' 123@qqcom ');
insert into student(stu_id,name,age,hiredate,classes,email) values(20211114,'zhangsan',22,to_date('2021-11-14','YYYY-MM-DD'),'1',' 1234@qqcom ');

select from student;

-- 给表添加列，添加新列时不允许为not null,因为与旧值不兼容
alter table student add address varchar(100);

-- 删除列
alter table student drop column address;

--修改列
alter table student modify(email varchar2(100));
正规表设计使用power disinger

--表的重命名
rename student to stu;
-- 表删除
drop table stu;

在删除表的时候，经常会遇到多个表关联的情况(外键)，多个表关联的时候不能随意删除，使用如下三种方式:

2表的约束（constraint）
约束:创建表时，指定的插入数据的一些规则
约束是在表上强制执行的数据校验规则
Oracle 支持下面五类完整性约束:
1) NOT NULL 非空约束 ---- 插入数据时列值不能空
2) UNIQUE Key 唯一键约束 ----限定列唯一标识，唯一键的列一般被用作索引
3) PRIMARY KEY 主键约束 ----唯一且非空，一张表最好有主键，唯一标识一行记录
4) FOREIGN KEY 外键约束---多个表间的关联关系，一个表中的列值，依赖另一张表某主键或者唯一键
-- 插入部门编号为50的，部门表并没有编号为50的，报错
insert into emp(empno,ename,deptno) values(9999,'hehe',50);
5) CHECK 自定义检查约束---根据用户需求去限定某些列的值，使用check约束
-- 添加主键约束/not null约束/check约束/唯一键约束
create table student
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age>0 and age<126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
);

-- 添加外键约束
create table stu
(
stu_id number(10) primary key,
name varchar2(20) not null,
age number(3) check(age>0 and age<126),
hiredate date,
grade varchar2(10) default 1,
classes varchar2(10),
email varchar2(50) unique,
deptno number(2),
FOREIGN KEY(deptno) references dept(deptno)
);
-- 创建表时没添加外键约束 也可以修改 其中fk_0001为外键名称
alter table student add constraint fk_0001 foreign key(deptno) references dept(deptno);

索引创建有两种方式:

组合索引：多个列组成的索引
--索引：加快数据剪碎
create index i_ename on emp(ename);

--当创建某个字段索引后，查询某个字段会自动使用到索引
select from emp where ename = 'SMITH';
--删除索引 索引名称也是唯一的
drop index i_ename;

一些概念：
回表：
覆盖索引
组合索引
最左匹配

可以的 根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。有关数据库所支持的索引功能的详细信息，请参见数据库文档。 提示：尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。

---