Here the following is an example SQL shows you that select the maximum emp_address which is not indexed in the EMPLOYEE table with 3 million records, the emp_grade is an indexed column. select max(emp_address) from employee a where emp_grade<4000 As 80% of the EMPLOYEE table’s records will be retrieved to examine the maximum emp_address string. The query plan of this SQL shows a Table Access Full on EMPLOYEE table is reasonable. How many ways to build an index to improve this SQL? Although it is simple SQL, there are still 3 ways to build an index to improve this SQL, the following are the possible indexes that can be built for the SQL, the first one is a single column index and the 2 and 3 are the composite index with a different order. 1. EMP_ADDRESS 2. EMP_GRADE, EMP_ADDRESS 3. EMP_ADDRESS, EMP_GRADE Most people may use the EMP_ADDRESS as the first choice to improve this SQL, let's see what the query plan is if we build a virtual index for the EMP_ADDRESS column in the following, you can see the estimated cost is reduced by almost half, but this query plan is finally not being used after the physical index is built for benchmarking due to actual statistics is collected. The following query shows the EMP_ADDRESS index is not used and the query plan is the same as the original SQL without any new index built. Let’s try the second composite index (EMP_GRADE, EMP_ADDRESS), the new query plan shows an Index Fast Full Scan of this index, it is a reasonable plan which no table’s data is needed to retrieve. So, the execution time is reduced from 16.83 seconds to 3.89 seconds. Let’s test the last composite index (EMP_ADDRESS, EMP_GRADE) that EMP_ADDRESS is placed as the first column in the composite index, it creates a new query plan that shows an extra FIRST ROW operation for the INDEX FULL SCAN (MIN/MAX), it highly reduces the execution time from 16.83 seconds to 0.08 seconds. So, indexing sometimes is an art that needs you to pay more attention to it, some potential solutions may perform excess your expectation. The best index solution is now more than 200 times better than the original SQL without index, this kind of index recommendation can be achieved by Tosska SQL Tuning Expert for Oracle automatically. https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracwhichich/
