Oracle optimizer limitation with bitmap indexes operations

Recently I stumbled with an issue when trying to access quite big tables with bitmap indexes.
I had two big segments (table partitions): one with 1e10+ rows another one an order of magnitude smaller.
Both tables have bitmap indexed column. The column have quite a big amount of repeatable values (~10000 rows per key) so corresponding bitmap index segments were pretty small 1. I don’t need any other column from the table
2. and I only need distinct values of those keys (not the amount of the corresponding rows).

But I’ve got unexpected result: after dozens and dozen minutes of execution I had to stop the query as it had produced ~0.5TB of temp on BITMAP CONVERSION TO ROWIDS operation.

Let me reproduce the issue and the solution with a simplified example.

First of all let’s prepare tables:

DROP TABLE IDXMAIN;
DROP TABLE IDXAUX;

CREATE TABLE IDXMAIN AS SELECT rownum id, trunc(rownum/100)*100 unit_id,rpad('a',1000,'v') filler from dual connect by level <=1e4;

ALTER TABLE IDXMAIN MODIFY unit_id NOT NULL;

CREATE BITMAP INDEX IDXMAIN_IX1 ON IDXMAIN (unit_id);

CREATE TABLE IDXAUX AS
 SELECT id2*mult id2, unit_id*mult unit_id, filler FROM (SELECT rownum id2, trunc(1 + rownum/1000)*100 unit_id,rpad('b',10,'z') filler from dual connect by level <=1e6),
 (select rownum mult from dual connect by level<=10);

ALTER TABLE IDXAUX MODIFY unit_id NOT NULL;

CREATE BITMAP INDEX IDXAUX_IX1 ON IDXAUX(unit_id);

exec dbms_stats.gather_table_stats(NULL, 'IDXMAIN');

exec dbms_stats.gather_table_stats(NULL, 'IDXAUX');

so I prepared 2 tables with 1e4 and 1e7 records, both of them have unit_id column with 100 records per key.

let’s start with a simplest case:

SQL_ID	ckpbc1z9cgw95, child number 0
-------------------------------------
select distinct unit_id from IDXMAIN m WHERE EXISTS (select 1 from
IDXAUX a WHERE a.unit_id=m.unit_id)

Plan hash value: 1340588278

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	     |	    1 |        |       |  2062 (100)|	       |    100 |00:00:55.51 |	  2281 |       |       |	  |
|   1 |  HASH UNIQUE		       |	     |	    1 |    101 |   707 |  2062	 (2)| 00:00:42 |    100 |00:00:55.51 |	  2281 |  1518K|  1518K| 1360K (0)|
|*  2 |   HASH JOIN SEMI	       |	     |	    1 |   9900 | 69300 |  2061	 (2)| 00:00:42 |   9901 |00:00:55.49 |	  2281 |  1594K|  1594K| 2220K (0)|
|   3 |    BITMAP CONVERSION TO ROWIDS |	     |	    1 |  10000 | 30000 |     1	 (0)| 00:00:01 |  10000 |00:00:00.01 |	     3 |       |       |	  |
|   4 |     BITMAP INDEX FAST FULL SCAN| IDXMAIN_IX1 |	    1 |        |       |	    |	       |    101 |00:00:00.01 |	     3 |       |       |	  |
|   5 |    BITMAP CONVERSION TO ROWIDS |	     |	    1 |     10M|    38M|  2037	 (0)| 00:00:41 |     10M|00:00:14.06 |	  2278 |       |       |	  |
|   6 |     BITMAP INDEX FAST FULL SCAN| IDXAUX_IX1  |	    1 |        |       |	    |	       |   6426 |00:00:00.02 |	  2278 |       |       |	  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

I see 2 “BITMAP CONVERSION TO ROWIDS” operations which are unnecessary I believe
as finally I need only distinct unit_id
It seems optimizer put HASH UNIQUE operation above HASH SEMI-JOIN itself(looks like missing of some useful transformation). OK I appreciate optimizer’s hard work and decided to help it a bit.

