oracle如何重建域索引,Oracle索引规划设计

管理索引的准则：

l在表中插入数据后创建索引

l在正确的表和列上创建索引

l为性能而排序索引列

l限制每个表的索引数量

l删除不再需要的索引

l估计索引大小并设置存储参数

l为每个索引指定表空间

l考虑并行创建索引

l考虑创建具有nologging的索引

l考虑合并或重建索引时的开销和优点

l在停用或删除约束之前考虑开销

lB_tree索引：默认和最常用的

lB_tree索引：为簇定义的索引

l散列簇索引：为散列簇定义的索引

l全局和局部索引：涉及到分区表和索引

l反向键索引：使用于Oracle Real Application Clusters应用环境

l位图索引：紧凑的，特别使用于具有少量值集的列

l基于函数的索引：包含函数/表达式的预先计算的值

l域索引：特别针对应用和插件

1.索引在逻辑上和物理上都不依赖于相关表的数据。作为独立的结构，索引需要存储空间；

2.标准和位图索引

Create index idx_emp_ename on emp(ename[desc/asc]);

Create bitmap index idx_emp_sex on emp(sex);对表操作需要较多的空间，首先应用于数据仓库环境中

3.复合索引列：

应该将含有最少重复值的列指定为第一列，第二个含有最少重复值的列指定为第二列，依此类推；含有许多重复值或含有较多null值的列不应该包含在索引中；否则需要指定索引的最后列。

4.全局和局部索引

--创建分区和非分区索引

CREATE TABLE SALES(

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE NOT NULL,

SALES_COST NUMBER(10))

PARTITION BY RANGE (SALES_DATE)(

PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01', 'YYYY-MM-DD')),

PARTITION P2 VALUES LESS THAN (TO_DATE('2004-01-01', 'YYYY-MM-DD')),

PARTITION P3 VALUES LESS THAN (MAXVALUE));

create index idx_sales_sales_date on sales(sales_date)

local

(PARTITION P1,PARTITION P2,PARTITION P3)

当相关表被分区时索引也将分区，索引的分区键和表的分区键是相同的

如果一个表是按照日期进行范围分区，可以在日期上创建索引，并使用表分区相同的范围进行索引分区，叫局部分区索引；

create index idx_sales_production_id on sales(product_id);

create index idx_sales_sale_cost on sales(sales_cost)

global partition by hash(sales_cost)

( partition p1,

partition p2);

也可以不使用和表相同的分区模式来创建分区索引，这种索引叫非分区索引或全局索引

5.在正确的表和列上创建索引

如果经常需要检索大表种少于15%的行，那么就创建索引。这个百分比的变化取决于表扫描的相对速度和相关的行数据如何分发到索引键。

主键约束和唯一约束自动创建索引，但应该在外键上创建索引；

小表不需要索引，如果查询花费时间太长，可能是表增长太快，小表已增长为大表了；

6.适合创建索引的列

列中的值相对具有唯一性

取值范围大(适合创建常规索引)

取值范围小(适合创建位图索引)

列中有许多空置，但经常查询所有具有值的行。使用{where col_x > -9.99 * power(10,125)}或{where col_x is not null}第一种方法好些，因为第一句使用了col_x列上的索引(假设col_x是一个数值列)

不适合创建索引列：

列中有许多空值，但又不查询非空值

LONG和LONG RAW列不能建索引

7.为性能而排序索引列

在create index语句中，列的排序会影响查询的性能；通常，将最常用的列放在最前面；创建多列的索引来提高查询速度，例如col1,col2,col3，查询只访问col1，或col1和col2，这样的组织可以提高查询的速度；如果只访问col2或只访问col3，或是col2和col3，查询不会使用索引

8.限制每个表的索引数量

索引越多，修改表的开销越大。特别是插入或删除行时或更改一个列时，表上的所有索引也要被修改；在确定是否创建多个索引时，需要衡量表的读写功能，如果一个表主要用于读，则多一些索引是有好处的，如果经常更好一个表，则索引少一些为好；

9.删除不再需要的索引

索引不能加速查询。表可能很小，或表种有很多行，但几乎没有索引项；

应用种的查询不使用索引；

重建索引之前必须先删除索引；

10.估计索引大小和设置存储参数

--评估创建一个索引所使用的空间成本

declare

l_used_bytes number;

l_alloc_bytes number;

begin

dbms_space.create_index_cost (

ddl => 'create index idx_emp_dept on scott.emp(deptno) tablespace users',

used_bytes => l_used_bytes,

alloc_bytes => l_alloc_bytes

);

dbms_output.put_line ('Used Bytes= '||l_used_bytes);

dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);

end;

/

11.为每个索引指定表空间

如果将表和索引使用相同的表空间，能方便对数据库进行管理(如表空间或文件备份),或保证应用的可用性；

如果将表和索引使用不同的表空间(在不同磁盘上)产生的性能，要比放在相同的表空间好，因为减少磁盘竞争；

