如何使用plsql 给一个oracle数据库中的表的字段建索引

create index index_name on table_name(column_name)

只要你查询使用到建了索引的字段，一般都会用到索引。

--创建表

create table aaa

(

a number,

b number

)

--创建索引

create index idx_a on aaa (a)

--使用索引

select * from aaa where a=1

这句查询就会使用索引 idx_a

重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：

首先建立测试表及数据：

SQL>CREATE TABLE TEST AS SELECT CITYCODE C1 FROM CITIZENINFO2

Table created

SQL>ALTER TABLE TEST MODIFY C1 NOT NULL

Table altered

SQL>SELECT COUNT(1) FROM TEST

COUNT(1)

----------

16000000

一、drop and re-create和rebuild

首先看看正常建立索引时，对表的加锁情况。

suk@ORACLE9I>@show_sid

SID

----------

14

suk@ORACLE9I>CREATE INDEX IDX_TEST_C1 ON TEST(C1)

索引已创建。

SQL>SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14

OBJECT_NAME LMODE

------------------------------ ----------

OBJ$ 3

TEST 4

可见，普通情况下建立索引时，oracle会对基表加share锁，由于share锁和 row-X是不兼容的，也就是说，在建立索引期间，无法对基表进行DML *** 作。

对于删除重建索引的方法就不介绍了，它与上面的描述是一样的，下面我们看看用rebuild的方式建立索引有什么特别。

suk@ORACLE9I>ALTER INDEX IDX_TEST_C1 REBUILD

索引已更改。

另开一个会话，查询此时test的加锁情况：

SQL>SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14

OBJECT_NAME LMODE

------------------------------ ----------

TEST 4

可见，rebuild的方式对基表的加锁方式与CREATE时是一样的。

另开一个会话，在索引正在rebuild时，执行如下SQL：

suk@ORACLE9I>SET AUTOTRACE TRACE

suk@ORACLE9I>SELECT /*+ INDEX(TEST) */ COUNT(1) FROM TEST WHERE ROWNUM<10

执行计划

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=26 Card=1)

1 0 SORT (AGGREGATE)

2 1 COUNT (STOPKEY)

3 2 INDEX (FULL SCAN) OF 'IDX_TEST_C1' (NON-UNIQUE) (Cost=

26 Card=1986621)

可以看到索引在重建时，查询仍然可以使用旧索引。实际上，oracle在rebuild时，在创建新索引过程中，并不会删除旧索引，直到新索引rebuild成功。

从这点可以知道rebuild比删除重建的一个好处是不会影响原有的SQL查询，但也正由于此，用rebuild方式建立索引需要相应表空间的空闲空间是删除重建方式的2倍。

重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：

相关文章：

oracle重建索引（一）

二、rebuild 和rebuild online

首先我们跟踪一下rebuild online的过程。

另开一个会话查看锁的信息：

SQL>SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=14

OBJECT_NAME LMODE

------------------------------ ----------

SYS_JOURNAL_10499 4

TEST 2

SQL>INSERT INTO TEST VALUES(11)

1 row inserted

SQL>COMMIT

Commit complete

可以看到，在rebuild online期间，oracle对基表加的是RS所，此时我们可以对基表进行DML *** 作。但奇怪的话在相同的session中有一个SYS_JOURNAL_10499表被加SHARE锁，这个表是干什么用的呢？

我们看看trace文件，有这样的信息：

create table "SUK"."SYS_JOURNAL_10499" (C0 NUMBER(6,0), opcode char(1),

partno number, rid rowid, primary key( C0 , rid )) organization index

TABLESPACE "TEST"

CREATE UNIQUE INDEX "SUK"."SYS_IOT_TOP_10605" on

"SUK"."SYS_JOURNAL_10499"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "TEST"

NOPARALLEL

drop table "SUK"."SYS_JOURNAL_10499"

我们在查查10499是什么东西：

SQL>SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=10499

OBJECT_NAME OBJECT_TYPE

------------------------------ ------------------

IDX_TEST_C1 INDEX

从这些信息可以推测：表SYS_JOURNAL_10499就是实现在重建索引时不阻塞DML *** 作而设计的，它存储的是在索引重建期间发生在基表的数据变化。可以推测，CREATE INDEX .... ONLINE应该也有一张类似的表。

实际上，oracle之所以在创建索引时锁表阻止DML *** 作就是为了防止不能索引新变化的数据，在online方式重建时，有了临时表SYS_JOURNAL_XXXX，oracle就可以放心大胆地让用户 *** 作了，因为所有重建索引期间的数据变化信息都会保留在SYS_JOURNAL_XXX表中，当索引重建完后再加上SYS_JOURNAL_XXX记录的数据，就不会漏索引数据了。（XXX是被重建的索引对应的OBJECT_ID)

