We have following query, which taking longtime to return the results.
SELECT object_id
FROM object_table
WHERE create_time > :B1
AND new_object = :B2
AND object_class IN (:B3, :B4)
ORDER BY new_object ASC;
Table Details:
-------------
Table: object_table
No of records: 900,000
Table Size: 2 Gb
Indexe Details: (four different single column indexes)
------------------------------------------------------
CREATETIME_IX on (create_time)
NEW_OBJECT_IDX on (new_object)
OBJECT_CLASS_IDX on (object_class))
OBJECTID_IDX on (object_id)
The above query was running long and using the full tablescan. The execution plan was shown below:
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 10885 (100)|
| 1 |
SORT ORDER BY | | 3 |
54 | 10885 (1)|
| 2 |
TABLE ACCESS FULL| OBJECT_TABLE
| 3 | 54 | 10884
(1)|
----------------------------------------------------------------------------
The query will always return very less number of rows (10-20 rows), So scanning whole table(900k rows) to get 10-20 rows was not a optimal access method.
How to eliminate the full table scan for above query?
Ans: INDEX JOIN or INDX COMBINE(Bitmap Conversion)
Index Join:
The SELECT statement involved in four different columns, and all the columns have separate indexes.
So If we join all the indexes we can eliminate the full tablescan. This can be done by using the INDEX_JOIN hint.
Index Joins will be used to avoid the expensive table scans by joining two or more indexes belonging to the same table.
Oracle will join the indexes only if all the columns referenced in the query must be covered in the indexes itself, So it can avoid the table visits.
Index joins will be used only with HASH joins but can use any available index access method.
I forced the query to use index join by using the hint INDEX_JOIN(table_name) , Execution plan was shown below:
Since by using index join, oracle joining the four indexes using the HASH joins.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10694 (100)|
| 1 | SORT ORDER BY | | 3 | 54 | 10694 (1)|
| 2 | VIEW | index$_join$_001 | 3 | 54 | 10693 (1)|
| 3 | HASH JOIN | | | | |
| 4 | HASH JOIN | | | | |
| 5 | HASH JOIN | | | | |
| 6 | INDEX RANGE SCAN | NEW_OBJECT_IDX | 3 | 54 | 688 (1)|
| 7 | INDEX RANGE SCAN | CREATETIME_IX | 3 | 54 | 1610 (1)|
| 8 | INLIST ITERATOR | | | | |
| 9 | INDEX RANGE SCAN | OBJECT_CLASS_IDX | 3 | 54 | 2670 (1)|
| 10 | INDEX FAST FULL SCAN| OBJECTID_IDX | 3 | 54 | 6670 (1)|
--------------------------------------------------------------------------------------
The query with index_join hint was running faster than the original one, but response time was still inacceptable to application.
So I have one more option to try, i.e BITMAP CONVERSION OF BTREE INDEXES
Bitmap Conversion for the B-tree Indexes:
Now look at the following two indexes:
CREATETIME_IX on (NextRunTime)
NEW_OBJECT_IDX on (SI_RUNNABLE_OBJECT)
So we have two single column indexes on the two of the columns referenced in the WHERE claue, So oracle can combine those indexes and make use of it.
Since we have another column was referenced in the table, it needs a visit to table if we use this method.
We can use following hint to force BITMAP CONVERSION plan of BTREE index: INDEX_COMBINE(table_name index_name1 index_name2)
.
It builds in-memory BITMAP IDEX by using the those two BTREE indexes and based on the rowid it will access the table and applies the filter condition.
The execution plan was shown below:
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 12945|
| 1 |
SORT ORDER BY
| | 2 |
36 | 12945|
| 2 |
TABLE ACCESS BY INDEX ROWID |
CMS_INFOOBJECTS6 | 2 |
36 | 12944|
| 3 |
BITMAP CONVERSION TO ROWIDS
| | |
| |
| 4 |
BITMAP AND | | |
| |
| 5 |
BITMAP CONVERSION FROM ROWIDS| | |
| |
| 6 |
INDEX RANGE SCAN |
NEW_OBJECT_IDX | | 814 | |
| 7 |
BITMAP CONVERSION FROM ROWIDS| | |
| |
| 8 |
SORT ORDER BY | | |
| |
| 9 |
INDEX RANGE SCAN |
CREATETIME_IX | | | 736
|
----------------------------------------------------------------------------------------
Even though it shows more cost than other two execution plan, The response time was very low compared to other two methods and it solved the response
time problem.
So it looks like a bug in the optimizer while estimating the cost of BITMAP CONVERSION of BTREE INDEXES.
