玉面飞龙的BLOG

What the RBO rules don’t tell you #1

Only single column indexes are ever merged. Consider the following SQL and indexes:

SELECT col1, ...
  FROM emp
 WHERE emp_name = 'GURRY'
   AND emp_no   = 127
   AND dept_no  = 12
 
Index1 (dept_no)
Index2 (emp_no, emp_name) 

The SELECT statement looks at all three indexed columns. Many people believe that Oracle will merge the two indexes, which involve those three columns, to return the requested data. In fact, only the two-column index is used; the single-column index is not used. While Oracle will merge two single-column indexes, it will not merge a multi-column index with another index.

There is one thing to be aware of with respect to this scenario. If the single-column index is a unique or primary key index, that would cause the single-column index to take precedence over the multi-column index. Compare rank 4 with rank 9 in Table 1.

NOTE: Oracle8i introduced a new hint, INDEX_JOIN, that allows you to join multi-column indexes.

What the RBO rules don’t tell you #2

If all columns in an index are specified in the WHERE clause, that index will be used in preference to other indexes for which some columns are referenced. For example:

SELECT col1, ...
  FROM emp
 WHERE emp_name = 'GURRY'
   AND emp_no   = 127
   AND dept_no  = 12
 
Index1 (emp_name)
Index2 (emp_no, dept_no, cost_center) 

In this example, only Index1 is used, because the WHERE clause includes all columns for that index, but does not include all columns for Index2.

What the RBO rules don’t tell you #3

If multiple indexes can be applied to a WHERE clause, and they all have an equal number of columns specified, only the index created last will be used. For example:

SELECT col1, ...
  FROM emp
 WHERE emp_name = 'GURRY'
   AND emp_no   = 127
   AND dept_no  = 12
   AND emp_category = 'CLERK'
 
Index1 (emp_name, emp_category)  Created 4pm Feb 11th 2002
Index2 (emp_no, dept_no) Created 5pm Feb 11th 2002

In this example, only Index2 is used, because it was created at 5 p.m. and the other index was created at 4 p.m. This behavior can pose a problem, because if you rebuild indexes in a different order than they were first created, a different index may suddenly be used for your queries. To deal with this problem, many sites have a naming standard requiring that indexes are named in alphabetical order as they are created. Then, if a table is rebuilt, the indexes can be rebuilt in alphabetical order, preserving the correct creation order. You could, for example, number your indexes. Each new index added to a table would then be given the next number.

What the RBO rules don’t tell you #4

If multiple columns of an index are being accessed with an = operator, that will override other operators such as LIKE or BETWEEN. Two =’s will override two =’s and a LIKE. For example:

SELECT col1, ...
  FROM emp
 WHERE emp_name  LIKE 'GUR%'
   AND emp_no       = 127
   AND dept_no      = 12
   AND emp_category = 'CLERK'
   AND emp_class    = 'C1'
 
Index1 (emp_category, emp_class, emp_name)  
Index2 (emp_no, dept_no) 

In this example, only Index2 is utilized despite Index1 having three columns accessed and Index2 having only two column accessed.

What the RBO rules don’t tell you #5

A higher percentage of columns accessed will override a lower percentage of columns accessed. So generally, the optimizer will choose to use the index from which you specify the highest percentage of columns. However, as stated previously, all columns specified in a unique or primary key index will override the use of all other indexes. For example:

SELECT col1, ...
  FROM emp
 WHERE emp_name     = 'GURRY'
   AND emp_no       = 127
   AND emp_class    = 'C1'
 
Index1 (emp_name, emp_class, emp_category)  
Index2 (emp_no, dept_no) 

In this example, only Index1 is utilized, because 66% of the columns are accessed. Index2 is not used because a lesser 50% of the indexed columns are used.

What the RBO rules don’t tell you #6

If you join two tables, the rule-based optimizer needs to select a driving table. The table selected can have a significant impact on performance, particularly when the optimizer decides to use nested loops. A row will be returned from the driving table, and then the matching rows selected from the other table. It is important that as few rows as possible are selected from the driving table.

The rule-based optimizer uses the following rules to select the driving table:

• A unique or primary key index will always cause the associated table to be selected as the driving table in front of a non-unique or non-primary key index.
• An index for which you apply the equality operator (=) to all columns will take precedence over indexes from which you use only some columns, and will result in the underlying table being chosen as the driving table for the query.
• The table that has a higher percentage of columns in an index will override the table that has a lesser percentage of columns indexed.
• A table that satisfies one two-column index in the WHERE clause of a query will be chosen as the driving table in front of a table that satisfies two single-column indexes.
• If two tables have the same number of index columns satisfied, the table that is listed last in the FROM clause will be the driving table. In the SQL below, the EMP table will be the driving table because it is listed last in the FROM clause.

SELECT ….
  FROM DEPT d, EMP e
 WHERE e.emp_name    = ‘GURRY’
   AND d.dept_name   = ‘FINANCE’
   AND d.dept_no     = e.dept_no

What the RBO rules don’t tell you #7

If a WHERE clause has a column that is the leading column on any index, the rule-based optimizer will use that index. The exception is if a function is placed on the leading index column in the WHERE clause. For example:

SELECT col1, ...
  FROM emp
 WHERE emp_name     = 'GURRY'
 
Index1 (emp_name, emp_class, emp_category) 
Index2 (emp_class, emp_name, emp_category) 

Index1 will be used, because emp_name (used in the WHERE clause) is the leading column. Index2 will not be used, because emp_name is not the leading column.

The following example illustrates what happens when a function is applied to an indexed column:

SELECT col1, ...
  FROM emp
 WHERE LTRIM(emp_name) = 'GURRY' 

In this case, because the LTRIM function has been applied to the column, no index will be used.

More in

   Oracle SQL Tuning Pocket Reference