Thanks for the question, Christian.

Asked: April 23, 2025 - 9:22 am UTC

Last updated: May 01, 2025 - 2:10 am UTC

Version: 19.24.0.0.0

Viewed 1000+ times

You Asked

Hello Chris. Hello Connor,

I’m struggling with a performance issue …

We have a single big table (167 million of rows, 118 columns). For ad hoc queries we have created a simple Apex application with 15 most important columns as filters. To avoid long running queries we start a simple count(*) query bevor running the main query. Depending of a result of our count(*) we warn or even force the end user for better filtering.

select count(*)
from big_table
where filter_column_1 = filter_value_1
and filter_column_2 = filter_value_2
and filter_column_3 between filter_value_3 and filter_value_4;

To support the count(*) query I have created a bitmap index for each filter column. It works fine! Oracle combines the relevant bitmap indexes with a BITMAP AND or BITMAP MERGE. The response time is excellent, maximum a few seconds, often lower than 1 second. Works as expected and I’m happy!

But: sometimes the optimizer decides to quit the path of bitmap processing and converts all bitmap indexes with BITMAP CONVERSION TO ROWIDS and uses then HASH JOINs. In this case the response time of the count(*) query is much worse, sometimes even minutes!

My questions:
• Why does the optimizer use BITMAP CONVERSION TO ROWIDS? My feeling is, that it is more probably if the where clause is complex (many filters in use) or the count(*) delivers a big number.
• Are there any SQL-hints to avoid BITMAP CONVERSION TO ROWIDS?
• Are there any tips for my count(*) query ( WITH clauses, subqueries, … ) to avoid BITMAP CONVERSION TO ROWIDS?
The DB-Version is 19.24.0.0.0, the big table and all indexes have fresh statistics.

Thank you in advance!

Christian

and Connor said...

Can you please provide plan output for your queries as Comments

Ideally if you could run

select /*+ gather_plan_statistics */ count(*)
from big_table
where filter_column_1 = filter_value_1
and filter_column_2 = filter_value_2
and filter_column_3 between filter_value_3 and filter_value_4;

select * from dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST')

so we can see what we're dealing with

Rating

(3 ratings)

Comments

Execution plans

Christian Schwitalla, April 24, 2025 - 12:03 pm UTC

Hello Connor,

   this is the "good" execution plan with BITMAP MERGE and BITMAP AND:
   
- COUNT(*) = 57 032 000
- time elapsed = 2 sec

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) 
FROM big_table z
WHERE z.filter_column_1 = 9240
  AND z.filter_column_2 BETWEEN TO_DATE( '10.12.2023','DD.MM.YYYY') AND TO_DATE( '20.12.2023','DD.MM.YYYY')
  AND z.filter_column_3 = 'BEE';
 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                                | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                     |      1 |        |   124K(100)|      1 |00:00:01.71 |   18731 |       |       |          |
|   1 |  SORT AGGREGATE               |                                     |      1 |      1 |            |      1 |00:00:01.71 |   18731 |       |       |          |
|*  2 |   FILTER                      |                                     |      1 |        |            |   1035 |00:00:01.68 |   18731 |       |       |          |
|   3 |    BITMAP CONVERSION COUNT    |                                     |      1 |     67M|   124K  (1)|   1035 |00:00:01.68 |   18731 |       |       |          |
|   4 |     BITMAP AND                |                                     |      1 |        |            |   1035 |00:00:01.60 |   18731 |       |       |          |
|*  5 |      BITMAP INDEX SINGLE VALUE| BIG_TABLE_FILTER_COLUMN_3_B_IND  |      1 |        |            |  13563 |00:00:00.02 |    6784 |       |       |          |
|*  6 |      BITMAP INDEX SINGLE VALUE| BIG_TABLE_FILTER_COLUMN_1_B_IND     |      1 |        |            |  11341 |00:00:00.01 |    5673 |       |       |          |
|   7 |      BITMAP MERGE             |                                     |      1 |        |            |   1298 |00:00:00.99 |    6274 |  1024K|   512K|   44M (0)|
|*  8 |       BITMAP INDEX RANGE SCAN | BIG_TABLE_FILTER_COLUMN_2_B_IND     |      1 |        |            |  12542 |00:00:00.02 |    6274 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

   And now the same statement with two additional filters (filter_column_4 and filter_column_5). Now we have the "bad" execution plan with BITMAP CONVERSION TO ROWIDS and HASH JOIN:

