-
Bitmap index is not working
Hi,
I am new in this forum.
I am getting one problem in using bitmap index in a table.
I am having one table named tab1.
tab1
(a NUMBER,
b INTEGER,
c NUMBER,
d DATE DEFAULT sysdate,
e INTEGER DEFAULT 0,
f VARCHAR2(100 BYTE),
g NUMBER,
h INTEGER DEFAULT 0)
having B-Tree index on
a,trunc(d) and c
and having bitmapped index on e.
E is heavily updated by application and having values only 0,8,1003.
When I am using the below query:
select * from tab1 where e=0 ; It is not using bitmapped index.
Now I have created one table named tab2 having same column structure but having only bitmapped index on column e.
Here I am using the below query:
select * from tab2 where e=0 ; It is using index while search.
can anybody help me out the reason?
-
There are two different matters here.
1- Oracle optimizer would rely on indexes only if indexes are cost-effective. If cardinality of e=0 is too high Oracle may decide FTS is more efficient.
2- Bitmap indexes are not supposed to work alone. They are supposed to team up with other bitmap indexes in a process called star-transformation.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Reply from AWR and ADDM Reports
Hi,
I had analyzed AWR and ADDM Reports. There it was showing that the query running on bitmapped indexed column taking Highest Physical Reads and next to highest buffer gets. The CPU time is also higher for that query.
I had tried using hints to forcefully use the index but it was not using the index.
And also from ADDM Reports it is showing high IO for that query. Please find the details from ADDm reports:
RATIONALE: The I/O usage statistics for the object are: 3209 full object
scans, 9849769 physical reads, 144 physical writes and 0 direct
reads.
RATIONALE: The SQL statement with SQL_ID "1jvz9xkmfp0jq" spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 1jvz9xkmfp0jq
I had deleted the original query .
The query was :
select * from tab1 where e=0 and rownum<3000;
-
you didn't read my post, don't you?
How is data distribution for your 0,8,1003 values on "e" column? is it any close to normal distribution?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
Yes. Pablo is right. It depends on data distribution only for index scanning.
-
Bitmap index
Hi,
In the table the data meaning is as follows:
0 -> command has not been populated and just the data is inserted in the table.
8 -> Command executed successfully .
1003 -> command execution failure.
Like these data are distributed.
Once data is inserted in the table the status field is inserted =0. after commit when process starts the fields are updated as 5 where it is showing the commands are psocessing and after that the filelds are updated as 8 and 1003 according to the command execution status.
But why I am asking the question that:
How bitmap index depends on other index,
because in tab1 the bitmap index is not working with other indexes for the query.
But if I am creating same table (tab2) with same column but only bitmap index is there , then the bitmap index is working for the same query.
One thing I have done today for the table tab1.
I have dropped bitmapped index and created one B-Tree index, but still I have find out the indes is not working.
Can you please share your knowledge that how index works with other indexes?
-
Originally Posted by kallol.chakra
0 -> command has not been populated and just the data is inserted in the table.
8 -> Command executed successfully .
1003 -> command execution failure.
Like these data are distributed.
Once data is inserted in the table the status field is inserted =0. after commit when process starts the fields are updated as 5 where it is showing the commands are psocessing and after that the filelds are updated as 8 and 1003 according to the command execution status.
That's the logic of the application not the way data is distributed.
We are asking for cardinality, meaning how many rows have e=0, how many rows have e=8 and how many rows have e=1003
Research indexes + cardinality
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
What is explain plan when you are not using
1) select * tab1 where e=0;
2) select a,b,c from tab1 where e=0;
Did you generate histogram using dbms_stats for same table?
Thanks and regards,
Gitesh Trivedi
http://www.dbametrix.com
-
Originally Posted by giteshtrivedi
What is explain plan when you are not using
1) select * tab1 where e=0;
2) select a,b,c from tab1 where e=0;
1- Little confused about "not using" phrasing.
2- Provided stats do not change in between execution of the two queries execution plan would be dependent on predicate - I see the same predicate on both queries.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
Sorry that was typo error.
Did you generate histogram for same column on table?
What is distinct values and count in column "e"?
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|