create index 与alter index 执行计划分析

数据库版本： SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for 32-bit Windows: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production
创建索引的执行计划： SQL> explain plan for create index tt_ind_id on tt(object_id); Explained. SQL> @ F:oracleproduct10.1.0em_1RDBMSADMINutlxpls.sql PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- Plan hash value: 291521517 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | CREATE INDEX STATEMENT | | 19 | 38 | 4 (0)| 00:00:01 | | 1 | INDEX BUILD NON UNIQUE| TT_IND_ID | | | | | | 2 | SORT CREATE INDEX | | 19 | 38 | | | | 3 | TABLE ACCESS FULL | TT | 19 | 38 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------ PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- Note ----- - estimated index size: 65536 bytes 14 rows selected. SQL> create index tt_ind_id on tt(object_id); Index created. 索引重建的执行计划： SQL> explain plan for alter index tt_ind_id rebuild; Explained. SQL> @ F:oracleproduct10.1.0em_1RDBMSADMINutlxpls.sql PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- Plan hash value: 1259404785 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | ALTER INDEX STATEMENT | | 19 | 38 | 1 (0)| 00:00:01 | | 1 | INDEX BUILD NON UNIQUE| TT_IND_ID | | | | | | 2 | SORT CREATE INDEX | | 19 | 38 | | | | 3 | INDEX FAST FULL SCAN| TT_IND_ID | 19 | 38 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ 10 rows selected. 表中没有数据，索引重建的执行计划（执行全表扫描）： CREATE TABLE t1 (c1 NUMBER); CREATE INDEX i1 ON t1 (c1); EXPLAIN PLAN FOR ALTER INDEX i1 REBUILD; This statement creates the following execution plan (utlxpls.sql) 0 ALTER INDEX STATEMENT 1 0 INDEX BUILD (NON UNIQUE) I1 2 1 SORT (CREATE INDEX) 3 2 TABLE ACCESS (FULL) OF 'T1' 表中插入两条数据，索引重建的执行计划： SQL> insert into t0 values(1); 1 row created. SQL> insert into t0 values(2); 1 row created. SQL> commit; Commit complete. SQL> explain plan for alter index i0 rebuild; Explained. SQL> @ F:oracleproduct10.1.0em_1RDBMSADMINutlxpls.sql PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- Plan hash value: 192869984 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 82 | 1066 | 2 (0)| 00:00:01 | | 1 | INDEX BUILD NON UNIQUE| I0 | | | | | | 2 | SORT CREATE INDEX | | 82 | 1066 | | | | 3 | INDEX FAST FULL SCAN| I0 | | | | | ------------------------------------------------------------------------------- 10 rows selected. 索引重建，删除索引段后，再建新的索引段。 SQL> select * from dba_objects 2 where object_name='I0'; OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE --------- -------------- 51318 51319 INDEX SQL> alter index i0 rebuild; Index altered. SQL> select OWNER,OBJECT_NAME, OBJECT_ID,DATA_OBJECT_ID from dba_objects 2 where object_name='I0'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 51318 51320

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472973/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472973/