473,320 Members | 1,820 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Why isn't bitmap index being used in this example

I have a table named 'touchpoint' which contains about 20 millions of
rows.

I created a bitmap index on a column named 'reason_cd' which has 7
distinct values: 'IN', 'FR', 'OP', 'CM', 'SV', 'ME', and null.

However, If you take a look at the explain plan for a script like
this, The oracle choose to do full access which is unexpected.

select * from touchpoint where reason_cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ---------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL TOUCHPOINT

Funny thing is if I run the following script, it choose to use the
index.

select count(*) from touchpoint where reason-cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ----------------
0 SELECT STATEMENT
1 SORT AGGREGATE
2 INLIST ITERATOR
3 BITMAP CONVERSION COUNT
4 BITMAP INDEX SINGLE VALUE IDX_TP_REASON_CD

Can anyone explain what is going on?
Jul 19 '05 #1
2 4870
> I have a table named 'touchpoint' which contains about 20 millions of
rows.

I created a bitmap index on a column named 'reason_cd' which has 7
distinct values: 'IN', 'FR', 'OP', 'CM', 'SV', 'ME', and null.

However, If you take a look at the explain plan for a script like
this, The oracle choose to do full access which is unexpected.

select * from touchpoint where reason_cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ---------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL TOUCHPOINT

Funny thing is if I run the following script, it choose to use the
index.

select count(*) from touchpoint where reason-cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ----------------
0 SELECT STATEMENT
1 SORT AGGREGATE
2 INLIST ITERATOR
3 BITMAP CONVERSION COUNT
4 BITMAP INDEX SINGLE VALUE IDX_TP_REASON_CD

Can anyone explain what is going on?


Your select count(*) requires only a visit to the bitmap index,
whereas your select * would require both a visit to the bitmap index
and a read of the table data itself (using the rowid pointers). Oracle
visibly thinks that, for the select *, it might as well read the table
record by record (or I should say block by block). If you want to see
by how much the full table scan "wins", turn on event 10053 (and don't
forget that for the log file to be populated, the statement has to be
PARSED, and not only executed - flush the shared pool if necessary).

Daniel
Jul 19 '05 #2
ch********@hotmail.com (chulhee) wrote in message news:<b5**************************@posting.google. com>...
I have a table named 'touchpoint' which contains about 20 millions of
rows.

I created a bitmap index on a column named 'reason_cd' which has 7
distinct values: 'IN', 'FR', 'OP', 'CM', 'SV', 'ME', and null.

However, If you take a look at the explain plan for a script like
this, The oracle choose to do full access which is unexpected.

select * from touchpoint where reason_cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ---------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL TOUCHPOINT

Funny thing is if I run the following script, it choose to use the
index.

select count(*) from touchpoint where reason-cd in ('OP', 'SV')

ID OPERATION OPTIONS OBJECT_NAME
--- -------------------- -------------------- ----------------
0 SELECT STATEMENT
1 SORT AGGREGATE
2 INLIST ITERATOR
3 BITMAP CONVERSION COUNT
4 BITMAP INDEX SINGLE VALUE IDX_TP_REASON_CD

Can anyone explain what is going on?


The two plans you show make me think that the statistics show the
table is fairly small so accessing the data is faster performing a
full table scan rather than via the bitmap index. While just scanning
the index is faster for counting hits since no data is requested which
in turn means table access is not required at all.

You might want to compare your actual row count to the
dba_tables.num_rows column. If the table is small you need more data
for testing.

HTH -- Mark D Powell --
Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Prashant | last post by:
Hi, I have a huge problem. I have a data file which looks something like this -: ..1 .5 .9 -1 .2 .5 ...... ..2 .9 .1 .4 .3 -1 ...... ..2 .4 .5 .7 .6 .2 ...... ........
14
by: ford_desperado | last post by:
Why isn't ALLOW REVERSE SCANS the default? Why do we have to - drop PK - create an index - recreate PK What are the advantages of indexes that do not allow reverse scans?
10
by: Troels Arvin | last post by:
Hello, At http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems it's stated that DB2 doesn't have bitmap indexes. This seemed strange to me, so I tried looking in...
6
by: Lespaul36 | last post by:
From what I have read, there isn't really a converter to icon format..it becomes a png file. I tried: 1: Dim bmp As Bitmap = CType(Bitmap.FromFile("C:\myfolder\file.bmp"), Bitmap) 2: ...
0
by: sp | last post by:
Hi everyone, Just joined this group. I have a question about saving bitmap images from flash too a .NET site using VBScript. There are several tutorials showing how this can be done using php...
8
by: Frank | last post by:
Given a bitmap I want to write a Icon file using it. I believe I can do it except for writing the bits of the image. Can you tell me how to get the bits into a ByteArray Thanks
6
by: \Frank\ | last post by:
I trying to learn what a Bitmap is. Not a Managed Bitmap Object but one that, for example, comes from the clipboard with CF_BITMAP. I'm guessing that a CompatableBitmap is an array of indices...
4
by: krishhhna | last post by:
what is the bitmap index ? how it can be used? what is the difference between bitmap index and other indexes?
2
by: chulhee | last post by:
I have a table named 'touchpoint' which contains about 20 millions of rows. I created a bitmap index on a column named 'reason_cd' which has 7 distinct values: 'IN', 'FR', 'OP', 'CM', 'SV',...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.