- COUNT(*) = 3 296 000
- time elapsed = 110 sec

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*) 
FROM big_table z
WHERE z.filter_column_1 = 9240
  AND z.filter_column_2 BETWEEN TO_DATE( '10.12.2023','DD.MM.YYYY') AND TO_DATE( '20.12.2023','DD.MM.YYYY')
  AND z.filter_column_3 = 'BEE'
  AND z.filter_column_4 > 10000
  AND z.filter_column_5 = 'Example_1';
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                                | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                     |      1 |        |   849K(100)|      1 |00:01:14.46 |     103K|    392K|    392K|       |       |          |         |
|   1 |  SORT AGGREGATE                    |                                     |      1 |      1 |            |      1 |00:01:14.46 |     103K|    392K|    392K|       |       |          |         |
|*  2 |   FILTER                           |                                     |      1 |        |            |   3296K|00:01:20.52 |     103K|    392K|    392K|       |       |          |         |
|*  3 |    VIEW                            | index$_join$_001                    |      1 |   3263K|   849K  (1)|   3296K|00:01:20.24 |     103K|    392K|    392K|       |       |          |         |
|*  4 |     HASH JOIN                      |                                     |      1 |        |            |   3296K|00:01:19.74 |     103K|    392K|    392K|   420M|    23M|  191M (1)|     433M|
|*  5 |      HASH JOIN                     |                                     |      1 |        |            |   5052K|00:00:57.31 |   21595 |    339K|    339K|   513M|    25M|  125M (1)|     718M|
|*  6 |       HASH JOIN                    |                                     |      1 |        |            |   6946K|00:00:38.84 |   15321 |    251K|    251K|   713M|    26M|   32M (1)|     832M|
|*  7 |        HASH JOIN                   |                                     |      1 |        |            |     10M|00:00:25.40 |    9647 |    147K|    147K|   642M|    29M|   32M (1)|    1194M|
|   8 |         BITMAP CONVERSION TO ROWIDS|                                     |      1 |   3263K|  2040   (0)|     10M|00:00:00.56 |    2863 |      0 |      0 |       |       |          |         |
|*  9 |          BITMAP INDEX SINGLE VALUE | BIG_TABLE_FILTER_COLUMN_5_B_IND     |      1 |        |            |   5721 |00:00:00.02 |    2863 |      0 |      0 |       |       |          |         |
|  10 |         BITMAP CONVERSION TO ROWIDS|                                     |      1 |   3263K|  6708   (1)|    166M|00:00:07.30 |    6784 |      0 |      0 |       |       |          |         |
|* 11 |          BITMAP INDEX SINGLE VALUE | BIG_TABLE_FILTER_COLUMN_3_B_IND   |      1 |        |            |  13563 |00:00:00.06 |    6784 |      0 |      0 |       |       |          |         |
|  12 |        BITMAP CONVERSION TO ROWIDS |                                     |      1 |   3263K|  6802   (1)|     81M|00:00:03.60 |    5674 |      0 |      0 |       |       |          |         |
|* 13 |         BITMAP INDEX SINGLE VALUE  | BIG_TABLE_FILTER_COLUMN_1_B_IND     |      1 |        |            |  11341 |00:00:00.01 |    5674 |      0 |      0 |       |       |          |         |
|  14 |       BITMAP CONVERSION TO ROWIDS  |                                     |      1 |   3263K|   111K  (1)|    137M|00:00:05.84 |    6274 |      0 |      0 |       |       |          |         |
|* 15 |        BITMAP INDEX RANGE SCAN     | BIG_TABLE_FILTER_COLUMN_2_B_IND     |      1 |        |            |  12542 |00:00:00.06 |    6274 |      0 |      0 |       |       |          |         |
|  16 |      BITMAP CONVERSION TO ROWIDS   |                                     |      1 |   3263K|    17M (30)|    128M|00:00:07.42 |   81781 |      0 |      0 |       |       |          |         |
|* 17 |       BITMAP INDEX RANGE SCAN      | BIG_TABLE_FILTER_COLUMN_4_B_IND     |      1 |        |            |    163K|00:00:00.20 |   81781 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
   I hope it helps ...
 
Christian

April 28, 2025 - 8:27 am UTC

Thanks for the additional info.

