使用EXPLAIN PLAN获取SQL语句执行计划 - 数据库编程

scott@ORCL> @Get_Plan
Enter value for input_statement_id: IDX
old 9: AND statement_id =upper( '&input_statement_id')
new 9: AND statement_id =upper( 'IDX')

OPERATION OPTIONS OBJECT_NAME POS BYTES COST
------------------------- ------------------------- ------------------------- ---------- ---------- ----------
CREATE INDEX STATEMENT 6 4000 6
INDEX BUILD NON UNIQUE I_T1 1
SORT CREATE INDEX 1 4000
TABLE ACCESS FULL T1 1 4000 5

scott@ORCL> CREATE INDEX i_t1 ON t1 (object_id);

scott@ORCL> delete from plan_table;

3.使用自顶向下的读取方法获取执行计划

--使用explain plan加载重建索引的执行计划
scott@ORCL> explain plan set statement_id='A_IDX' for alter index i_t1 rebuild;

Explained.

--执行下面的语句来获的A_IDX的执行计划，其结果是从上至下来读，从最内侧往最外侧读。
SELECT LPAD(' ', 2 * (LEVEL - 1)) || LEVEL || '.' || NVL(POSITION, 0) || ' ' ||
OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' ||
OBJECT_TYPE || ' ' ||
DECODE(ID, 0, STATEMENT_ID || ' Cost = ' || POSITION) || COST || ' ' ||
OBJECT_NODE "Query Plan"
FROM PLAN_TABLE
START WI

使用EXPLAIN PLAN获取SQL语句执行计划(四)