| Previous | Table of Contents | Next |
To remedy this problem, some dialects of SQL allow the user to control the index access. This is a gross violation of the declarative nature of theoretical SQL: The user does not control access paths. But in practice, these extensions can improve performance. Oracle, for example, allows the concatenation of a null string to the field name in the WHERE clause to suppress index access. The previous query could be rewritten in Oracle SQL to bypass the student_level index as follows:
SELECT * FROM STUDENT WHERE student_level||'' = 'UNDERGRAD';
The concatenation (||) of a null string to the field tells the Oracle SQL optimizer to bypass index processing for this field, instead invoking a faster-running full-table scan.
This a very important point. While SQL optimizers are becoming more intelligent about their databases, they still cannot understand the structure of the data and will not always choose the best access path.
A concatenated index is created on multiple columns. This type of index can greatly speed up queries where all of the index columns are specified in the queries’ SQL WHERE clause. For example, assume the following index on the STUDENT table:
CREATE INDEX idx1 ON STUDENT (student_level, major, last_name) ASCENDING;
The following concatenated index could be used to speed up queries that reference both student_level and major in the WHERE clause:
SELECT student_last_name FROM STUDENT
WHERE
student_level = 'UNDERGRAD'
AND
major = 'computer science';
However, some queries using major or student_level will not be able to use this concatenated index. In this example, only the major field is referenced in the query:
SELECT * FROM STUDENT
WHERE
major = 'computer science';
In this example, even though student_level is the high-order index key, it will not be used since there are other index keys following student_level. Since major is the second column in the index, Oracle will conclude that the index cannot be used:
SELECT last_name FROM STUDENT
WHERE
student_level = 'PLEBE'
ORDER BY last_name;
Since student_level is the first item in the index, the leading portion of the index can be read and the SQL optimizer will invoke an index scan. Why have we chosen to add the last_name to the index, even though it is not referenced in the WHERE clause? Because Oracle will be able to service the request by reading only the index, and the rows of the STUDENT table will never be accessed. Also, since the ORDER BY clause asks to sort by last_name, Oracle will not need to perform a sort on this data.
The NOT (!) operator will cause an index to be bypassed and the query “show all undergrads who are NOT computer science majors” will cause a full-table scan:
SELECT * FROM STUDENT
WHERE
student_level = 'UNDERGRAD'
AND
major != 'computer science';
Here, the NOT condition isn’t a sargeable predicate and will cause a full-table scan.
Tools exist within most implementations of SQL that allow the access path to be interrogated.
To see the output of an EXPLAIN PLAN, you must first create a plan table. Oracle provides a script in $ORACLE_HOME/rdbms/admin with a script called utlxplan.sql. Execute utlxplan.sql and create a public synonym for the plan_table:
sqlplus > @utlxplan table created. sqlplus > create public synonym plan_table for sys.plan_table; synonym created.
Most relational databases use an explain utility that takes the SQL statement as input, runs the SQL optimizer, and outputs the access path information into a plan_table, which can then be interrogated to see the access methods. Listing 4.6 runs a complex query against a database.
Listing 4.6 A sample database query.
EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
SET STATEMENT_ID = 'RUN1'
INTO plan_table
FOR
SELECT 'T'||salesnet.terr_code, 'P'||detsale.pac1 || detsale.pac2 || detsale.pac3, 'P1', sum(salesnet.ytd_d_ly_tm),
sum(salesnet.ytd_d_ty_tm),
sum(salesnet.jan_d_ly),
sum(salesnet.jan_d_ty),
FROM salesnet, detsale
WHERE
salesnet.mgc = detsale.mktgpm
AND
detsale.pac1 in ('N33','192','195','201','BAI',
'P51','Q27','180','181','183','184','186','188',
'198','204','207','209','211')
GROUP BY 'T'||salesnet.terr_code, 'P'||detsale.pac1 || detsale.pac2 || detsale.pac3;
This syntax is piped into the SQL optimizer, which will analyze the query and store the plan information in a row in the plan table identified by RUN1. Please note that the query will not execute; it will only create the internal access information in the plan table. The plan tables contains the following fields:
Now that the plan_table has been created and populated, you may interrogate it to see your output by running the following query:
plan.sql - displays contents of the explain plan table
SET PAGES 9999;
SELECT lpad(' ',2*(level-1))||operation operation,
options,
object_name,
position
FROM plan_table
START WITH id=0
AND
statement_id = 'RUN1'
CONNECT BY prior id = parent_id
AND
statement_id = 'RUN1';
Listing 4.7 shows the output from the plan table shown in Listing 4.6.
Listing 4.7 The plan table’s output.
SQL> @list_explain_plan
OPERATION
-----------------------------------------------------------------
OPTIONS OBJECT_NAME POSITION
-----------------------------------------------------------------
SELECT STATEMENT
SORT
GROUP BY 1
CONCATENATION 1
NESTED LOOPS 1
TABLE ACCESS FULL SALESNET 1
TABLE ACCESS BY ROWID DETSALE 2
INDEX RANGE SCAN DETSALE_INDEX5 1
NESTED LOOPS
| Previous | Table of Contents | Next |