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 secThe 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.
B-tree index: 3.05 sec
Bitmap index: 0.05 sec
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
count(*) "No. Sessions"
username is not null