Wednesday, June 22

Bitmap index

Been playing with bitmap indexes in Oracle on my FC4 machine all morning, our application really gets a boost when using them. I'm just afraid that the scares updates will be suffering and to slow, need to benchmark update speed as well.
My select benchmarks (very unscientific) on a 3 million row table give some pointers on speed;

Simple where x=y query
Full table scan:  3.50 sec
B-tree index: 0.65 sec
Bitmap index: 0.02 sec

Query with list where x in (y,z). Now here it gets interesting
Full table scan:  3.50 sec
B-tree index: 3.05 sec
Bitmap index: 0.05 sec
The index doesn't do much at all with a btree index since the inlist iterator didn't come in as it did when using the bitmap index.
I've put the execution plans for the second test in a file if someone is interested (view)


SQL of the day (on a completely different matter):
This query will summarize all connected users, since many applications spawn several hundred DB connections from the same application the query will group by username, application and the machine they are connecting from and also show the total number of connected user (not that internal Oracle connections will not be listed).
column Username format a15
column Application format a30
column Machine format a25
break on report
compute sum of "No. Sessions" on report

select
username "Username",
program "Application",
machine "Machine",
count(*) "No. Sessions"
from
v$session
where
username is not null
group by
username,
program,
machine
order by
username
/


1 comment:

John Wu said...

Here is a good article with information about update speed when bitmap index is used
http://technology.amis.nl/blog/?p=1420