Having the account column the index avoided the need sort

than the other index, query performance will be suboptimal. The effect is very similar to that from

the ordering of tables in the FROM clause. Consider the following example:

rows than those for BMARK_ID=9, which will return at most only one or two rows.

The following query reverses the order of the conditions in the WHERE clause, resulting in a much

number of rows higher in your WHERE clause.

1.3.4 Problem 4: Using the ORDER BY Indexand not the WHERE Index

Index_1 UNIQUE (ACCOUNT_NO)
Index_2 (ACCOUNT_CODE)

With the indexes shown, the runtime of this query was 20 minutes. The query was used for a report, which was run by many brokers each day.

We decided to drop index_2 (ACCOUNT CODE), which was no longer required because the ACCOUNT_CODE was the leading column of the new index. The ACCOUNT_NO column was added to the new index to take advantage of the index storing the data in ascending order. Having the ACCOUNT_NO column in the index avoided the need to sort, adding the index in a runtime of under 10 seconds.

1.4 Cost-Based Optimizer Problems and Solutions