Posted by “Back in the day”, bitmap indexes on tables meant any kind of DML was going kill you either for the concurrency, or the performance.
The concurrency issue still applies, but with each version of Oracle, the cost of maintaining bitmaps is still significant but might still be acceptable for you
We can see some simple results as below
SQL> create table my_tab
2 ( ticket_seq number(10),
3 c1 number(10),
4 c2 number(10),
5 c3 number(10),
6 c4 number(10)
7 ) tablespace users;
Table created.
SQL> insert into my_tab
2 select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3 from dual
4 connect by level <= 50000;
50000 rows created.
SQL> create bitmap index IX1 on my_tab ( c1) tablespace users;
Index created.
SQL> create bitmap index IX2 on my_tab ( c2) tablespace users;
Index created.
SQL> create bitmap index IX3 on my_tab ( c3) tablespace users;
Index created.
SQL> create bitmap index IX4 on my_tab ( c4) tablespace users;
So we have 4 bitmap indexes, and we’ll see what how long it takes to do 500,000 inserts…
SQL> set timing on
SQL> insert into my_tab
2 select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3 from dual
4 connect by level <= 500000;
500000 rows created.
Elapsed: 00:00:07.45
SQL> commit;
Commit complete.
SQL> select segment_name, bytes
2 from dba_segments
3 where segment_name in ( 'IX1','IX2','IX3','IX4');
SEGMENT_NAME BYTES
---------------------------------------- ----------
IX4 4194304
IX3 4194304
IX2 4194304
IX1 4194304
7 seconds for 500,000 rows isn’t too bad really…and the indexes have grown to 4 meg. Now we repeat the test in the more conventional sense, namely, set the indexes to unusable and then rebuild afterwards.
SQL> drop table my_tab purge;
Table dropped.
SQL> create table my_tab
2 ( ticket_seq number(10),
3 c1 number(10),
4 c2 number(10),
5 c3 number(10),
6 c4 number(10)
7 ) tablespace users;
Table created.
SQL> insert into my_tab
2 select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3 from dual
4 connect by level <= 50000;
50000 rows created.
SQL> create bitmap index IX1 on my_tab ( c1) tablespace users;
Index created.
SQL> create bitmap index IX2 on my_tab ( c2) tablespace users;
Index created.
SQL> create bitmap index IX3 on my_tab ( c3) tablespace users;
Index created.
SQL> create bitmap index IX4 on my_tab ( c4) tablespace users;
Index created.
SQL> alter index ix1 unusable;
Index altered.
SQL> alter index ix2 unusable;
Index altered.
SQL> alter index ix3 unusable;
Index altered.
SQL> alter index ix4 unusable;
Index altered.
SQL> insert into my_tab
2 select rownum, mod(rownum,20000), mod(rownum,20000), mod(rownum,20000), mod(rownum,20000)
3 from dual
4 connect by level <= 500000;
500000 rows created.
Elapsed: 00:00:02.13
As expected, the population is much faster, but the indexes need to be rebuilt and are unavailable for queries. You may be better off leaving them there, you may not be… your mileage may vary.
SQL> commit;
Commit complete.
SQL> alter index ix1 rebuild;
Index altered.
Elapsed: 00:00:00.22
SQL> alter index ix2 rebuild;
Index altered.
Elapsed: 00:00:00.19
SQL> alter index ix3 rebuild;
Index altered.
Elapsed: 00:00:00.20
SQL> alter index ix4 rebuild;
Index altered.
Elapsed: 00:00:00.19
SQL> select segment_name, bytes
2 from dba_segments
3 where segment_name in ( 'IX1','IX2','IX3','IX4');
SEGMENT_NAME BYTES
---------------------------------------- ----------
IX4 3145728
IX3 3145728
IX2 3145728
IX1 3145728
But notice that the indexes get nicely recompacted on rebuild, so even if you need to leave indexes on during some DML, either adhoc or regular, its worth scheduling a rebuild at some stage.
Learning is not a spectator sport 