Mything 2

June 2023: This was a note I wrote in 2011 in answer to a question about 11g – but a quick test on 23c shows that the limitation it describes is still present in 23c.

It’s about time I wrote a sequel to Mything in Action – and funnily enough it’s also ended up as an article on bitmap indexes. It starts with a thread on the OTN database forum that prompted me to run up a quick test to examine something that turned out to be a limitation in the optimizer. The problem was that the optimizer didn’t do a “bitmap and” between two indexes when it was obviously a reasonable – possibly even good – idea. Here’s some sample code to demonstrate the issue:

rem
rem     Script:         bitmap_force.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2011
rem

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 1e4
)
select
	mod(rownum-1,10)	c1,
	mod(rownum-1,100)	c2,
	mod(rownum-1,101)	c3,
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1e6 -- > hint to avoid WordPress format issue
;

-- stats collection goes here.

create bitmap index t1_b1b2 on t1(c1,c2);
create bitmap index t1_b1b3 on t1(c1,c3);

This was a reasonable model of the situation described in the original posting; and here’s the critical query with the surprise execution path:

select
	/*+
		index_combine(t1 t1_b1b2 t1_b1b3)
	*/
	*
from
	t1
where
	c1 = 5
and	c2 = 50
and	c3 = 50
;

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |   231 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |   231 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE | T1_B1B2 |       |       |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C3"=50)
   3 - access("C1"=5 AND "C2"=50)

You might look at the query and the indexing and decide (as I did) that Oracle ought to be able to manage a “bitmap index single value” on both the indexes, then do a “bitmap and” to minimize the work – something like:

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |     6 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |     6 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |       |
|   3 |    BITMAP AND                |         |       |       |       |
|*  4 |     BITMAP INDEX SINGLE VALUE| T1_B1B2 |       |       |       |
|*  5 |     BITMAP INDEX SINGLE VALUE| T1_B1B3 |       |       |       |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"=5 AND "C2"=50)
   5 - access("C1"=5 AND "C3"=50)

But it doesn’t – and there’s a clue about why not in the “Predicate Information”. To create this plan the optimizer would have to duplicate an existing predicate (c1 = 5) so that it could find the second index after selecting the first one. There’s no reason, of course, why the optimizer code couldn’t do this but at present it just doesn’t. Perhaps this is another opportunity for thinking about manual optimisation strategies – or perhaps ensuring that you’ve created the right set of indexes.

You might notice, of course, that Oracle seems to have ignored my index_combine() hint. Oracle doesn’t ignore hints, of course (apart from cases suffering from problems with syntax, legality, or bugs) but remember that index_combine() is only supplying a list of indexes that Oracle should consider, it doesn’t require the optimizer to use every index in the list. In this case, of course, the hint also has an error because it’s naming an index that can’t be used.

Anyway, I replied to the thread with a note suggesting that there was a gap in the optimizer’s strategies, specifically:

I’ve just spent a few minutes playing with a data set where this (c1,c2) (c1,c3) type of index combination is obviously a good idea – and can’t get the bitmap_tree() hint to force the path. I think this means there’s a hole in the optimizer’s legal strategies that you might have to fill by other methods.

Here’s where the mything starts. The OP replied as follows:

Do I right understand that it is impossible to combine bitmap non-one-column indexes?

ABSOLUTELY NOT! The OP has jumped from the particular to the general; fortunately he asked the question rather than silently making the assumption then spreading misinformation. Of course I was at fault because I could have pointed out explicitly that the pattern was dependent on the two indexes starting with the same column – but is it so hard to interpret patterns.

What’s more annoying is that the OP was already using a model to test what happened – would it have been so hard for him to try a few different combinations of indexes – switching the column order on both indexes. For example what happens if the indexes are (c2, c1) (c3,c1) ?

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    10 |  1250 |    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |    12   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |            |          |
|   3 |    BITMAP AND                |         |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| T1_B2B1 |       |       |            |          |
|   5 |     BITMAP MERGE             |         |       |       |            |          |
|*  6 |      BITMAP INDEX RANGE SCAN | T1_B3B1 |       |       |            |          |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C2"=50 AND "C1"=5)
   6 - access("C3"=50)
       filter("C3"=50)

See how easy it is to show that the optimizer can combine multi-column bitmap indexes; but we can observe at the same time that it doesn’t make “perfect” use of the (c3, c1) index. Oracle still does excess work in the index because it hasn’t repeated the use of the c1 predicate.

Maxim:

When you see some unexpected behaviour the least you should do when investigating it is to ask yourself: “in what way might this be a special case?”