otak-otak | favorite evening food

April 21, 2011

BITMAP CONVERSION TO ROWIDS

Filed under: sql tuning — ora62 @ 8:54 am

on 10.2.0.5 AIX

The query is really simple just querying a table filtered by combination of columns, the table has 2 indexes (each 1 column) which is as part of where clause. from the plan, oracle seems to deal with just only cost, ya it is true cost is cheaper than by scanning only 1 index, but IO cost would likely double (scanning 2 indexes, bitmap and operation and than conver bitmap to rowid).

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |     1 |   112 |    66   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | T_4             |     1 |   112 |    66   (2)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                 |       |       |            |          |
|   3 |    BITMAP AND                    |                 |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IDX_1           | 25237 |       |     7   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IDX_0           | 25237 |       |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

here by force to use index 1,

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   112 |   501   (1)| 00:00:07 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_4             |     1 |   112 |   501   (1)| 00:00:07 |
|*  2 |   INDEX RANGE SCAN          | IDX_1           |  3801 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

it seems the cost is higher than bitmap conversion, but from the real execution time the second one is really faster. it seems cost calculation on the first plan is misleading.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: