In Quest SQL Optimizer, transformation rules relating to index usage are designed to guide the database SQL optimizer as to how it should use the indexes for a specific SQL statement.  Although those rules may familiar to most of SQL developers, but it is still worth to illustrate some commonly used rules for SQL beginners, of course, if you don’t want to do it by yourself or if you don’t have time to check every SQL in your application, you can use Quest SQL Optimizer to do those boring jobs.  Why? I still remember around 20 years; I was so exciting that I was getting to know more in tuning SQL statements, the first few hundreds of SQL were interesting to me, after I was promoted to DBA position, then tuning SQL is no longer an interest to me, it become my job! It is ironic when you get pay for your interest; you are no longer interested in it anymore.  That’s why I invented a product to automate it job.

The following two SQL statements are quite often used in an online query system in which the user inputs values in a range from :c to :d to retrieve data from a table. If user does not specify the range, the :c and :d values will be null. Due to the complexity of the SQL statement caused by using an OR condition plus some undetermined variables, the database SQL optimizer will usually choose a full table scan to process the SQL statement.

For a SQL statement that has multiple indexes which can be used to search a table, the following transformation can be used to enable any one of the indexes.

To disable the index on the numeric emp_id field, zero was added to the field. This disables the index because zero must be added to emp_id for each row thereby requiring a full table scan or enabling a different index to be used.

The same process is used for the character field of emp_dept where nothing, represented by '' (single quotes with no value), was concatenated to the field. This also disables the index because the concatenate operation must be preformed for each row thereby requiring a full table scan or enabling a different index to be used.

The other technique for disabling the index is to use the COALESCE operation which in the way that it is used in this case does nothing to the value in the field. But because it must be preformed for each row in the table, it disables the index and thereby causes a full table scan or enables a different index to be used.