导读：

重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：

相关文章：

oracle重建索引（一）

oracle重建索引（二）

三、rebuild和rebuild online的数据源

网上一直有这样一个说法：重建索引是以原索引作为数据源的。那么，这种说法是否准确呢？我们做实验来验证一下：

suk@ORACLE9I>COL SEGMENT_NAME FORMAT A30

--首先看看表和索引的大小

suk@ORACLE9I>SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C1')

SEGMENT_NAME BYTES

------------------------------ ----------

TEST 201326592

IDX_TEST_C1 293601280

suk@ORACLE9I>EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD

已解释。

suk@ORACLE9I>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY())

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

-----------------------------------------------------------------------

| 0 | ALTER INDEX STATEMENT | | | | |

| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |

| 2 | SORT CREATE INDEX | | | | |

| 3 | TABLE ACCESS FULL | TEST | | | |

-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当索引比表大时，rebuild索引用的数据源是基表。

suk@ORACLE9I>EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE

已解释。

suk@ORACLE9I>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY())

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

-----------------------------------------------------------------------

| 0 | ALTER INDEX STATEMENT | | | | |

| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |

| 2 | SORT CREATE INDEX | | | | |

| 3 | TABLE ACCESS FULL | TEST | | | |

-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当索引比表大时，rebuild online索引用的数据源是基表。

--我们为TEST添加一列，使得表比索引大

suk@ORACLE9I>ALTER TABLE TEST ADD(C2 CHAR(30) DEFAULT '1')

表已更改。

suk@ORACLE9I>SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C

1')

SEGMENT_NAME BYTES

------------------------------ ----------

TEST 1476395008

IDX_TEST_C1 293601280

suk@ORACLE9I>EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD

已解释。

suk@ORACLE9I>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY())

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

-----------------------------------------------------------------------

| 0 | ALTER INDEX STATEMENT | | | | |

| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |

| 2 | SORT CREATE INDEX | | | | |

| 3 | INDEX FAST FULL SCAN| IDX_TEST_C1 | | | |

-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当表比索引大时，执行计划已经改变，rebuild索引是以索引作为数据源的。

suk@ORACLE9I>EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE

已解释。

suk@ORACLE9I>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY())

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

-----------------------------------------------------------------------

| 0 | ALTER INDEX STATEMENT | | | | |

| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |

| 2 | SORT CREATE INDEX | | | | |

| 3 | TABLE ACCESS FULL | TEST | | | |

-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当表比索引大时，rebuild online仍然以基表作为数据源。

rebuild模式下，因为表数据不会产生变化，oracle主要考虑性能问题，把更快扫描完成的段作为数据源。在上面的例子中，我们并没有对表进行分析，故oracle应该根据数据段的大小来决定那个作为数据源的。一般索引字段比较多，或者对索引字段的DML *** 作较多，可能会导致索引比表大，这时oracle就会使用基表作为新索引的数据源进行rebuild了。

而在rebuild online模式下，因为允许DML *** 作，而表数据变化的同时索引也会跟着变化，为了索引与基表数据的一致性，比如采用基表数据作为数据源，而不能用原索引数据作为数据源。

我们用反证法证明不能用原索引作为新索引的数据源。

例如：

T1发出rebuild online命令

T2删除某条数据，删数据的同时，oracle会自动维护了旧索引

T3扫描经过T2数据所在索引节点

T4插入一条记录，新记录对应的索引节点刚好重用了T2删除的数据对应的索引节点空间

如果是这样的话，新建的索引将不包含T4插入的记录的信息。所以，rebuild online情况下新索引的数据源不能是原索引。

rebuild online情况下，如果非用原索引作为新索引的数据源的话，用中间表记录索引变化的方法应该是可以实现的，但由于数据变化会同时引起索引变化的特定决定了这种方法将异常复杂及效率底下，所以oracle不考虑旧索引作为新索引的数据源是有道理的。

结论：

1、rebuild会阻塞对基表的DML *** 作，但不会影响rebuild期间查询对原有索引的使用。

2、rebuild的数据源可能是基表，也可能是原索引。取决于基表和原索引的大小，那个小，rebuild时就会用那个作为数据源。这也说明了网上盛传的rebuild以原索引作为数据库的说法是不完全正确的。

3、rebuild online运行用户在索引重建期间执行DML *** 作。

4、rebuild online的数据源是基表

1、创建测试表，

create table test_index(id varchar2(20)， v_date date)

2、将id字段，添加索引；

-- Create/Recreate indexes

create index idx_test_index_id on TEST_INDEX (id)

3、编写sql，查看系统视图，查找该索引是否存在；

select * from user_indexes t where index_name = upper('idx_test_index_id')；

4、执行sql语句，并查看执行计划，可以发现索引已经起了作用；

---