|
一、创建索引对执行计划的影响
在SQL开始执行之前，Oracle会确定SQL语句的执行计划，并按照执行计划的步骤访问相应的表和索引。
一旦执行计划确定下来，Oracle会按照这个执行计划完成SQL语句的执行，在SQL语句执行开始之后建立的索引不会改变SQL语句的执行计划。
因此，创建索引不会对执行计划有任何的影响，也就不会对运行中的SQL语句有影响。下面通过一个例子简单验证一下：
SQL> CREATE TABLE TEST (ID NUMBER, FID NUMBER, NAME VARCHAR2(30), BID_COLUMNS CHAR(2000));
Table created.
SQL> INSERT INTO TEST VALUES (1, 0, "OBJECT", "0");
1 row created.
SQL> INSERT INTO TEST VALUES (2, 1, "TABLE", "0");
1 row created.
SQL> INSERT INTO TEST VALUES (3, 1, "INDEX", "0");
1 row created.
SQL> INSERT INTO TEST VALUES (4, 1, "VIEW", "0");
1 row created.
SQL> INSERT INTO TEST VALUES (5, 1, "SYNONYM", "0");
1 row created.
SQL> INSERT INTO TEST VALUES (6, 1, "SOURCE", "0");
1 row created.
SQL> INSERT INTO TEST SELECT 20000+ROWNUM, 2, TABLE_NAME, "0" FROM DBA_TABLES;
874 rows created.
SQL> INSERT INTO TEST SELECT 30000+ROWNUM, 3, INDEX_NAME, "0" FROM DBA_INDEXES;
1074 rows created.
SQL> INSERT INTO TEST SELECT 40000+ROWNUM, 4, VIEW_NAME, "0" FROM DBA_VIEWS;
2929 rows created.
SQL> INSERT INTO TEST SELECT 50000+ROWNUM, 5, TABLE_NAME, "0" FROM DBA_SYNONYMS;
2437 rows created.
SQL> INSERT INTO TEST SELECT 60000+ROWNUM, 6, NAME, "0" FROM DBA_SOURCE;
99717 rows created.
SQL> COMMIT;
Commit complete.
SQL> set timing on
SQL> SELECT COUNT(*) FROM TEST
2 START WITH ID = 1
3 CONNECT BY PRIOR ID = FID;
COUNT(*)
----------
107037
Elapsed: 00:02:03.84
1
2
3
下一页>>
数据挖掘论坛
| 