This looks like a stats issue more than anything else to me.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                                | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   8 |         BITMAP CONVERSION TO ROWIDS|                                     |      1 |   3263K|  2040   (0)|     10M|00:00:00.56 |    2863 |      0 |      0 |       |       |          |         |

|  10 |         BITMAP CONVERSION TO ROWIDS|                                     |      1 |   3263K|  6708   (1)|    166M|00:00:07.30 |    6784 |      0 |      0 |       |       |          |         |

We were expecting to carry a few million rows (E-rows) into these phases, but we ended up with 10M and 166M respectively.

So that is now going to be huge amount of "stuff" we will be carrying into the subequent joins (hence the performance cost, along with the nice big chunk of temp space used).

You might need a decent frequency histogram there.

There other thing of note is - bitmaps are awesome for equality/inequality predicates. If your date and numeric columns are going to used more for *range* predicates, it might be worth reviewing whether there is truly a benefit of bitmaps on these. They might be better off with either a standard index or no index at all.

Execution plans..

Rajeshwaran Jeyabal, April 27, 2025 - 4:47 am UTC

when you post the code, make sure to wrap it inside the "code" tag for fixed-width formatting and better readability.

when you got a filter on indexed column having range based predicates , we could see HASH join appear in the plan.

demo@ATP19C> create table t nologging as select * from big_table;

Table created.

demo@ATP19C>
demo@ATP19C> create bitmap index t_idx1 on t(owner);

Index created.

demo@ATP19C> create bitmap index t_idx2 on t(object_type);

Index created.

demo@ATP19C> create bitmap index t_idx3 on t(created);

Index created.

demo@ATP19C> create bitmap index t_idx4 on t(object_id);

Index created.

demo@ATP19C> set autotrace traceonly exp statistics
demo@ATP19C> select count(*)
  2  from t
  3  where owner ='SYS'
  4  and object_type ='TABLE';


Execution Plan
----------------------------------------------------------
Plan hash value: 423269016

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    19 |   124   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    19 |            |          |
|   2 |   BITMAP CONVERSION COUNT    |        | 64480 |  1196K|   124   (0)| 00:00:01 |
|   3 |    BITMAP AND                |        |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| T_IDX2 |       |       |            |          |
|*  5 |     BITMAP INDEX SINGLE VALUE| T_IDX1 |       |       |            |          |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("OBJECT_TYPE"='TABLE')
   5 - access("OWNER"='SYS')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        212  consistent gets
          0  physical reads
          0  redo size
        289  bytes sent via SQL*Net to client
         37  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

demo@ATP19C> select count(*)
  2  from t
  3  where owner ='SYS'
  4  and object_type ='TABLE'
  5  and created between to_date('01-jan-2025','dd-mon-yyyy') and sysdate
  6  and object_id between 5 and 55000;


Execution Plan
----------------------------------------------------------
Plan hash value: 2146634856

