索引概念及创建

1.索引可以提高查询效率。ORACLE索引是按索引关键字顺序存放记录，也叫数据结构。

索引记录中存放索引关键字和指向表中真正数据的指针，ORACLE利用算法在索引上可以很快查找到所需记录，并利用指针找到所需数据。

索引是独立于表的对象，存放在与表不同的表空间中。索引的删除不会影响真正存数据的表。

索引由ORACLE系统自动维护，并由ORACLE决定什么时候使用索引。

当表被删除，基于此表的索引被自动删除。

索引是一种允许直接访问表中某一数据行的树型结构。索引逻辑分类从应用角度划分的：

单列索引，基于一列的索引

多列索引，组合索引，基于多列的索引，最多32列。

唯一索引，保证表中任何数据行的索引列值都不相同。

非唯一索引，表中不同数据行的索引列的值可以相同。

基于函数的索引，可以是B树索引也可以是位图索引。一列或多列

物理分类是按物理结构分：

分区或非分区索引

B树或位图索引。

#################################################################################

2.ORACLE中索引默认都使用B-树结构。

创建索引要遵守以下原则

平衡查询和DML需要，DML操作频繁的表上尽量减少索引数量，

将其放入单独表空间，

使用统一EXTENT，数据块尺寸的5倍，减少系统转换时间。

对大索引考虑使用NOLOGGING，减少REDO操作提高系统效率。

索引的INITRANS参数应该比相对应表高。

##############################################################################3333

3.创建、修改、删除索引

BYS@bys1>create table dept3 as select * from dept;
Table created.
BYS@bys1> create index bys.dept3_dname_idx on bys.dept3(dname)  pctfree 20  storage(initial 100k next 100k pctincrease 0 maxextents 100) tablespace  users;
Index created.

BYS@bys1>col index_type for a10

BYS@bys1>col table_name for a10
BYS@bys1> select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes where owner='BYS' and table_name='DEPT3';
INDEX_NAME      TABLE_NAME TABLESPACE INDEX_TYPE UNIQUENES STATUS
--------------- ---------- ---------- ---------- --------- --------

DEPT3_DNAME_IDX DEPT3      USERS      NORMAL     NONUNIQUE VALID

BYS@bys1>select index_name,pct_free,pct_increase,initial_extent,next_extent  from dba_indexes where owner='BYS' and table_name='DEPT3';
INDEX_NAME        PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT
--------------- ---------- ------------ -------------- -----------
DEPT3_DNAME_IDX         20                      106496      106496

修改索引

BYS@bys1>alter index  bys.dept3_dname_idx  rebuild pctfree 40 storage (next 300k);
Index altered.
BYS@bys1>select index_name,pct_free,pct_increase,initial_extent,next_extent  from dba_indexes where owner='BYS' and table_name='DEPT3';
INDEX_NAME        PCT_FREE PCT_INCREASE INITIAL_EXTENT NEXT_EXTENT
--------------- ---------- ------------ -------------- -----------
DEPT3_DNAME_IDX         40                      106496      311296

#################

手工增加一个EXTENT

BYS@bys1>select  segment_name,segment_type,tablespace_name,extents from dba_segments where owner='BYS' and  segment_name='DEPT3_DNAME_IDX';
SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE    EXTENTS
--------------- ------------------ ---------- ----------
DEPT3_DNAME_IDX INDEX              USERS               3
BYS@bys1> alter index bys.dept3_dname_idx  allocate extent;
Index altered.
BYS@bys1>select  segment_name,segment_type,tablespace_name,extents from dba_segments where owner='BYS' and  segment_name='DEPT3_DNAME_IDX';
SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE    EXTENTS
--------------- ------------------ ---------- ----------
DEPT3_DNAME_IDX INDEX              USERS               4

回收用户下索引段的没用磁盘空间

原来使用了4个EXTENTS，其中两个是手动分配，未使用。可以用以下语句回收未使用空间：

BYS@bys1> alter index bys.dept3_dname_idx  deallocate unused;

Index altered.
BYS@bys1>select  segment_name,segment_type,tablespace_name,extents from dba_segments where owner='BYS' and  segment_name='DEPT3_DNAME_IDX';
SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE    EXTENTS
--------------- ------------------ ---------- ----------
DEPT3_DNAME_IDX INDEX              USERS               2

合并碎片语句：

BYS@bys1> alter index bys.dept3_dname_idx  coalesce;
Index altered.

#########################################################

删除索引

BYS@bys1>drop index bys.dept3_dname_idx ;
Index dropped.
BYS@bys1>select index_name,table_name,tablespace_name,index_type,uniqueness,status from dba_indexes where owner='BYS' and table_name='DEPT3';
no rows selected