| BitMap Index Issue [message #516893] |
Thu, 21 July 2011 05:59  |
|
|
Hi,
In of my table, full table scan was happening. which had 4 columns.
1st,2nd & 3rd columns have only 2-values
4th column has 9-possible values.
I have created bitmap for all four columns.
The performance has been increased after doing so.
Will it have any impact for creating so many indexes in one table?
can i create index for column having 9-possible values
|
|
|
|
|
|
|
|
| Re: BitMap Index Issue [message #516911 is a reply to message #516894] |
Thu, 21 July 2011 07:19  |
|
|
In my query, i'm making use of 2-tables, where i'll be taking data
by comparing Primary key of one of the table.
The primay key table is already indexed..
I have created bitmap indexing for other table.
My query is simple select statement... where no other DML operations happens..
Creating Bitmap index does have any impact??
My query look like this
SELECT a.ID
,a.Name
,a.Type
,b.AssignTo
,b.Standard
FROM Document b
,Read a
WHERE b.DID = a.ID
AND a.Status='expired'
AND a.State ='Y'
AND a.TYPE ='New'
AND b.DEFAULT = 'Yes'
AND b.FORMAT = ' view'
AND b.LAYOUT = 'Wide'
AND (
b.ASSIGNTO = 'c'
OR b.ASSIGNTO = 'd'
);
.
I have created bitmap index on Document column..
Will have any impact on performance
|
|
|
|
| Re: BitMap Index Issue [message #516920 is a reply to message #516911] |
Thu, 21 July 2011 07:44 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Bitmaps cause DML operations on tables - delete, update, insert - to slow down.
If you've got a data warehouse type system where the data isn't modified much this isn't a problem.
If you have OLTP system where the data is modified a lot it's a big problem.
|
|
|
|
|
|
| Re: BitMap Index Issue [message #517416 is a reply to message #516920] |
Mon, 25 July 2011 22:00 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Small clarification: the severe effects of DML on bitmap indexes apply to row-wise DML (INSERT...VALUES, row-by-row UPDATE/DELETE, FORALL INSERT/UPDATE/DELETE). Set-based DML is impacted about the same as for normal b-tree indexes over larger volumes.
For OLTP systems, DML overheads are not the reason you avoid bitmap indexes - it is because with 2 or more concurrent sessions you will inevitably encounter locking / deadlocking issues.
For Data Warehouses, you can avoid bitmap DML problems by applying set-based DML, or by disabling and rebuilding indexes during the ETL.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
