bitmap indexes… better in new versions

“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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s