Impact of creating an index on SQL statement execution

The impact of creating an index on the execution plan

Before SQL starts executing, Oracle determines the execution plan for the SQL statement and accesses the corresponding tables and indexes according to the steps of the execution plan.

Once the execution plan is finalized, Oracle completes the execution of the SQL statement according to this execution plan, and the index established after the execution of the SQL statement does not change the execution plan of the SQL statement.

Therefore, creating an index does not have any effect on the execution plan, nor does it affect the running SQL statement. Here is a simple example to verify:

Sql> CREATE TABLE TEST (ID number, FID number, NAME VARCHAR2 (), 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

Constructs a tree query and then logs the elapsed time of the tree query.

Sql> SELECT COUNT (*) from TEST

2 START with ID = 1

3 CONNECT by PRIOR ID = FID;

COUNT (*)

----------

107037

elapsed:00:05:26.15

Run the query again, starting at the start of the query, and immediately creating an index in another session. By observing the execution time, you can see that creating an index does not result in a performance boost to the running SQL statement, and is likely to slow the query because of contention for system resources. If, in a more reasonable system of IO distribution, you can see that creating indexes can be done quickly, and that subsequent execution of the same query will quickly return the structure because of the use of the index, the creation of the index does not speed up the statement that is already in the running state.

SESSION2:

Sql> SET TIMING on

Sql> CREATE INDEX ind_test_id on TEST (ID) tablespace USERS;

The index has been created.

Time used: 000:01:56.92

Sql> CREATE INDEX Ind_test_fid on TEST (FID) tablespace USERS;

The index has been created.

Time used: 00:02:00.57

When indexing is established, the same query speed is significantly improved.

Sql> SELECT COUNT (*) from TEST

2 START with ID = 1

3 CONNECT by PRIOR ID = FID;

COUNT (*)

----------

107037

Time used: 00:01:02.11

The statements and query statements that set up two indexes above are run on a separate SESSION2. The three operations on the SESSION2--creating two indexes and executing the same query statement--are done, and the results of the first session are still not returned.

Ii. the impact of creating indexes on Oracle internal mechanisms

A simple example shows that creating an index does not change the execution plan of the SQL that is already running. Not to say, however, that creating an index does not give a performance boost to an already running SQL statement.

Here's a very special example:

Sql> CREATE TABLE TEST as SELECT rownum ID, a.* from Dba_objects A;

Table has been created.

Sql> CREATE TABLE TEST1 as SELECT rownum ID, RowNum FID, a.* from dba_synonyms A;

Table has been created.

sql> ALTER TABLE TEST ADD CONSTRAINT pk_test PRIMARY KEY (ID);

The table has changed.

sql> ALTER TABLE TEST1 ADD CONSTRAINT fk_test1_fid FOREIGN KEY (FID) REFERENCES TEST (ID);

The table has changed.

Sql> INSERT into TEST1 SELECT * from TEST1;

1616 lines have been created.

Sql> INSERT into TEST1 SELECT * from TEST1;

3232 lines have been created.

Sql> INSERT into TEST1 SELECT * from TEST1;

6464 lines have been created.

Sql> INSERT into TEST1 SELECT * from TEST1;

12928 lines have been created.

Sql> INSERT into TEST1 SELECT * from TEST1;

25856 lines have been created.

Sql> COMMIT;

Submit completed.

Sql> DELETE TEST1;

51712 rows have been deleted.

Sql> COMMIT;

Submit completed.

Sql> SET TIMING on

Sql> DELETE TEST;

6208 rows have been deleted.

Time used: 00:00:17.03

Sql> ROLLBACK;

Fallback is complete.

Time used: 00:00:00.06

Constructs two tables, TEST1 's FID establishes the foreign key of the reference test table ID column. But there is no index on the foreign key column.

Start the test by filling in the data in the test and TEST1 tables with a certain amount of data. This test is to delete the execution time of the test table. First remove the TEST1 with the delete command, calculate the time it takes to delete the test table after submission, approximately 17 seconds, and then roll back the data.

A second deletion test is prepared below. The difference is that, at the beginning of the delete operation, immediately in another session to the Foreign Key column index, through the test can be found, almost at the same time the index was created, the first session returned the results, the deletion of the time required to shorten to 3 seconds.

Delete statement for the first session:

Sql> DELETE TEST;

6208 rows have been deleted.

Time used:? 00:00:03.00

The index creation statement for the second session:

Sql> CREATE INDEX Ind_test1_fid on TEST1 (FID);

Index has been created

The creation of indexes in this test affects the SQL statements that are already running and significantly improves execution efficiency. This phenomenon is not in conflict with the view described in the previous article. Oracle's execution plan is immutable for user-issued SQL statements, but Oracle does a lot of internal operations to execute user-issued SQL statements, including permissions checking, syntax checking, target object presence, and maintaining data integrity, and so on. In this example, the execution plan for the SQL statements issued by the user has not changed, and the execution plan for internal maintenance operations statements for Oracle has changed.

If you perform a delete operation in the first session, and you check the statement that the first session is running through the following SQL statement, you will see the following results (9i and previous versions, or 10g, only delete TEST).

Sql> SELECT Sql_text from V$session A, V$sql B

2 WHERE a.sql_hash_value = B.hash_value

3 and a.sql_address = b.address

4 and a.sid = 17;

Sql_text

----------------------------------------------------------------------------

Select/**//*+ all_rows */COUNT (1) from "YANGTK". " TEST1 "where" FID =: 1

This SQL statement is the internal SQL that Oracle uses to maintain integrity.

Looking back at our example, we established a foreign key, but did not establish an index. Oracle checks to see if the primary key is referenced in TEST1 every time a test record is deleted. Because there is no index, Oracle can only look for records in TEST1 through a full table scan. Although TEST1 is not logged, deleting test uses delete instead of truncate, so the TEST1 high watermark does not drop, which means that every time a test is deleted, a table with 50,000 data is scanned for the entire table. That's why the delete operation is slow to execute.

And the index that we built is speeding up this step, and the SQL statements maintained by Oracle Select Index scans after the index is available, so the delete operation returns quickly after the index is created.

Third, summary

Creating an index does not result in a performance boost for the running SQL that the user has issued. This is because the statements executed by the user run according to the execution plan, and the execution plan is determined at the start of the run and does not change during the run of the SQL statement.

For SQL execution, the SQL statements executed internally by Oracle for maintenance are likely to gain performance gains from the newly created indexes.