Proactive approach to tuning

 

Typically you will create an index for the most selective columns and create statistics for the least selective columns in a query. By creating an index, the optimizer knows that the column is selective and it also gives the optimizer the ability to use that index to implement the query.

In a perfect radix index, the order of the columns is important. In fact, it can make a difference as to whether the optimizer uses it for data retrieval at all. As a general rule, order the columns in an index in the following way:

• Equal predicates first. That is, any predicate that uses the "=" operator may narrow down the range of rows the fastest and should therefore be first in the index.

• If all predicates have an equal operator, then order the columns as follows:

  • Selection predicates + join predicates

  • Join predicates + selection predicates

  • Selection predicates + group by columns

  • Selection predicates + order by columns

In addition to the guidelines above, in general, the most selective key columns should be placed first in the index.

Consider the following SQL statement:

SELECT b.col1, b.col2, a.col1
   FROM table1 a, table2 b    WHERE b.col1='some_value' AND
     b.col2=some_number AND
     a.join_col=b.join_col    GROUP BY b.col1, b.col2, a.col1
   ORDER BY b.col1

With a query like this, the proactive index creation process can begin. The basic rules are:

• Custom-build a radix index for the largest or most commonly used queries. Example using the query above:

  radix index over join column(s) - a.join_col and b.join_col 
  radix index over most commonly used local 	selection column(s) - b.col2 

• For ad hoc online analytical processing (OLAP) environments or less frequently used queries, build single-key EVIs over the local selection column(s) used in the queries. Example using the query above:

   EVI over non-unique local selection columns - b.col1 and b.col2

 

Parent topic:

Indexing strategy