Hi folks,
In two words, I am trying to push the Optimizer to choose a better plan without success..
Actually, what I need is
a star transformation, which uses the bitmap minus operation .
Here it is the test case:
create table fact (
a_dim_key number not null
, b_dim_key number not null
);
insert into fact
with generator as (select rownum id from all_objects where rownum <= 1000)
select case when round(dbms_random.value(1, 2)) = 1
then 1
else round(dbms_random.value(2, 10))
end a_dim_key
, case when round(dbms_random.value(1, 2)) = 1
then 1
else round(dbms_random.value(2, 10))
end b_dim_key
from generator g1
, generator g2
where rownum <= 1000000
;
The "fact" table holds 1M records. It is a sample of a fact table from the DWH world. It has only two dimension keys and no measures -- I told you, really, a simple one :) The data inside are skewed, so 50% of the dimension keys in both columns have dim_keys = 1 and the rest are in the interval [2, 10].
Of course, we will need bitmap indexes and statistics collection:
create bitmap index fact__dim_key_a__bix on fact (dim_key_a);
create bitmap index fact__dim_key_b__bix on fact (dim_key_b);
begin
dbms_stats.gather_table_stats(
ownname => user
,tabname => 'FACT'
,partname => null
,estimate_percent => 100
,block_sample => false
,method_opt => 'for all columns size 1'
,granularity => 'ALL'
,cascade => true
,no_invalidate => false
,force => true
);
end;
Now, lets take a look on a particular select statement, which demonstrates the power of the
bitmap minus operation I am struggling to achieve in combination with the star transformation.
select count(*)
from fact
where a_dim_key = 1
and NOT b_dim_key = 1
The actual plan is:
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 6 | | 1 |00:00:00.01 | 44 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 90000 | 527K| 24 (0)| 5 |00:00:00.01 | 44 |
| 3 | BITMAP MINUS | | 1 | | | | 5 |00:00:00.01 | 44 |
|* 4 | BITMAP INDEX SINGLE VALUE| FACT__A_DIM_KEY__BIX | 1 | | | | 41 |00:00:00.01 | 22 |
|* 5 | BITMAP INDEX SINGLE VALUE| FACT__B_DIM_KEY__BIX | 1 | | | | 41 |00:00:00.01 | 22 |
------------------------------------------------------------------------------------------------------------------------------------
The Oracle Optimizer correctly identifies that it can "reverse" the second bitmap and "bitmap minus" only 2 bitmaps gathered via "BITMAP INDEX SINGLE VALUE" !
So far, so good...
Now, lets introduce the star transformation and see what happens.
First, dimensions creation:
----
-- DIM_A
----
create table dim_a (dim_key number, bk_a number not null, attr_a number not null);
alter table dim_a add constraint dim_a_pk primary key (dim_key);
alter table dim_a add constraint dim_a_uk unique (bk_a);
insert into dim_a
select rownum
, rownum + 100
, rownum + 100
from all_objects
where rownum <= 10
;
begin
dbms_stats.gather_table_stats(
ownname => user
,tabname => 'DIM_A'
,partname => null
,estimate_percent => 100
,block_sample => false
,method_opt => 'for all columns size 1'
,degree => 4
,granularity => 'ALL'
,cascade => true
,no_invalidate => false
,force => true
);
end;
----
-- DIM_B
----
create table dim_b (dim_key number, bk_b number not null, attr_b number not null);
alter table dim_b add constraint dim_b_pk primary key (dim_key);
alter table dim_b add constraint dim_b_uk unique (bk_b);
insert into dim_b
select rownum
, rownum + 100
, rownum + 100
from all_objects
where rownum <= 10
;
begin
dbms_stats.gather_table_stats(
ownname => user
,tabname => 'DIM_B'
,partname => null
,estimate_percent => 100
,block_sample => false
,method_opt => 'for all columns size 1'
,degree => 4
,granularity => 'ALL'
,cascade => true
,no_invalidate => false
,force => true
);
end;
Next, the foreign keys:
alter table fact add constraint fact__dim_key_a__fk foreign key (a_dim_key) references dim_a(dim_key);
alter table fact add constraint fact__dim_key_b__fk foreign key (b_dim_key) references dim_b(dim_key);
It is time for the star_transformation. Just to prove that the star transformation is possible at my instance and with these tables/data, I will try it without the NOT condition:
alter session set star_transformation_enabled=temp_disable;
select /*+ star_transformation
*/
count(*)
from fact f
, dim_a da
, dim_b db
where f.a_dim_key = da.dim_key
and da.attr_a = 101
and f.b_dim_key = db.dim_key
and db.attr_b = 101
;
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 6 | | 1 |00:00:00.01 | 58 | | | |
| 2 | BITMAP CONVERSION COUNT | | 1 | 10000 | 60000 | 369 (1)| 5 |00:00:00.01 | 58 | | | |
| 3 | BITMAP AND | | 1 | | | | 5 |00:00:00.01 | 58 | | | |
| 4 | BITMAP MERGE | | 1 | | | | 5 |00:00:00.01 | 29 | 1024K| 512K| 148K (0)|
| 5 | BITMAP KEY ITERATION | | 1 | | | | 41 |00:00:00.01 | 29 | | | |
|* 6 | TABLE ACCESS FULL | DIM_A | 1 | 1 | 7 | 3 (0)| 1 |00:00:00.01 | 7 | | | |
|* 7 | BITMAP INDEX RANGE SCAN| FACT__A_DIM_KEY__BIX | 1 | | | | 41 |00:00:00.01 | 22 | | | |
| 8 | BITMAP MERGE | | 1 | | | | 5 |00:00:00.01 | 29 | 1024K| 512K| 148K (0)|
| 9 | BITMAP KEY ITERATION | | 1 | | | | 41 |00:00:00.01 | 29 | | | |
|* 10 | TABLE ACCESS FULL | DIM_B | 1 | 1 | 7 | 3 (0)| 1 |00:00:00.01 | 7 | | | |
|* 11 | BITMAP INDEX RANGE SCAN| FACT__B_DIM_KEY__BIX | 1 | | | | 41 |00:00:00.01 | 22 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("DA"."ATTR_A"=101)
7 - access("F"."A_DIM_KEY"="DA"."DIM_KEY")
10 - filter("DB"."ATTR_B"=101)
11 - access("F"."B_DIM_KEY"="DB"."DIM_KEY")
Note
-----
- star transformation used for this statement
Looks good!
The question is how to introduce the NOT condition (NOT b_dim_key = 1), so it uses the BITMAP MINUS operation.
I.e. I am interested in all b_dim_keys with values [2,10], but I don't want MERGE of these 9 bitmaps, instead I want that a single REVERSED bitmap "b_dim_key = 1" to be used.
In order to put the NOT condition I had to rewrite the statement like:
select /*+ star_transformation
*/
count(*)
from fact f
, dim_a da
where f.a_dim_key = da.dim_key
and da.attr_a = 101
and NOT f.b_dim_key in (select db.dim_key from dim_b db where db.attr_b = 101)
;
Unfortunately, the star transformation is gone then :(
Note, that the "rewrite" is correct in terms of star transformation because the analog statement (with IN instead of NOT IN) uses star transformation.
I suspect, that the Optimizer when seeing the "NOT .. IN" condition is not able to understand that there is just one row, so I replaced the IN with equality condition:
select /*+ star_transformation
*/
count(*)
from fact f
, dim_a da
where f.a_dim_key = da.dim_key
and da.attr_a = 101
and f.b_dim_key = (select db.dim_key from dim_b db where db.attr_b = 101)
Again, the star is gone :(
Sorry, for this lengthy explanation.
I will appreciate your comments or ideas how to get things done in the better way :)