I use these sources all the time for people who say the same thing.

However, trusted Oracle sources say that 10g bitmap index maintenance algorithms make many of the things you and JL say about single row DML and its effects on bitmaps index quality and system performance no longer "as" true, although duly prefaced with "your mileage may vary so test it out"

On another note, despite having read JL's articles several times, I have never understand the "deterioration/growth" of the bitmap over time based on single row dml. I understood the idea that bitmap efficiency depends on the clustering of values for each bitmap piece which JL suggests is about the size of a block I think. If a single bitmap chunk/block only has 1 unique value in that block, there won't be a huge bitmap, despite the fact that the table overall has many unique values. But the data is arranged in blocks at load time, so unless the data was loaded in order, this "deterioration/growth" would happen regardless of whether it was single row or bulk if the data was loaded randomly. I think you told me before it was something internal to the way the bitmap was organized/built.

I was hoping someone would take the time to test/compare this single row deterioration/growth in 10g vs. 9i and report back. any takers?

it is no longer "as true".  consider, in 9i:


    exec sql create table t ( x int );
    exec sql create bitmap index t_idx on t(x);
    for( i = 0; i < 1000000; i++ )
    {
        exec sql insert into t values ( mod(:i,5) );
        exec sql commit;
    }
    exit(0);




ops$tkyte@ORA9IR2> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................              51
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           2,523
Total Blocks............................           2,688
Total Bytes.............................      22,020,096
Total MBytes............................              21
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................               7
Last Used Ext BlockId...................           4,360
Last Used Block.........................              64
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter index t_idx rebuild;
 
Index altered.
 
ops$tkyte@ORA9IR2> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             103
Total Blocks............................             120
Total Bytes.............................         983,040
Total MBytes............................               0
Unused Blocks...........................               6
Unused Bytes............................          49,152
Last Used Ext FileId....................               7
Last Used Ext BlockId...................           4,488
Last Used Block.........................               2
 
PL/SQL procedure successfully completed.


<b>the index was 22mb with row at a time processing, after the rebuild, down to 950 kb

now the same in 10g:</b>


ops$tkyte@ORA10G> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................              42
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             264
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,672
Last Used Block.........................              64
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> alter index t_idx rebuild;
 
Index altered.
 
ops$tkyte@ORA10G> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             103
Total Blocks............................             120
Total Bytes.............................         983,040
Total MBytes............................               0
Unused Blocks...........................               6
Unused Bytes............................          49,152
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           2,000
Last Used Block.........................               2
 
PL/SQL procedure successfully completed.

<b>3mb -- better than 22mb.

but the concurrency issues are *the same*.  This just helps slow by slow processing tools like informatica not make a total mess of the index</b>

 

Hello Tom,

you mentioned
</code> http://www.dbazine.com/jlewis3.html, <code>a very
useful article.

However, Mr Lewis states
"...and consider an example based on the countries that make up the United Kingdom -- England Ireland, Scotland and Wales."

Oops - I learnt something different in history in school.

As I saw, you will be
at the Irish Enterprise Architects Club 23rd February 2005 in Dublin.

I don't think you will meet Mr. Lewis there, but in case you
will, please tell him that Ireland *IS NOT* part of the UK

Thank you

Northern Ireland is still part of the UK, while Southern Ireland is not. So depending on your politics, religion and level of tolerance the statement is not totally wrong.

Cheers

Tim...

As usual you gave a very useful and clever answer !

At least he didn't mention evolution ... that always gets the forums buzzing.

I have read the mentioned articles, and what Mr. Lewis seems to be saying is that the most important part of bitmap indexes is their ability to work together, such that BITMAP ANDS and BITMAP MERGES compile the results of bitmap scans to create "new" bitmaps that key to the result sets that are desired. However, the fact of multiple bitmap indexes seems to conflict with another "rail" of Oracle warehousing: partitioning. Since one can only create LOCAL bitmap indexes, the desire to partition to induce parallelism seems to rule out the possiblity of multiple bitmap indexes. We can either have multiple bitmap indexes, or a single local bitmap index and a partitioned table.

Am I missing something?

Thanks Tom.

I think I'm confused.

On a table partitioned by DATE, would I want to create a local bitmap index on USER_ID? Would such an index even be useful?

And considering the structure of bitmap indexes, wouldn't creating all these individual bitmap indexes take a long time to build?

Thanks as usual Tom.

If I'm querying USER_ID, and the query does not have DATE as one of the predicates, and assuming that my result set spans all the partitions, then it seems that Oracle will have to return a result set from each of the partitions, combine them, and produce a final merged result set of all the partitions. That seems like an extra step that might possibly negate the performance enhancements from the bitmap, where possibly a global b-tree would be more efficient (it might anyway, but I hate to preclude it...)

However, in re-reading Mr. Lewis' first article in light of your respsonse, it seems that Oracle might actually combine the result sets into a new merged bitmap, much in the way it does with result sets querying different columns. If this is the case, then your response makes sense.

Is there any benefit or detriment to creating multi-key bitmaps? It seems like it might actually increase the performance of queries using both keys, but I have trouble visualizing the structure of such an index.

Thanks as usual.

