We have a query like this:
SELECT blob_col, datetime_col
FROM table1
WHERE col1 LIKE 'PROPERTY!PY%!%!52'
AND col2 = :1
ORDER BY table_pk;
(1.) Primay key of the table is table_pk column.
(2.) tab1 has 19,160 rows
(3.) col1 has 19,160 different values (it's like a name of a person)
(4.) This table has 10 indexes.
(5.) However, there is no index for col combination col1 + col2.
(6.) All rows in the table have a value for BLOB_col.
(7.) This table has 37 columns.
Explain plan says that a Full Table Scan will be done with a cost of 3650.
The ADDM report gave this query as a possible candidate for performance improvement with the following recommendations:
Recommendation 3: SQL Tuning
Estimated benefit is .01 active sessions, 4.03% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"5x9aw5925wvq0".
.... SQL STATEMNENT....
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "5x9aw5925wvq0" was executed 6 times and had
an average elapsed time of 2.9 seconds.
I was asked to to see if indexes should be added in order to further improve the query.
Even if a create a new index for col1 + col2, the optimizer will not take it.
Even though all col2 values are unique, it's values are about 100 characters in length. So, the LIKE operator can reduce the result set to only 100s of rows.
So, why is it that the new index for col1 + col2 is not used in this instance by the optimizer?
I see it benefiting. Since all the rows have BLOBs, reading all the tables blocks to get at, say, the 10 rows we want (from the WHERE-CLAUSE), can be avoided by going straight to the blocks which has only our 10 records.
So, we read the index for the col1 + col2, and then read only the DB blocks (with the BLOBs) we want (i.e. the WHERE-CLAUS).
Will it be beneficial to FORCE the use of the index using a hint?
PS: I still have no access to their Tuning Adviser, nor access to run SQL trace and TKPROF. I should be getting this in a few days, hopefully.