创建索引对sql语句执行的影响（The impact of creating indexes on the execution of SQL statements）.docVIP

创建索引对sql语句执行的影响（The impact of creating indexes on the execution of SQL statements） The impact of [Oracle] creation indexes on the execution of SQL statements (1) First, the impact of index creation on execution plans Before SQL starts executing, Oracle will determine the execution plan of the SQL statement and access the corresponding tables and indexes as scheduled. Once the execution plan is determined, Oracle will complete the execution of the SQL statement in accordance with the execution plan, and the index created after the execution of the SQL statement will not change the execution plan of the SQL statement. Therefore, creating an index does not have any impact on the execution plan, nor does it affect the running SQL statement. Here is an example of a simple proof: 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 (*) Dear sirsre One hundred and seven thousand and thirty-seven Elapsed: 00:0