We worked with our DWH ETL on 10G without dropping bitmaps, and it seems that 10G handles DML's with bitmaps much more efficiently than 9i (where we had to drop the bitmaps everytime we used to run the ETL). But when we introduced APPEND PARALLEL hints with 10G, we still had issues with DML's on bitmapped columns.

Also why doesnt partitioning cause any issues similar to the APPEND PARALLEL issues with DML's on bitmap columns?

Is it a good idea to bitmap index date columns

I have a table with 300000 records,with the distinct dates on the eff_dt column = 450.

Is it suggestible to build a) Bitmap index b)B-Tree indexes
and Why?

Thanks,

Oracle Versions - 6, 7, 7.3, 8i

In your book (Expert / Oracle / Database Architecture) you have nicely explained how more than one bitmap-indexes of the same table may be used, in the same execution plan, by optimizer to get the resuslt-set.

But, the same is not mentioned about the B*Tree Indexes.

Long back I had gone through many of the Oracle Manuals of versions 6, 7, 7.3 (hard-copies) like Oracle Concepts.

I clearly remember that one of the manuals mentioned (with some example) that this happened with B*Tree indexes.

Meaning that, if table t1 had two "unique" B*Tree indexes t1_i1 and t1_i2, then in some cases, the optimizer would use both the indexes, in the same execution plan, to get the result-set. Something like : Oracle would scan t1_i1 to get selected rows (rowids) and then look for them in the t1_i2.

Did I understand something wrong(ly) ? Or, has it changed in the later versions ?

Hi,

For all those who need to know the use of Bitmap Indexes and the difference between BTREE and BITMAP.

http://www.oracle.com/technology/pub/articles/sharma_indexes.html

Regards

Thanks for the kind support.

Hi Tom,

If i have partition table with 6,000,000 records and i like to add bitmap index on date column with this format
ddmmyyy.

What you suggest?

one partition bitmap index on the date column or split the date column to three columns yyyy_col,mm_col and dd_col then add three partition bitmap index one for each one .???


BTW i tried the second approach and get this result all quires make full scan except i use at least two of the three columns in the where statement and dd_col must be one of the two .

Kindly advice,

"You do NOT ever want to split a date into many attributes, just use a DATE"


thats what i need to hear . thanks alot.

We have a partitioned table in Oracle 10g. There are 16,907,572 records. We currently have a bitmap index on a date column (the values in the column are only date - no time component - there are 966 distinct values).

Why is it that the doing a query with
date Between Day1 and Day2
(maybe up to 60 days eg SYSDATE-60 and SYSDATE) does not use the bitmap index? (and opts for a full table scan)

Similar query using >= Day1 will use the Index ...

Statistics on both table and index are up to date and accuarate

Would it use the index on a normal B-tree Index (wih the between)?
If so, why?

The links mentioned by Tom are broken, the new links seem to be:

https://communities.bmc.com/communities/docs/DOC-10031
https://communities.bmc.com/communities/docs/DOC-10061
https://communities.bmc.com/communities/docs/DOC-10072

Select * from v$version

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

I have a problem with local bitmap index not getting used in a specific scenario when I have an equi join with time dimension. I have tested in 2 scenarios and the results are below.

My scenario below has a fact which is list partitioned monthly on std_accounting_period.

Query A joins the fact with time and the other dimension (booking code) to aggregate data for a year for specific booking codes. Its supposed to hit 12 partitions and it does that but it does a full scan on the partitions and is not taking advantage of the bitmap index defined on the Booking code column

The number of distinct values on booking code is 244 in a 55 million table and it has frequency based histogram also defined. Each partition has on an average 1 million records.

In Query B I have joined the fact with booking code without time dimension and passed the 12 std accounting periods in an INLIST. And here I see the bitmap used. Can you please help me in understanding if there is an Oracle issue or I am doing something incorrect?

I saw Jonathan Lewis addressing some issues with local bitmap indexes with list partitioned tables.
Jonathan lewis has written on this http://jonathanlewis.wordpress.com/2010/11/03/list-partitions/

A) Query with equi join

SELECT
/*+ gather_plan_statistics */
T1274643.period_name,
SUM(a.PERIOD_NET_CR) AS c1,
SUM(a.PERIOD_NET_DR) AS c2,
SUM(a.BEGIN_BALANCE_CR) AS c3,
SUM(a.BEGIN_BALANCE_DR) AS c4
FROM P_GL_BALANCE_TEMP a ,
P_PERIOD T1274643,
P_BOOKING_CODE T1274349
WHERE std_accounting_period = T1274643.period_name
AND T1274643.PERIOD_YEAR ='2011'
AND T1274643.PERIOD_SET ='Std Accounting'
AND T1274643.ADJUSTMENT_FLAG='N' and period_type <> 'Quarter' and period_type <> 'Year'
AND T1274349.BC = a.BC
AND concat(concat(T1274349.BC, ' - '), T1274349.BC_DESC) = '123B - XYZ Services Ltd.'
AND concat(concat(T1274349.LEVEL_2_CODE, ' - '), T1274349.LEVEL_2_DESC) = 'XYZ - World excluding US'
AND concat(concat(T1274349.LEVEL_3_CODE, ' - '), T1274349.LEVEL_3_DESC) = 'XYZ - World excluding US/UK/Europe'
AND concat(concat(T1274349.LEVEL_4_CODE, ' - '), T1274349.LEVEL_4_DESC) = 'XYZ - INDIA'
AND T1274349.LEVEL_1_CODE = 'GEOTOTAL'
GROUP BY T1274643.period_name