如果一个包含数据或索引的表空间脱机，那么就不能保证那个表的语句能正常执行；

12.考虑用nologging创建索引

使用nologging创建索引时不存档，创建后应该进行备份；

节省了重做日志文件的空间；

缩短了创建索引的时间；

改善了并行创建大索引时的性能；

Create index idx_emp_salary on salary(sal) tablespace user2 parallel 5 nologging;

13.考虑合并或重建索引时的损益

叶数据块合并：ALTER INDEX IDX_EMP_ENAME COALESCE;结合

14.创建索引

Create index idx_emp_ename on emp(ename) tablespace user2 storgae(initial 20k next 20k pctincrease 75);

Create index idx_dept_unique_name on dept(dname) tablespace user2;

15.使用using index创建索引

--使用USING INDEX子句创建索引

CREATE TABLE emp (

empno NUMBER(5) PRIMARY KEY,

ename varchar2(20),

sex char(1),

age INTEGER,

address varchar2(100)

)

ENABLE PRIMARY KEY USING INDEX

TABLESPACE users;

--使用不同的USING INDEX方法创建索引

CREATE TABLE a (

a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1))

);

CREATE TABLE b(

b1 INT,

b2 INT,

CONSTRAINT bu1 UNIQUE (b1, b2)

USING INDEX (create unique index bi on b(b1, b2)),

CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);

CREATE TABLE c(c1 INT, c2 INT);

CREATE INDEX ci ON c (c1, c2);

ALTER TABLE c

ADD CONSTRAINT cpk PRIMARY KEY (c1)

USING INDEX ci;

16.收集统计信息

analyze index idx_emp_ename compute statistics;

create index idx_emp_sal on emp(sal) compute statistics;

17.创建大索引[分配较大的临时表空间]

使用create tablespace或create temporary tablespace语句创建新的临时表空间；

使用alter user语句的temporary tablespace选项为该用户创建新的临时表空间；

使用create index语句创建索引；

使用drop tablespace语句删除该表空间，然后使用alter user语句将用户的临时表空间重新设置成原始的临时表空间。

该过程可以避免将普通的、一般共享的临时表空间扩展到一个大小不合理的表空间，从而影响以后的性能；

18.联机创建索引

Create index idx_emp_ename on emp(mgr, emp1, emp2, emp3) online;

应当在DML操作不多时联机创建索引；

联机创建或重建不支持并行操作

19.创建基于函数的索引

--创建基于函数的索引

create or replace function fn_sal

(p_a number)

return number

deterministic

as

v_b number;

begin

v_b:= p_a*1.1;

return v_b;

end;

/

create index emp_sal on emp(fn_sal(sal));

@/u01/oracle/rdbms/admin/utlxplan.sql；

explain plan for

select empno,ename,fn_sal(sal),sal

from emp

where fn_sal(sal)>3300;

select lpad(' ',4*(level-2))||

operation||' '||

options||' '||

object_name "execution_plan"

from plan_table

start with id = 0

connect by prior id = parent_id;

20.创建键压缩索引

Create index idx_emp_ename on emp(ename) tablespace user1 compress 1;

Alter index idx_emp_ename rebuild nocompress;

21.创建不可见的索引

--创建不可见的索引

CREATE INDEX emp_ename ON emp(ename)

TABLESPACE users

STORAGE (INITIAL 20k

NEXT 20k

PCTINCREASE 75)

INVISIBLE;

22.更改索引的存储特征

Alter index idx_emp_ename storage(pctincrease 50);不能修改initial和minextents

Alter table emp enable primary key using index;

alter table emp disable primary key;

23.重建现有的索引

Alter index idx_emp_ename rebuild[online]

24.标记索引为不可见

Alter index idx_emp_name invisible;

Alter index idx_emp_name visible;

select table_name, index_name, status, visibility from user_indexes;

25.监视索引使用

Alter index idx_emp_sal monitoring usage;

Alter index idx_emp_sal nomonitoring usage;

select * from v$object_usage;

26.监视索引的使用空间

Analyze index idx_emp_ename validate structure;

select pct_used from index_stats where name='IDX_EMP_ENAME';

27.删除索引

Drop index idx_emp_ename;

28.数据字典

Dba_indexes，all_indexes，user_indexes;描述索引信息，某些列包含由DBMS_STATS包或ANALYZE语句产生的统计数据；

Dba/all/user_ ind_columns描述表上的索引的列，包含由DBMS_STATS包或ANALYZE语句产生的统计数据；

Dba/all/user_ind_expressions描述表上基于函数的索引的表达式；

Dba/all/user_ind_statistics包括适合于索引的优化器统计数据；

Index_stats存储最后一条ANALYZE INDEX VALIDATE STRUCTURE语句所产生的信息；

Index_histogram存储最后一条ANALYZE INDEX VALIDATE STRUCTURE语句所产生的信息；

v$object_usage包含由alter index [index_name] monitoring usage语句所产生的索引使用的信息；