------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |     1 |    32 |  3834   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |                  |     1 |    32 |            |          |
|*  2 |   FILTER                          |                  |       |       |            |          |
|*  3 |    VIEW                           | index$_join$_001 |  1131 | 36192 |  3834   (2)| 00:00:01 |
|*  4 |     HASH JOIN                     |                  |       |       |            |          |
|*  5 |      HASH JOIN                    |                  |       |       |            |          |
|*  6 |       HASH JOIN                   |                  |       |       |            |          |
|   7 |        BITMAP CONVERSION TO ROWIDS|                  |  1131 | 36192 |    44   (3)| 00:00:01 |
|*  8 |         BITMAP INDEX RANGE SCAN   | T_IDX3           |       |       |            |          |
|   9 |        BITMAP CONVERSION TO ROWIDS|                  |  1131 | 36192 |    62   (0)| 00:00:01 |
|* 10 |         BITMAP INDEX SINGLE VALUE | T_IDX2           |       |       |            |          |
|  11 |       BITMAP CONVERSION TO ROWIDS |                  |  1131 | 36192 |   146   (0)| 00:00:01 |
|* 12 |        BITMAP INDEX SINGLE VALUE  | T_IDX1           |       |       |            |          |
|  13 |      BITMAP CONVERSION TO ROWIDS  |                  |  1131 | 36192 | 10030   (1)| 00:00:01 |
|* 14 |       BITMAP INDEX RANGE SCAN     | T_IDX4           |       |       |            |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYSDATE@!>=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - filter("OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE(' 2025-01-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "OWNER"='SYS' AND "OBJECT_ID"<=55000 AND "OBJECT_ID">=5 AND
              "CREATED"<=SYSDATE@!)
   4 - access(ROWID=ROWID)
   5 - access(ROWID=ROWID)
   6 - access(ROWID=ROWID)
   8 - access("CREATED">=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "CREATED"<=SYSDATE@!)
  10 - access("OBJECT_TYPE"='TABLE')
  12 - access("OWNER"='SYS')
  14 - access("OBJECT_ID">=5 AND "OBJECT_ID"<=55000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2246  consistent gets
          0  physical reads
          0  redo size
        286  bytes sent via SQL*Net to client
         37  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

and you dont need to have a scalar index on every single predicates. sometimes you need to leverage, composite bitmap index for optimization.

demo@ATP19C> drop index t_idx3 ;

Index dropped.

demo@ATP19C> drop index t_idx4 ;

Index dropped.

demo@ATP19C>
demo@ATP19C> create bitmap index t_idx3 on t(object_type,created,object_id);

Index created.

demo@ATP19C> select count(*)
  2  from t
  3  where owner ='SYS'
  4  and object_type ='TABLE'
  5  and created between to_date('01-jan-2025','dd-mon-yyyy') and sysdate
  6  and object_id between 5 and 55000;


Execution Plan
----------------------------------------------------------
Plan hash value: 54080540

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    32 |    38   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |        |     1 |    32 |            |          |
|*  2 |   FILTER                      |        |       |       |            |          |
|   3 |    BITMAP CONVERSION COUNT    |        |  1131 | 36192 |    38   (0)| 00:00:01 |
|   4 |     BITMAP AND                |        |       |       |            |          |
|   5 |      BITMAP MERGE             |        |       |       |            |          |
|*  6 |       BITMAP INDEX RANGE SCAN | T_IDX3 |       |       |            |          |
|*  7 |      BITMAP INDEX SINGLE VALUE| T_IDX1 |       |       |            |          |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYSDATE@!>=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   6 - access("OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE(' 2025-01-01
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "OBJECT_ID">=5 AND
              "CREATED"<=SYSDATE@! AND "OBJECT_ID"<=55000)
       filter("OBJECT_ID"<=55000 AND "OBJECT_ID">=5 AND "CREATED"<=SYSDATE@!
              AND "CREATED">=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "OBJECT_TYPE"='TABLE')
   7 - access("OWNER"='SYS')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
        286  bytes sent via SQL*Net to client
         37  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Solution found

Christian Schwitalla, April 30, 2025 - 2:50 pm UTC

Hello Connor, Hello Rajeshwaran,

thank you very much for your input!

Once more the starting point:
• a big table with 170 million rows, 118 columns
• an Apex app for ad hoc queries
• a ‘pre-query’ COUNT(*) check, how big the result set will be
• 15 filter columns, 2 of them mandatory
• a bitmap index for each filter column

My performance issue was: sometimes Oracle transforms the bitmap indexes (BITMAP CONVERSION TO ROWIDS) and then the performance of the COUNT(*) suffers a lot …

I think, I have found a solution:
• list partitioning of the big table with the first mandatory filter column as partition key
• a composite bitmap index for the two mandatory filter columns
• a scalar bitmap index for other filter columns
• all bitmap indexes are local
• fresh statistics for the table and for all indexes

Now my issue totally disappears. :-) Oracle doesn’t use the BITMAP CONVERSION TO ROWIDS anymore. Instead of that Oracle always uses BITMAP MERGE, BITMAP AND, BITMAP CONVERSION COUNT. My COUNT(*) needs only a few seconds for the result, even if I’m using the range based filtering (BETWEEN).

Now I will try a more complex partitioning: List Partition for the first mandatory filter column with Range Sub-Partition for the second mandatory filter column. But even with the current simple list partitioning I’m quite happy.

My conclusion: table partitioning can improve the usage of the bitmap indexes.

Thanks
Christian

May 01, 2025 - 2:10 am UTC

Glad you made progress.

I'd stress - be careful about broad statements like "table partitioning can improve the usage of the bitmap indexes", because its easy to fall into the trap of making it a "rule"

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Execution plans

Execution plans..

Solution found

More to Explore

Performance

Bitmap indexes and BITMAP CONVERSION TO ROWIDS

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Execution plans

Execution plans..

Solution found

More to Explore

Performance