14.8 How do I…Suppress indexes in a query?
Problem
I have used EXPLAIN PLAN, the AUTOTRACE option, or SQL*Trace to reveal the optimizer’s execution plan for some queries. I have found that the optimizer occasionally uses an index when the query returns a significant number of table rows. In these cases, index suppression enhances performance. How do I tell the Oracle8 optimizer not to use an index?
Technique
Use EXPLAIN PLAN, the SQL*Trace utility, or the SQL*Plus autotrace option to reveal how SQL statements use indexes. Changes to the WHERE clause of a SQL statement that do not alter the clause’s result can suppress index use. Specifically, if a SQL statement modifies an indexed column, then the optimizer will not use indexes based on that column.
Steps
1. Run SQL*Plus, connect as the WAITE user, and use the START command to load and execute the script CHP14_17.SQL as shown in Figure 14.17.
The script creates a partitioned version of the familiar EMP table traditionally found in SCOTT’s schema. The partition key is column EMPNO. The script also creates a local prefixed index named IDX_EMP14_8. (You can read more about the three partitioned index types Oracle8 supports in How-To 5.8). Finally, the script uses an anonymous PL/SQL block to insert 500 rows into the EMP14_8 table.
Note-Partitioned tables are most useful in supporting very large databases. The small partitions featured in Figure 14.17 are not realistic and are intended for example purposes only.
2. If you haven’t already done so, follow the directions in the “Comments” section of How-To 14.3 to enable the AUTOTRACE option for the WAITE account.
3. In SQL*Plus, issue the SET AUTOTRACE ON command to reveal the execution paths of the SQL statements in this How-To.
4. Use the START command to load and execute the script CHP14_18.SQL. The script and its result appear in Figure 14.18.
Because AUTOTRACE is on, SQL*Plus generates the execution plan for the SQL statement. The plan indicates use of the index on EMP14_8 even though the query returned 30% of the data residing in the table.
5. Load and execute the script CHP14_19.SQL with the START command as shown in Figure 14.19.
Lines 3 and 4 of the script change the WHERE clause syntax of the previous step by adding 0 to the value of EMPNO. This has no effect on the results of the WHERE clause but does prevent the optimizer from using the index on the EMPNO column. The execution plan results support this assertion by showing that the optimizer has performed a full table scan instead of utilizing the IDX_EMP14_8 index.
How It Works
Step 1 creates a sample partitioned table to illustrate the rest of this section. Steps 2 and 3 enable the AUTOTRACE facility so that SQL*Plus reveals SQL statement execution plans. Step 4 shows how the default behavior of a large volume query is to use an index. Step 5 shows how trivial changes to indexed columns in the WHERE clause can prevent Oracle8’s optimizer from incorrectly using an index.
Comments
There is, of course, another way to suppress index use: hints! The script CHP14_20.SQL, shown in Figure 14.20, is just as effective at forcing a full table scan as the machinations in Step 5; it is also easier to read.
The method, and this How-To really presents a trick more than a method, has achieved some popularity among developers. It is included here for completeness and to enhance understanding of legacy code, but optimizer hints are the prescribed solution to this How-To’s problem. Future changes in Oracle’s optimizer may render obsolete the technique presented here.