SQL_ID	0n848w015ck2m, child number 0
-------------------------------------
select * FROM (select distinct unit_id from IDXMAIN ) m WHERE EXISTS
(select 1 from IDXAUX a WHERE a.unit_id=m.unit_id)

Plan hash value: 2273114039

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name	     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |	     |	    1 |        |       |   204 (100)|	       |    100 |00:00:00.01 |	   213 |       |       |	  |
|   1 |  NESTED LOOPS SEMI	       |	     |	    1 |    100 |  1700 |   204	 (1)| 00:00:05 |    100 |00:00:00.01 |	   213 |       |       |	  |
|   2 |   VIEW			       |	     |	    1 |    101 |  1313 |     2	(50)| 00:00:01 |    101 |00:00:00.01 |	     3 |       |       |	  |
|   3 |    HASH UNIQUE		       |	     |	    1 |    101 |   303 |     2	(50)| 00:00:01 |    101 |00:00:00.01 |	     3 |  1518K|  1518K| 1392K (0)|
|   4 |     BITMAP INDEX FAST FULL SCAN| IDXMAIN_IX1 |	    1 |  10000 | 30000 |     1	 (0)| 00:00:01 |    101 |00:00:00.01 |	     3 |       |       |	  |
|   5 |   BITMAP CONVERSION TO ROWIDS  |	     |	  101 |   9900K|    37M|   204	 (1)| 00:00:05 |    100 |00:00:00.01 |	   210 |       |       |	  |
|*  6 |    BITMAP INDEX SINGLE VALUE   | IDXAUX_IX1  |	  101 |        |       |	    |	       |    100 |00:00:00.01 |	   210 |       |       |	  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Well we get rid of one of BITMAP CONVERSION TO ROWIDS,
However on this phase I would expect optimizer guess that the second BITMAP CONVERSION TO ROWIDS is unnecessary, we have a semi join, right? So we need to check only a single value and don’t need to know how many exact row would join.
But it seems again some optimizer limitation.

Let’s try our trick (extract a distinct part in inline view) again:

SQL_ID	9kwurxu63zz82, child number 0
-------------------------------------
select /*+ LEADING(m) USE_HASH(a@i) */ distinct unit_id FROM (select
/*+ no_merge */ distinct unit_id from IDXMAIN) m where unit_id IN
(SELECT /*+ qb_name(i) no_merge */ distinct unit_id from IDXAUX a)

Plan hash value: 2793925139

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			| Name	      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time	| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|	      |      1 |	|	|  2066 (100)|		|    100 |00:00:54.40 |    2281 |	|	|	   |
|   1 |  HASH UNIQUE			|	      |      1 |    101 |   707 |  2066   (2)| 00:00:42 |    100 |00:00:54.40 |    2281 |  1518K|  1518K| 1363K (0)|
|*  2 |   HASH JOIN			|	      |      1 |    203K|  1391K|  2062   (2)| 00:00:42 |    290K|00:00:53.95 |    2281 |  1594K|  1594K| 1555K (0)|
|   3 |    VIEW 			|	      |      1 |    101 |   303 |     2  (50)| 00:00:01 |    101 |00:00:00.01 |       3 |	|	|	   |
|   4 |     HASH UNIQUE 		|	      |      1 |    101 |   303 |     2  (50)| 00:00:01 |    101 |00:00:00.01 |       3 |  1518K|  1518K| 1360K (0)|
|   5 |      BITMAP INDEX FAST FULL SCAN| IDXMAIN_IX1 |      1 |  10000 | 30000 |     1   (0)| 00:00:01 |    101 |00:00:00.01 |       3 |	|	|	   |
|   6 |    BITMAP CONVERSION TO ROWIDS	|	      |      1 |     10M|    38M|  2037   (0)| 00:00:41 |     10M|00:00:13.58 |    2278 |	|	|	   |
|   7 |     BITMAP INDEX FAST FULL SCAN | IDXAUX_IX1  |      1 |	|	|	     |		|   6426 |00:00:00.02 |    2278 |	|	|	   |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

but the trick doesn’t work

and finally …

Advertisements

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