Oracle技术：通过EXPLAINPLANFOR查看DDL语句执行计划
在9i及以前版本，Oracle只能看到DML的执行打算，不外从10g起头，经由过程EXPLAIN PLAN FOR的体例，已经可以看到DDL语句的执行打算了。这对于研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX，ALTER INDEX REBUILD等语句有很大的辅佐。
举个简单的例子，Oracle的文档上对于索引的成立有如下描述：
The optimizer can use an existing index to build another index. This results in a much faster index build.
如不美旁观不到DDL的执行打算，只能按照执行时刻的长短去猜测Oracle的具体执行打算，可是这种体例没有足够的说服力。可是经由过程DDL的执行打算，就使得结不美观一目了然了。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已建树。
SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
已诠释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3035241083
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | TABLE ACCESS FULL | T | 57915 | 3732K| 41 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Note
-----
- estimated index size: 5242K bytes
已选择14行。
SQL> CREATE INDEX IND_T_OWNER_NAME ON T(OWNER, OBJECT_NAME);
索引已建树。
SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
已诠释。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 517242163
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | INDEX FAST FULL SCAN| IND_T_OWNER_NAME | | | | |
-------------------------------------------------------------------------------------------
Note
-----
- estimated index size: 5242K bytes
已选择14行。
SQL> SET AUTOT ON
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已建树。
注重，查看DDL的执行打算需要使用EXPLAIN PLAN FOR，AUTOTRACE对于DDL是无效的。
页:
[1]