Plan hash value: 4200736382

----------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows|Buffers|
----------------------------------------------------------------------------------
|0 |SELECT STATEMENT | | 1| | 9| 102K|
|1 | HASH GROUP BY | | 1| 15 | 9| 102K|
|2 | NESTED LOOPS | | 1| 1210 | 17698| 102K|
|3 | MERGE JOIN CARTESIAN | | 1| 1 | 12| 9 |
|*4| TABLE ACCESS BY INDEX ROWID|P_BOOKING_CODE | 1| 1 | 1| 2 |
|*5| INDEX RANGE SCAN |P_BOOKING_CODE_I1| 1| 1 | 1| 1 |
|6 | BUFFER SORT | | 1| 15 | 12| 7 |
|*7| TABLE ACCESS FULL |P_PERIOD | 1| 15 | 12| 7 |
|8 | PARTITION LIST ITERATOR | | 12| 5021 | 17698| 102K|
|*9| TABLE ACCESS FULL |P_GL_BALANCE_TEMP| 12| 5021 | 17698| 102K|
----------------------------------------------------------------------------------

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

4 - filter(("LEVEL_4_CODE"||' - '||"LEVEL_4_DESC"='XYZ - INDIA' AND "LEVEL_3_CODE"||' -
'||"LEVEL_3_DESC"='XYZ - World excluding US/UK/Europe' AND "LEVEL_2_CODE"||' -
'||"LEVEL_2_DESC"='XYZ - World excluding US' AND "T1274349"."LEVEL_1_CODE"='GEOTOTAL'))
5 - access("T1274349"."SYS_NC00053$"='123B - XYZ Services Ltd.')
7 - filter(("T1274643"."PERIOD_YEAR"=2011 AND "T1274643"."PERIOD_SET"='Std Accounting' AND
"T1274643"."ADJUSTMENT_FLAG"='N' AND "PERIOD_TYPE"<>'Quarter' AND "PERIOD_TYPE"<>'Year'))
9 - filter(("STD_ACCOUNTING_PERIOD"="T1274643"."PERIOD_NAME" AND "T1274349"."BC"="A"."BC"))


B) Query with inlist

select /*+ gather_plan_statistics */ a.std_accounting_period,
SUM(a.PERIOD_NET_CR) AS c1,
SUM(a.PERIOD_NET_DR) AS c2,
SUM(a.BEGIN_BALANCE_CR) AS c3,
SUM(a.BEGIN_BALANCE_DR) AS c4
FROM P_GL_BALANCE_TEMP a , P_BOOKING_CODE T1274349
WHERE T1274349.BC = a.BC
AND concat(concat(T1274349.BC, ' - '), T1274349.BC_DESC) = '123B - XYZ Services Ltd.'
AND concat(concat(T1274349.LEVEL_2_CODE, ' - '), T1274349.LEVEL_2_DESC) = 'XYZ - World excluding US'
AND concat(concat(T1274349.LEVEL_3_CODE, ' - '), T1274349.LEVEL_3_DESC) = 'XYZ - World excluding US/UK/Europe'
AND concat(concat(T1274349.LEVEL_4_CODE, ' - '), T1274349.LEVEL_4_DESC) = 'XYZ - INDIA'
AND T1274349.LEVEL_1_CODE = 'GEOTOTAL'
AND a.std_accounting_period IN ('JAN-11','FEB-11','MAR-11','APR-11','MAY-11','JUN-11','JUL-11','AUG-11','SEP-11','OCT-11','NOV-11','DEC-11')
GROUP BY a.std_accounting_period

-------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows| Buffers |
-------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 9| 9537 |
| 1| HASH GROUP BY | | 1| 12| 9| 9537 |
| 2| NESTED LOOPS | | 1| | 17698| 9537 |
| 3| NESTED LOOPS | | 1| 952| 17698| 23 |
|*4| TABLE ACCESS BY INDEX ROWID |P_BOOKING_CODE | 1| 1| 1| 2 |
|*5| INDEX RANGE SCAN |P_BOOKING_CODE_I1 | 1| 1| 1| 1 |
| 6| PARTITION LIST INLIST | | 1| | 17698| 21 |
| 7| BITMAP CONVERSION TO ROWIDS | | 12| | 17698| 21 |
|*8| BITMAP INDEX SINGLE VALUE |IND_03_P_GL_BALANC| 12| | 9| 21 |
| 9| TABLE ACCESS BY LOCAL INDEX ROWID|P_GL_BALANCE_TEMP | 17698| 60946| 17698| 9514 |
-------------------------------------------------------------------------------------------

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

4 - filter(("LEVEL_4_CODE"||' - '||"LEVEL_4_DESC"='XYZ - INDIA' AND "LEVEL_3_CODE"||' -
'||"LEVEL_3_DESC"='XYZ - World excluding US/UK/Europe' AND "LEVEL_2_CODE"||' -
'||"LEVEL_2_DESC"='XYZ - World excluding US' AND "T1274349"."LEVEL_1_CODE"='GEOTOTAL'))
5 - access("T1274349"."SYS_NC00053$"='123B - XYZ Services Ltd.')
8 - access("T1274349"."BC"="A"."BC")

Hi Tom

I have created sample scenario with scripts below. I have 2 queries provided to show the difference in behavior while using inlist vs equi join.


FACT - P_FACT_PART_DIM
DIMENSION - P_DIM_PERIOD
DIMENSION - P_DIM_CODE

Query (1) joins Fact with both dimensions
Query (2) joins Fact with only P_DIM_CODE and passes an inlist to prune partitions


CREATE TABLE P_FACT_PART_BITMAP
(
PERIOD_NAME VARCHAR2 (15) NOT NULL,
CODE VARCHAR2 (25),
PAD VARCHAR2 (500)
)
PARTITION BY LIST (PERIOD_NAME)
(
PARTITION P_FACT_PART_BITMAP_JAN2008 VALUES ('JAN-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2008 VALUES ('FEB-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2008 VALUES ('MAR-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2008 VALUES ('APR-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2008 VALUES ('MAY-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2008 VALUES ('JUN-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2008 VALUES ('JUL-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2008 VALUES ('AUG-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2008 VALUES ('SEP-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2008 VALUES ('OCT-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2008 VALUES ('NOV-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2008 VALUES ('DEC-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JAN2009 VALUES ('JAN-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2009 VALUES ('FEB-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2009 VALUES ('MAR-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2009 VALUES ('APR-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2009 VALUES ('MAY-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2009 VALUES ('JUN-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2009 VALUES ('JUL-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2009 VALUES ('AUG-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2009 VALUES ('SEP-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2009 VALUES ('OCT-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2009 VALUES ('NOV-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2009 VALUES ('DEC-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JAN2010 VALUES ('JAN-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2010 VALUES ('FEB-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2010 VALUES ('MAR-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2010 VALUES ('APR-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2010 VALUES ('MAY-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2010 VALUES ('JUN-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2010 VALUES ('JUL-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2010 VALUES ('AUG-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2010 VALUES ('SEP-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2010 VALUES ('OCT-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2010 VALUES ('NOV-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2010 VALUES ('DEC-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JAN2011 VALUES ('JAN-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2011 VALUES ('FEB-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2011 VALUES ('MAR-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2011 VALUES ('APR-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2011 VALUES ('MAY-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2011 VALUES ('JUN-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2011 VALUES ('JUL-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2011 VALUES ('AUG-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2011 VALUES ('SEP-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2011 VALUES ('OCT-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2011 VALUES ('NOV-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2011 VALUES ('DEC-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEFAULT VALUES (DEFAULT)
LOGGING
NOCOMPRESS
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE TABLE P_DIM_PERIOD (PERIOD_NAME VARCHAR2(15) PRIMARY KEY, PERIOD_YEAR NUMBER);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('APR-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('APR-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('APR-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('APR-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-10',2010);

COMMIT;
CREATE index P_DIM_PERIOD_IDX_1 ON P_DIM_PERIOD (PERIOD_YEAR);

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,PAD)
SELECT
PERIOD_NAME,
OWNER,
LPAD(OBJECT_NAME,200,' ')
FROM
ALL_OBJECTS, (SELECT PERIOD_NAME FROM P_DIM_PERIOD WHERE PERIOD_YEAR = 2011) --only inserting data for 2011 ;

COMMIT;
--Around 900,000 rows inserted. We had around 75000 objects and multiply by 12 periods from period table.

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,PAD)
SELECT
*
FROM
P_FACT_PART_BITMAP;
COMMIT;

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,PAD)
SELECT
*
FROM
P_FACT_PART_BITMAP;
COMMIT;

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,PAD)
SELECT
*
FROM
P_FACT_PART_BITMAP;
COMMIT;

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,PAD)
SELECT
*
FROM
P_FACT_PART_BITMAP;
COMMIT;

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,PAD)
SELECT
*
FROM
P_FACT_PART_BITMAP;

COMMIT;

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,PAD)
SELECT
*
FROM
P_FACT_PART_BITMAP;

COMMIT;
--Total 57 Million rows

CREATE BITMAP INDEX P_FACT_BIT_IDX1 ON P_FACT_PART_BITMAP (CODE) LOCAL;


CREATE TABLE P_DIM_CODE (CODE VARCHAR2 (25) PRIMARY KEY, CODE_DESC VARCHAR2 (100));

INSERT INTO P_DIM_CODE
SELECT
DISTINCT OWNER,
'Just testing'
FROM ALL_OBJECTS;


COMMIT;


exec dbms_stats.gather_table_stats(user,'P_FACT_PART_BITMAP',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
exec dbms_stats.gather_table_stats(user,'P_DIM_CODE',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
exec dbms_stats.gather_table_stats(user,'P_DIM_PERIOD',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);


Query 1): With join to period dim

select /*+ gather_plan_statistics */ count (*) from
P_FACT_PART_BITMAP a,
P_DIM_CODE b,
P_DIM_PERIOD c
where
a.code = b.code
and b.code = 'SYSTEM'
and c.period_name = a.PERIOD_NAME
and c.period_year = 2011

-----------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows|Buffers|Reads
-----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 1| 440K| 440K
| 1| SORT AGGREGATE | | 1| 1| 1| 440K| 440K
| 2| NESTED LOOPS | | 1| 377K| 387K| 440K| 440K
| 3| NESTED LOOPS | | 1| 12| 12| 3 | 0
|*4| INDEX UNIQUE SCAN |SYS_C0033875 | 1| 1| 1| 1 | 0
| 5| TABLE ACCESS BY INDEX ROWID|P_DIM_PERIOD | 1| 12| 12| 2 | 0
|*6| INDEX RANGE SCAN |P_DIM_PERIOD_IDX_1| 1| 12| 12| 1 | 0
| 7| PARTITION LIST ITERATOR | | 12| 32842| 387K| 440K| 440K
|*8| TABLE ACCESS FULL |P_FACT_PART_BITMAP| 12| 32842| 387K| 440K| 440K
-----------------------------------------------------------------------------------------

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

4 - access("B"."CODE"='SYSTEM')
6 - access("C"."PERIOD_YEAR"=2011)
8 - filter(("A"."CODE"='SYSTEM' AND "C"."PERIOD_NAME"="A"."PERIOD_NAME"))

Query (2): With inlist (no period dim join)

-----------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows|Buffers|Reads
-----------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 1 | 27061|11709
| 1| SORT AGGREGATE | | 1| 1| 1 | 27061|11709
| 2| NESTED LOOPS | | 1| 394K| 387K| 27061|11709
|*3| INDEX UNIQUE SCAN |SYS_C0033875 | 1| 1| 1 | 1| 0
| 4| PARTITION LIST INLIST | | 1| 394K| 387K| 27060|11709
| 5| TABLE ACCESS BY LOCAL INDEX ROWID|P_FACT_PART_BITMAP| 12| 394K| 387K| 27060|11709
| 6| BITMAP CONVERSION TO ROWIDS | | 12| | 387K| 24| 0
|*7| BITMAP INDEX SINGLE VALUE |P_FACT_BIT_IDX1 | 12| | 12 | 24| 0
-----------------------------------------------------------------------------------------------

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

3 - access("B"."CODE"='SYSTEM')
7 - access("A"."CODE"='SYSTEM')


--SYSTEM owner had 500 objects out of 75000 (SYS had 30000 PUBLIC had 28000 objects and rest in other owners)
While providing some other inputs e.g. SYS I saw the execution path change and doing a full scan.

My question is there any reason why for this input (SYSTEM) that I see 2 different exection paths when using equi join with period vs in-list to partition prune?

Sorry about that. I had formatting challenges with xplan output so I was making those changes on my notepad and copying to your site and previewing it if it was readable. Did it a few times and finally when I got it right and copied the results I mistakenly yanked out the 2nd query. Is there a better way to make xplan output readable on a single line?

Here is the missing query
SELECT /*+ gather_plan_statistics */ count(*) FROM
P_FACT_PART_BITMAP a,
P_DIM_CODE b
where
a.code = b.code
and b.code = 'SYSTEM'
and a.PERIOD_NAME IN ('JAN-11','FEB-11','MAR-11','APR-11','MAY-11','JUN-11','JUL-11','AUG-11','SEP-11','OCT-11','NOV-11','DEC-11')

I have also seen cases during my testing when I supply different values for owner. I have seen bitmap index getting used for both queries (in and join). This I believe was happening for b.code ='CTXSYS' which has lesser objects around 65 compared to SYSTEM with 500 and SYS with 30000. How did Oracle manage to pick up the bitmap for this case especially for Query 1 i.e. join with period for inlist I understand as you have explained?

While in another case b.code='SYS' I noticed both the queries not using the bitmap. This does result in a larger set nearly half of the data so I guess in INLIST case it had enough information to make that guess while for equi join it doesn't in any case but then in above example it selected bitmap (i.e. b.code='CTSYS'). So I am a little confused.

Lastly with range partitioned facts. I cannot partition prune when I join with a period dimension. Example if there is a range partitioned table on date (>= month start date and <= less than month end date) and join with period (primary key being date) and filtered for a month or quarter or year on period. It doesn't partition prune unless I give a between clause or pass literals to the fact. Atleast in List partitions it does partition prune but local indexes is not getting used. Can you please help me in understanding on how best to work with partitioned tables and local indexes?

Thanks
Prashant

I am sticking with LIST and Query 1 (i.e. fact join to time dimension) since I need some more clarification.

When I provide different values to CODE then I see that Oracle picks the index. Is this because I have histograms on the CODE column that I see this happening because the estimated and actual rows are very close? I gathered stats on Fact with method_opt => 'FOR ALL COLUMNS SIZE AUTO' or is it because of some other reason?

If Histograms is the reason I see accurate cardinalities is it advisable to create and maintain histograms on large facts (i.e this case 58 M)? What are the other options?

A) Filter = 'DBSNMP'
select /*+ gather_plan_statistics */ count (*) from
P_FACT_PART_BITMAP a,
P_DIM_CODE b,
P_DIM_PERIOD c
where
a.code = b.code
and b.code = 'DBSNMP'
and c.period_name = a.PERIOD_NAME
and c.period_year = 2011
------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows|Buffers|Reads|
------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 1| 5741| 869|
| 1| SORT AGGREGATE | | 1| 1| 1| 5741| 869|
|*2| HASH JOIN | | 1| 51354| 49920| 5741| 869|
| 3| NESTED LOOPS | | 1| 12| 12| 3| 0|
|*4| INDEX UNIQUE SCAN |SYS_C0033875 | 1| 1| 1| 1| 0|
| 5| TABLE ACCESS BY INDEX ROWID |P_DIM_PERIOD | 1| 12| 12| 2| 0|
|*6| INDEX RANGE SCAN |P_DIM_PERIOD_IDX_1| 1| 12| 12| 1| 0|
| 7| PARTITION LIST SUBQUERY | | 1| 53587| 49920| 5738| 869|
| 8| TABLE ACCESS BY LOCAL INDEX ROWID|P_FACT_PART_BITMAP| 12| 53587| 49920| 5736| 869|
| 9| BITMAP CONVERSION TO ROWIDS | | 12| | 49920| 24| 0|
|*10 BITMAP INDEX SINGLE VALUE |P_FACT_BIT_IDX1 | 12| | 12| 24| 0|
-------------------------------------------------------------------------------------------------

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

2 - access("C"."PERIOD_NAME"="A"."PERIOD_NAME")
4 - access("B"."CODE"='DBSNMP')
6 - access("C"."PERIOD_YEAR"=2011)
10 - access("A"."CODE"='DBSNMP')

B) Filter ='SYSTEM'
select /*+ gather_plan_statistics */ count (*) from
P_FACT_PART_BITMAP a,
P_DIM_CODE b,
P_DIM_PERIOD c
where
a.code = b.code
and b.code = 'SYSTEM'
and c.period_name = a.PERIOD_NAME
and c.period_year = 2011

-------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows|Buffers|Reads|
-------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 1 | 440K| 440K|
| 1| SORT AGGREGATE | | 1| 1 | 1 | 440K| 440K|
| 2| NESTED LOOPS | | 1| 318K| 387K| 440K| 440K|
| 3| NESTED LOOPS | | 1| 12 | 12 | 3 | 0 |
|*4| INDEX UNIQUE SCAN | SYS_C0033875 | 1| 1 | 1 | 1 | 0 |
| 5| TABLE ACCESS BY INDEX ROWID| P_DIM_PERIOD | 1| 12 | 12 | 2 | 0 |
|*6| INDEX RANGE SCAN | P_DIM_PERIOD_IDX_1| 1| 12 | 12 | 1 | 0 |
| 7| PARTITION LIST ITERATOR | | 12|27687 | 387K| 440K| 440K|
|*8| TABLE ACCESS FULL | P_FACT_PART_BITMAP| 12|27687 | 387K| 440K| 440K|
--------------------------------------------------------------------------------------------

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

4 - access("B"."CODE"='SYSTEM')
6 - access("C"."PERIOD_YEAR"=2011)
8 - filter(("A"."CODE"='SYSTEM' AND "C"."PERIOD_NAME"="A"."PERIOD_NAME"))

Hi Tom

I have done some additional tests on the fact table although I have made it smaller now and introduced a new dimension.

CREATE TABLE P_FACT_PART_BITMAP
(
PERIOD_NAME VARCHAR2 (15) NOT NULL,
CODE VARCHAR2 (25),
OBTYPE VARCHAR2 (40),
PAD VARCHAR2 (500)
)
PARTITION BY LIST (PERIOD_NAME)
(
PARTITION P_FACT_PART_BITMAP_JAN2008 VALUES ('JAN-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2008 VALUES ('FEB-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2008 VALUES ('MAR-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2008 VALUES ('APR-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2008 VALUES ('MAY-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2008 VALUES ('JUN-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2008 VALUES ('JUL-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2008 VALUES ('AUG-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2008 VALUES ('SEP-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2008 VALUES ('OCT-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2008 VALUES ('NOV-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2008 VALUES ('DEC-08')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JAN2009 VALUES ('JAN-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2009 VALUES ('FEB-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2009 VALUES ('MAR-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2009 VALUES ('APR-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2009 VALUES ('MAY-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2009 VALUES ('JUN-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2009 VALUES ('JUL-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2009 VALUES ('AUG-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2009 VALUES ('SEP-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2009 VALUES ('OCT-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2009 VALUES ('NOV-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2009 VALUES ('DEC-09')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JAN2010 VALUES ('JAN-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2010 VALUES ('FEB-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2010 VALUES ('MAR-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2010 VALUES ('APR-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2010 VALUES ('MAY-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2010 VALUES ('JUN-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2010 VALUES ('JUL-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2010 VALUES ('AUG-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2010 VALUES ('SEP-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2010 VALUES ('OCT-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2010 VALUES ('NOV-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2010 VALUES ('DEC-10')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JAN2011 VALUES ('JAN-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_FEB2011 VALUES ('FEB-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAR2011 VALUES ('MAR-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_APR2011 VALUES ('APR-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_MAY2011 VALUES ('MAY-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUN2011 VALUES ('JUN-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_JUL2011 VALUES ('JUL-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_AUG2011 VALUES ('AUG-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_SEP2011 VALUES ('SEP-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_OCT2011 VALUES ('OCT-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_NOV2011 VALUES ('NOV-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEC2011 VALUES ('DEC-11')
LOGGING
NOCOMPRESS ,
PARTITION P_FACT_PART_BITMAP_DEFAULT VALUES (DEFAULT)
LOGGING
NOCOMPRESS
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

CREATE TABLE P_DIM_PERIOD (PERIOD_NAME VARCHAR2(15) PRIMARY KEY, PERIOD_YEAR NUMBER);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('APR-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-11',2011);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('APR-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-08',2008);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('APR-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-09',2009);
INSERT INTO P_DIM_PERIOD VALUES ('JAN-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('FEB-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('MAR-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('APR-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('MAY-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('JUN-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('JUL-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('AUG-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('SEP-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('OCT-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('NOV-10',2010);
INSERT INTO P_DIM_PERIOD VALUES ('DEC-10',2010);

COMMIT;
CREATE index P_DIM_PERIOD_IDX_1 ON P_DIM_PERIOD (PERIOD_YEAR);

INSERT INTO P_FACT_PART_BITMAP (PERIOD_NAME, CODE,OBTYPE,PAD)
SELECT
PERIOD_NAME,
OWNER,
OBJECT_TYPE,
LPAD(OBJECT_NAME,200,' ')
FROM
ALL_OBJECTS, (SELECT PERIOD_NAME FROM P_DIM_PERIOD);


CREATE BITMAP INDEX P_FACT_BIT_IDX1 ON P_FACT_PART_BITMAP (CODE) LOCAL;
CREATE BITMAP INDEX P_FACT_BIT_IDX2 ON P_FACT_PART_BITMAP (OBTYPE) LOCAL;

CREATE TABLE P_DIM_CODE (CODE VARCHAR2 (25) PRIMARY KEY, CODE_DESC VARCHAR2 (100));

INSERT INTO P_DIM_CODE
SELECT
DISTINCT OWNER,
'Just testing'
FROM ALL_OBJECTS;
COMMIT;

CREATE TABLE P_DIM_OBTYPE (OBTYPE VARCHAR2 (25) PRIMARY KEY, OBTYPE_DESC VARCHAR2 (100));

INSERT INTO P_DIM_OBTYPE
SELECT
DISTINCT OBJECT_TYPE,
'Just testing'
FROM ALL_OBJECTS;

COMMIT;

exec dbms_stats.gather_table_stats(user,'P_FACT_PART_BITMAP');
exec dbms_stats.gather_table_stats(user,'P_DIM_CODE');
exec dbms_stats.gather_table_stats(user,'P_DIM_PERIOD');
exec dbms_stats.gather_table_stats(user,'P_DIM_OBTYPE');

Scenario 1 (as expected)
------------------------
select /*+ gather_plan_statistics*/ count (*) from
P_FACT_PART_BITMAP a,
P_DIM_CODE b,
P_DIM_PERIOD c,
P_DIM_OBTYPE d
where
a.code = b.code
and b.code = 'DBSNMP'
and a.obtype = d.obtype
--and d.obtype = 'TABLE'
and c.period_name = a.PERIOD_NAME
and c.period_year = 2011 ;

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | NESTED LOOPS | | 1 | 606 | 564 |
|* 3 | HASH JOIN | | 1 | 606 | 564 |
| 4 | NESTED LOOPS | | 1 | 12 | 12 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0053732 | 1 | 1 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID | P_DIM_PERIOD | 1 | 12 | 12 |
|* 7 | INDEX RANGE SCAN | P_DIM_PERIOD_IDX_1 | 1 | 12 | 12 |
| 8 | PARTITION LIST SUBQUERY | | 1 | 2424 | 564 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| P_FACT_PART_BITMAP | 12 | 2424 | 564 |
| 10 | BITMAP CONVERSION TO ROWIDS | | 12 | | 564 |
|* 11 | BITMAP INDEX SINGLE VALUE | P_FACT_BIT_IDX1 | 12 | | 12 |
|* 12 | INDEX UNIQUE SCAN | SYS_C0053733 | 564 | 1 | 564 |
-----------------------------------------------------------------------------------------------

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

3 - access("C"."PERIOD_NAME"="A"."PERIOD_NAME")
5 - access("B"."CODE"='DBSNMP')
7 - access("C"."PERIOD_YEAR"=2011)
11 - access("A"."CODE"='DBSNMP')
12 - access("A"."OBTYPE"="D"."OBTYPE")

Scenario 2 (I would hope that it prunes the partitions first and then does a bitmap scan but it goes the other way. I think Oracle estimates 108 in Step 7. How can I improve this estimation? I already have histograms in place)
--------------------------------------------------------------
select /*+ gather_plan_statistics*/ count (*) from
P_FACT_PART_BITMAP a,
P_DIM_CODE b,
P_DIM_PERIOD c,
P_DIM_OBTYPE d
where
a.code = b.code
and b.code = 'DBSNMP'
and a.obtype = d.obtype
and d.obtype = 'TABLE'
and c.period_name = a.PERIOD_NAME
and c.period_year = 2011 ;

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | HASH JOIN | | 1 | 30 | 300 |
| 3 | NESTED LOOPS | | 1 | 108 | 1200 |
| 4 | NESTED LOOPS | | 1 | 1 | 1 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0053733 | 1 | 1 | 1 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0053732 | 1 | 1 | 1 |
| 7 | PARTITION LIST ALL | | 1 | 108 | 1200 |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| P_FACT_PART_BITMAP | 49 | 108 | 1200 |
| 9 | BITMAP CONVERSION TO ROWIDS | | 48 | | 1200 |
| 10 | BITMAP AND | | 48 | | 48 |
|* 11 | BITMAP INDEX SINGLE VALUE | P_FACT_BIT_IDX1 | 48 | | 48 |
|* 12 | BITMAP INDEX SINGLE VALUE | P_FACT_BIT_IDX2 | 48 | | 48 |
| 13 | TABLE ACCESS BY INDEX ROWID | P_DIM_PERIOD | 1 | 12 | 12 |
|* 14 | INDEX RANGE SCAN | P_DIM_PERIOD_IDX_1 | 1 | 12 | 12 |
-----------------------------------------------------------------------------------------------

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

2 - access("C"."PERIOD_NAME"="A"."PERIOD_NAME")
5 - access("D"."OBTYPE"='TABLE')
6 - access("B"."CODE"='DBSNMP')
11 - access("A"."CODE"='DBSNMP')
12 - access("A"."OBTYPE"='TABLE')
14 - access("C"."PERIOD_YEAR"=2011)

Scenario 3 (I think it estimates 2284*12 = 27408 rows and feels full scan is preferable but why? I have 849,528 rows for 2011 i.e 70794 rows in each partition. Wouldn't index be the preferred option as this is less than 20% of rows)

select /*+ gather_plan_statistics*/ count (*) from
P_FACT_PART_BITMAP a,
P_DIM_CODE b,
P_DIM_PERIOD c,
P_DIM_OBTYPE d
where
a.code = b.code
and b.code = 'SYSTEM'
and a.obtype = d.obtype
--and d.obtype = 'TABLE'
and c.period_name = a.PERIOD_NAME
and c.period_year = 2011 ;

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | NESTED LOOPS | | 1 | 27404 | 5748 |
| 3 | NESTED LOOPS | | 1 | 27404 | 5748 |
| 4 | NESTED LOOPS | | 1 | 12 | 12 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0053732 | 1 | 1 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| P_DIM_PERIOD | 1 | 12 | 12 |
|* 7 | INDEX RANGE SCAN | P_DIM_PERIOD_IDX_1 | 1 | 12 | 12 |
| 8 | PARTITION LIST ITERATOR | | 12 | 2284 | 5748 |
|* 9 | TABLE ACCESS FULL | P_FACT_PART_BITMAP | 12 | 2284 | 5748 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0053733 | 5748 | 1 | 5748 |
-----------------------------------------------------------------------------------------

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

5 - access("B"."CODE"='SYSTEM')
7 - access("C"."PERIOD_YEAR"=2011)
9 - filter(("A"."CODE"='SYSTEM' AND "C"."PERIOD_NAME"="A"."PERIOD_NAME"))
10 - access("A"."OBTYPE"="D"."OBTYPE")

Scenario 4 (Here again it doesn't partition prune first like in scenario 2. In this case its using AND BITMAP like in (2) and in both it goes the other way. Any reason?)

select /*+ gather_plan_statistics*/ count (*) from
P_FACT_PART_BITMAP a,
P_DIM_CODE b,
P_DIM_PERIOD c,
P_DIM_OBTYPE d
where
a.code = b.code
and b.code = 'SYSTEM'
and a.obtype = d.obtype
and d.obtype = 'TABLE'
and c.period_name = a.PERIOD_NAME
and c.period_year = 2011 ;

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | HASH JOIN | | 1 | 285 | 1884 |
| 3 | TABLE ACCESS BY INDEX ROWID | P_DIM_PERIOD | 1 | 12 | 12 |
|* 4 | INDEX RANGE SCAN | P_DIM_PERIOD_IDX_1 | 1 | 12 | 12 |
| 5 | NESTED LOOPS | | 1 | 1139 | 7536 |
| 6 | NESTED LOOPS | | 1 | 1 | 1 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0053733 | 1 | 1 | 1 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0053732 | 1 | 1 | 1 |
| 9 | PARTITION LIST ALL | | 1 | 1139 | 7536 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| P_FACT_PART_BITMAP | 49 | 1139 | 7536 |
| 11 | BITMAP CONVERSION TO ROWIDS | | 48 | | 7536 |
| 12 | BITMAP AND | | 48 | | 48 |
|* 13 | BITMAP INDEX SINGLE VALUE | P_FACT_BIT_IDX1 | 48 | | 48 |
|* 14 | BITMAP INDEX SINGLE VALUE | P_FACT_BIT_IDX2 | 48 | | 48 |
-----------------------------------------------------------------------------------------------
My questions are mentioned along with each scenario.

I am still not getting a handle of effectively using bitmaps. I do think I have right strategies in terms of indexing and stats collections but I see a different behaviour than I expect. Can you please help?