Oracle的“索引”对象，与表关联的可选对象，提高SQL查询语句的速度;
索引直接指向包含所查询值的行的位置，减少磁盘I/O;
与所索引的表是相互独立的物理结构;
Oracle自动使用并维护索引，插入、删除、更新表后，自动更新索引;
语法：CREATE INDEX index ON table (column[, column]…);
B-tree结构(非 bitmap)
1、单表索引
索引创建：
create [unique | bitmap] index index_name on table_name(col_name) pctfree 20
storage(initial 100k next 100k pctincrease
tablespace_name;
0 maxextents 100) tablespace
索引重建：
重建索引(可以同时使用存储子句和参数,不重建时也可直接使用)
alter index index_name rebuild tablespace tablespace_name nologging parallel 4;
alter index index_name noparallel;
在线重建索引.可以减少加锁时间,从而开放使用 DML类型操作
alter index index_name rebuild tablespace tablespace_name nologging online;
手动拓展索引的空间
alter index index_name allocate extent;
收回未用到的空间
alter index index_name deallocate unused;
索引碎片整理
alter index index_name coalesce;
标识索引是否使用过
alter index index_name monitoring usage;
查询:
select * from v$object_usage;
取消监控
alter index index_name nomonitoring usage
索引压缩：
alter index index_name rebuild nologging online tablespace tablespace_name compress;
索引删除：
drop index index_name
索引查看：
索引相关信息
select owner,index_name,table_name,tablespace_name,index_type,degree,status
from dba_indexes;
索引列对照信息
select index_name,table_name,column_name,index_owner,table_owner
from dba_ind_columns;
索引存储信息
select index_name,pct_free,pct_increase,initial_extent,next_extent,min_extents,
max_extents from dba_indexes;
2、分区表索引
索引创建：
局部索引
create index index_name on table_name (column)
local
(
partition partition_name1 tablespace index_tablespace_name1,
partition partition_name2 tablespace index_tablespace_name2,
partition partition_name3 tablespace index_tablespace_name3
)
全局索引
create [unique] index index_name on table_name(column)
global partition by range(column)
(
partition partition_name1 value less than(first range value) tablespace
index_tablespace_name1,
partition partition_name2 value less than(second range value) tablespace
index_tablespace_name 2,
、、、、、、、
partition partition_nameN value less than(maxvalue) tablespace index_tablespace_nameN
)
create [unique] index index_name on table_name(column,[column2])
global partition by hash(column,[column2])
(
partition partition_name1 tablespace index_tablespace_name1,
partition partition_name2 tablespace index_tablespace_name 2,
、、、、、、、、
partition partition_nameN tablespace index_tablespace_nameN
)
索引重建：
alter index index_name rebuild tablespace tablespace_name nologging online parallel 4;
alter index index_name noparallel;
索引删除：
drop index index_name;
索引查看：
索引相关信息
select owner,index_name,table_name,tablespace_name,index_type,degree,status from
dba_indexes;
索引列对照信息
select index_name,table_name,column_name,index_owner,table_owner from
dba_ind_columns;
索引存储信息
select index_name,pct_free,pct_increase,initial_extent,next_extent,min_extents,max_extents
from dba_indexes;
3、主键、约束和唯一索引
唯一索引
唯一索引不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都
不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此
数据。例如，如果在 books_table表中的书名 (book_name)列上,创建了唯一索引，则所有书不能
同名。
主键索引
主键索引是唯一索引的特殊类型,数据库表通常有一列或列组合，其值用来唯一标识表中的每一
行。该列称为表的主键。在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯
一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允
许快速访问数据。
主键和唯一索引的一些比较：
(1)对于主健 unique/constraint oracle自动建立唯一索引
(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的
(3)主健可作外健，唯一索引不可，
(4)主健不可为空，唯一索引可，
(5)主健也可是多个字段的组合.
(6)主键不同的是
a.有 not null属性 b.每个表只能有一个
约束和唯一索引比较：
主键索引和主键约束的创建与删除顺序
创建主键索引 –>创建主键约束删除主键约束 –>删除主键索引
A分区字段不是主键的情况下,只可以创建全局分区索引,不可以创建本地主键分区索引.只有分
区字段为主键时才可以创建本地主键分区索引.
B如果创建本地唯一分区索引,除指定索引字段外还要加上表分区字段.这种索引意义不大:因为
这样
成复合索引,索引改变,约束也改变了.
C如果创建非唯一索引则不需要表分区字段.
D创建全局分区索引后可以创建约束.
唯一索引创建：
create unique index index_name on table_name (column)
local
(
partition partition_name1 tablespace index_tablespace1,
partition partition_name2 tablespace index_tablespace2,
、、、、、、
partition partition_nameN tablespace index_tablespaceN
)
单表创建唯一索引：create unique index index_name on table_name (column);
唯一索引重建：
alter index index_name rebuild tablespace tablespace_name nologging parallel 8 online
alter index index_name noparallel;
唯一索引删除：
如果有约束：先关闭约束 alter table table_name1 disable constraint constraint_name cascade;
再 drop索引：drop index index_name
索引查看：
DBA查看索引分区
select * from dba_ind_partitions
USER查看索引分区
select * from user_ind_partitions
DBA查看索引分区类型
select * from dba_part_indexes
USER查看索引分区类型
select * from user_part_indexes
约束创建：
在数据库中使用约束(constraints)是为了在该数据库中实施所谓的“业务规则“其实就是防止非法
信息进入数据库,满足管理员和应用开发人员所定义的规则集.
ORACLE使用完整性约束(integrity constraints)防止不合法的数据写入数据库,管理员和开发人
员可以定义完整性规则,增强商业规则,限制数据表中的数据.如果一个 DML语句执行的任何结果破
坏了完整性约束,ORACLE就会回滚语句,返回错误信息.
约束是通过使用 create table或 alter table语句生成的.(建立表时或者表建立后修改都可)如果相关
的约束定义在单列上,可以在列这一级指定约束的定义;多列约束必须定义在数据表级,相关的列要
在括号中指定,用逗号分隔.如果没有为约束提供一个名字,那么 ORACLE会分配一个系统生成的唯
一名字,以 SYS_开头,你可以使用关键字 CONSTRAINTS后面跟随相关的约束名字来为约束指定名
字.
ORACLE支持五种类型的完整性约束
NOT NULL (非空)–防止 NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许
在任何列中有 NULL值.
CHECK (检查)–检查在约束中指定的条件是否得到了满足.
UNIQUE (唯一)–保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的.
PRIMARY KEY (主键)–用来唯一的标识出表的每一行,并且防止出现 NULL值,一个表只能有一
个主键约束.
POREIGN KEY (外部键)–通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定
义的外部键可以指向主键或者其他表的唯一键.
约束定义存储在数据字典中,查询 user_constraints可以获得相关信息.
定义约束
create table [schema.]table
(column datatype [default expr]
[column_constraint],
…
[table_constraint][,…]);
创建约束：
create table employees
(employee_id number(6),
first_name varchar2(20),
…
job_id varchar2(10) not null,
constraints emp_emp_id_pk primary key (employee_id));
列级的约束定义
column [CONSTRAINT constraint_name] constraint_type,
表级约束的定义
column,..
[constraint constraint_name] constraint_type (column,…)
NOT NULL约束
只能定义在列级,不能定义在表级：
create table table_name1
(row_id number(6),
row_time varchar2(25) not null,
row_salary number(8,2),
row_pct number(2,2),
row_date date constraint constraint_name1 not null);
UNIQUE约束
用来保护一个表中的一个或者多个列没有任何两行在收到保护的列中具有重复的数据.ORACLE在
唯一键列上自动生成一个唯一索引以实现唯一性：
create table table_name1
(row_id number(6),
row_time varchar2(25) not null,
row_salary number(8,2),
row_pct number(2,2),
row_date date constraint constraint_name1 UNIQUE(row_id));
PRIMARY KEY约束
唯一键的所有特征都适用于主键约束,只是在主键列中不允许有 NULL值.一个表只能有一个主键：
create table table_name1
(row_id number(6),
row_time varchar2(25) not null,
row_salary number(8,2),
row_pct number(2,2),
row_date date constraint constraint_name1 PRIMARY KEY(row_id));
foreign key约束
用来保护一个表中的一个或者多个列,它会通过一个主键主键或者唯一键保证对于每个非 NULL
值在数据库的其他地方都有一个数据可用.这个外部键就是在生成此约束的表(子表)中的一个或多
个列,在父级表和子表中,相关列的数据类型必须匹配.外部键列和引用键(reference key)列可以位于
相同的表中(自引用完整性约束).
create table table_name1
(row_id number(6),
row_time varchar2(25) not null,
row_salary number(8,2),
row_pct number(2,2),
row_date date not null,
dep_id number(6),
constraint constraint_name1 foreign key(row_id) references table_name2(dep_id),
constraint constraint_name2 unique(dep_id));
上例中是在表级定义外部键约束,如果在列级定义,不同的是:
create table table_name1
(…,
dep_id number(4) constraint constraint_name1 references table_name2(dep_id),
…);
//没有关键字 FOREIGN KEY
FOREIGN KEY约束还有两个关键字是
ON DELETE CASCADE —当删除所引用的父表记录时,删除子表中相关的记录
ON DELETE SET NULL–与上面不同的是删除时,转换子表中相关记录为 NULL值
默认情况下,如果没有指定以上两个中任一,则父表中被引用的记录将不能被删除.
CHECK约束
[CONSTRAINT <constraint name>] CHECK (<condition> )
这里 CHECK子句中的 CONDITION应该求值为一个布尔值结果,并且可以引用相同行中其他列的
值;不
能包含子查询,序列,环境函数(SYSDATE,UID,USER,USERENV)和伪列
(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一个列上可以定义多个 CHECK约束,如果所定义的条
件为
FALSE,则语句将回滚.
CREATE TABLE table_name1
(…,
row_sal NUMBER(8,2) CONSTRAINT constraint_name1 CHECK (row_sal>0),
…);
添加约束
alter table table_name1
ADD CONSTRAINT constraint_name FOREIGN KEY(rowmagr_id) REFERENCES
table_name2(name_id);
删除约束
alter table table_name1
drop constraint constraint_name;
alter table table_name1
drop primary key cascade;
对于 NOT NULL约束,用 ALTER TABLE MODIFY子句来删除
alter table table_name1 modify row_name null;
关闭约束
alter table table_name1
disable constraint constraint_name cascade; //如果没有被引用则不需 CASCADE关键字
当你生成一个约束时,约束自动打开(除非你指定了 DISABLE子句,当用 DISABLE关闭 UNIQUE或
者 PRIMARY KEY约束时,ORACLE会自动删除相关的唯一索引,再次打开时,ORACLE又会自动
建立.
打开约束
alter table table_name enable constraint constraint_name;
//注意,打开一个先前关闭的被引用的主键约束,并不能自动打开相关的外部键约束
约束信息查看：
可以从 USER_CONSTRAINTS表和 USER_CONS_COLUMNS视图中查询约束的信息
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name=’table_name1′;
约束类型
C–CHECK和 NOT NULL都算为 C TYPE
P–PRIMARY KEY
R–REFERENTIAL INTEGRITY就是外部键约束
U–UNIQUE
select constraint_name,column_name
from user_cons_columns
where table_name=’table_name1′;
