Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, christophe.

Asked: January 26, 2005 - 3:28 pm UTC

Last updated: October 04, 2011 - 11:37 am UTC

Version: 9

Viewed 1000+ times

You Asked

Hi tom,

First thank you for all the time and consideration you give to oracle community.
My question is about bitmap index.
My comprehension of bitmap is to use them when there is low cardinality on a certain column.
Is it right ? and is it the only thing to consider to use a bitmap index ?

Thanks for your answer

and Tom said...

No -- that is not only not the only thing, but sometimes, it isn't even one of the things to consider!

Read:

</code> http://www.dbazine.com/jlewis3.html http://www.dbazine.com/jlewis6.html http://www.dbazine.com/jlewis7.html <code>

....

Rating

  (24 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

10g bitmap changes

bob, January 26, 2005 - 6:28 pm UTC

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?

Tom Kyte
January 27, 2005 - 9:07 am UTC

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>

 

Mr Lewis' error

Matthias Rogel, January 27, 2005 - 3:01 am UTC

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

Oh Really!

Tim Hall, January 27, 2005 - 5:20 am UTC

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...

use of bitmap index

christophe, January 27, 2005 - 8:51 am UTC

As usual you gave a very useful and clever answer !



Not so contraversial ...

David Aldridge, January 27, 2005 - 8:52 am UTC

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

Response to Jonathan Lewis' articles

Stewart W. Bryson, February 11, 2005 - 3:10 pm UTC

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.

Tom Kyte
February 12, 2005 - 12:02 pm UTC



where ( a = 5 and c = 20 ) or ( d > 50 and e < 20 )


bitmaps on a, c, d, e (one on each single column)


so, why would it matter if we:


bitmap( bitmap(A and C) or bitmap(D and E))

once for a single index partition or 20 times for the 20 local index partitions?

we'd be using the bitmaps against each partition to find the rows in each partition "where ( a = 5 and c = 20 ) or ( d > 50 and e < 20 )"


I don't see any mismatch in functionality?

Sorry Tom... I don't follow

Stewart W. Bryson, February 14, 2005 - 12:51 pm UTC

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.

Tom Kyte
February 14, 2005 - 4:00 pm UTC

no more than if the table weren't partitioned really.

what is it about the structure of the bitmap index that leads you to think it would not be useful to find rows by user_id in the table?

Maybe a little less confused...

Stewart W. Bryson, February 14, 2005 - 5:32 pm UTC

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.

Tom Kyte
February 14, 2005 - 6:16 pm UTC

it'll have to scan all of the partitions and does not have to merge anything. you are getting rows. we get the first row and give it straight back to you. when you ask for more, we get more.

we don't need to "merge" anything because of the partitions, we just range scan N indexes -- giving you rows as you ask for them. we do not get the entire thing (unless the query mandates that we do -- like "select count(*)" might -- but that would affect a single index, or N indexes -- they both would get them all first)


there is no "merging" here needed.

Bitmaps in 10G

Avinash, January 26, 2006 - 6:39 pm UTC

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?

Tom Kyte
January 27, 2006 - 8:27 am UTC

insufficient data...

guess:

when you do DML, it is done in bulk (big operation). In 9i and 10g (better in 10g than 9i) a bitmap index could "deal" with that (but never single row operations). So you saw an improvement of the management of bitmapped indexes in 10g over 9i.

Then you went parallel and broke a single big bulk operation into many smaller ones (but no idea the "scale" here, guessing). Taking one big bulk into many smaller bulk and ..... well, bitmaps want as big a bulk operation as possible.

date column as bitmap index

A reader, August 23, 2006 - 11:55 am UTC

Is it a good idea to bitmap index date columns

Tom Kyte
August 27, 2006 - 3:48 pm UTC

</code> http://asktom.oracle.com/Misc/i-believe-strongly-there-are-only-two.html <code>

the answer to this is:

it depends.


It is not a bad idea entirely.
There are many cases when it would be a bad idea
There are many cases when it would be a good idea.


A date is not that different from a number.

Which cse it is good and which it is bad

Dev, October 10, 2006 - 6:43 am UTC

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,

Tom Kyte
October 10, 2006 - 8:20 am UTC

please go to the other place you asked the same exact thing and read my comments over there.

More than 1 index in one execution plan

Yogesh Purabiya, May 21, 2007 - 5:23 am UTC

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 ?
Tom Kyte
May 21, 2007 - 10:28 am UTC

there is (with the CBO - cost based optimizer) the ability to do an index join - but in general, you won't see the optimizer merging together b*tree indexes in the fashion bitmaps are.


create table t ( x, y, z );

create index t_idx1 on t(x);
create index t_idx2 on t(y);

select x, y from t where x=5 and y=2;

that might be able to use t_idx1 to find the x=5's (and their rowids), and t_idx2 to find the y=2's (and their rowids) and then join those two results by rowid to find the x=5 and y=y rows.

but that is very different from a bitmap AND operation.

LINK for all those who need to know the use of Bitmap Indexes

VLS, May 21, 2007 - 11:03 am UTC

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

Yogesh Purabiya, May 22, 2007 - 11:21 pm UTC

Thanks for the kind support.

BITMAP INDEX

Doxxtt, August 17, 2011 - 2:44 am UTC

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,

Tom Kyte
August 17, 2011 - 4:27 am UTC

you do not use a format in an index on a date column- I have no idea what you mean by that at all?

you have a date column, you would just:

create bitmap index on table(date_column) local;

you need to be a lot more clear in what you mean here - maybe an example or something - I'm not following you at all.

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


Thanks alot

doxxtt, August 18, 2011 - 3:10 am UTC

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


thats what i need to hear . thanks alot.

Bitmap Index Vs B-Tree index on Date Column

Russell, August 24, 2011 - 8:35 pm UTC

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?
Tom Kyte
August 30, 2011 - 4:00 pm UTC

it is all about the estimated cardinalities.

An index - bitmap, b*tree or otherwise - will be used to get a SMALL number of rows from a table. SMALL is relative - it might be 100 rows, it might be 1,000 rows. It might not be 500 rows. It depends.


Look at the cardinality estimates. How many rows does each predicate assume it will retrieve?

FYI: Links to dbazine are broken

Frank Schmitt, August 25, 2011 - 3:20 am UTC

Bitmap on partitioned tables

Prashant, September 22, 2011 - 8:39 am UTC

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")
Tom Kyte
September 22, 2011 - 5:52 pm UTC

one would need schemas (indexes and all), description of data in said schemas


I don't even know what table is the fact table here - I'm not good at guessing (meaning I don't like to guess). You don't tell me what is the fact, what the bit map is on (tablewise) and so on.

No schema, no look

Prashant, September 23, 2011 - 7:59 am UTC

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?
Tom Kyte
September 23, 2011 - 9:50 am UTC

why did you leave off the second query?????? why would you do that? Everything else was there - but you leave off part of the test case?????


but the answer is rather simple -

If I tell you to get the rows from Table T such that column X is in ( 1,2,3,4 ) - would you be able to guess the estimated rows flowing out of table T easier than if I said get the rows from table T such that column X is in (select Y from T2 where ...).


Given the hard coded facts - we'd be able to likely estimate the rows (cardinality) better then if we have to first guess how many rows will come from T2, and then based on that unknown set of rows (we don't know their values), how many rows will come from T.



Added missing query and some additional points

Prashant, September 24, 2011 - 5:21 am UTC

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
Tom Kyte
September 24, 2011 - 2:03 pm UTC

I didn't need the query to answer - I answered your question in full above.


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?


again, give the setup for the problem - creates, etc.

Need a little more clarification

Prashant, September 26, 2011 - 5:46 am UTC

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"))


Tom Kyte
September 26, 2011 - 7:36 am UTC

when you are not using bind variables, as you would typically not be in a data warehouse, histograms can be *very* useful to have for skewed data. In the case of skewed data - you might want to use an index sometimes for a predicate against a given column - and not other times. Histograms provide us the additional information we need to make that decision.

Thanks Tom

Prashant, September 27, 2011 - 10:34 am UTC


Bitmap index

Prashant, October 04, 2011 - 7:15 am UTC

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?
Tom Kyte
October 04, 2011 - 11:37 am UTC

you use bitmap indexes for the same use cases you use b*tree indexes.


You would use a bitmap index to access very few of the rows in a table (just like you would use a b*tree index)

You would use a bitmap index INSTEAD of the table (just like a b*tree, sometimes we create an index to be a skinny version of the table - and full scan the index instead of full scanning the table)


Beyond that - I cannot read a 15 page example and digest it in a review section - and I don't really know what you were expecting so I cannot comment on why the behavior is different from what you expected.

be concise, ask a simple direct question - I can deal with that.