update is not slow with bitmap index

[pranavgovind]

Hi, I heard that, update will be very slow in bitmap index. I just created a table with bitmap index and ran the update on the table. Again i dropped the bitmap index and ran the same update. I do not see any time difference.

My question is, does bitmap index slow down the update statement??

02:19:31 SQL> create table t_bitmap(no number,name varchar2(20),sex char(1));

Table created.

02:19:31 SQL>
02:19:31 SQL> create bitmap index bidx on t_bitmap(sex);

Index created.

02:19:31 SQL>
02:19:40 SQL> declare
02:19:40 2 cursor c1 is select * from t_bitmap;
02:19:40 3 x number:=1;
02:19:40 4 begin
02:19:40 5 for i in 1..10000 loop
02:19:40 6 insert into t_bitmap values(x,'Scott','M');
02:19:40 7 x := x+1;
02:19:40 8 end loop;
02:19:40 9 end;
02:19:40 10 /

PL/SQL procedure successfully completed.

02:19:44 SQL>
02:20:12 SQL>
02:20:12 SQL> declare
02:20:12 2 cursor c1 is select * from t_bitmap;
02:20:12 3 x number:=10001;
02:20:12 4 begin
02:20:12 5 for i in 1..10000 loop
02:20:12 6 insert into t_bitmap values(x,'Fran','F');
02:20:12 7 x := x+1;
02:20:12 8 end loop;
02:20:12 9 end;
02:20:12 10 /

PL/SQL procedure successfully completed.

02:20:15 SQL> commit;

Commit complete.

02:20:17 SQL> execute DBMS_STATS.gather_table_stats (ownname=> 'SCOTT',tabname => 'T_BITMAP',estim
ate_percent => 100,method_opt=> 'FOR ALL COLUMNS SIZE 1',CASCADE=> TRUE);

PL/SQL procedure successfully completed.

02:20:44 SQL>
02:20:45 SQL>
02:21:16 SQL>
02:21:16 SQL>
02:21:16 SQL> declare
02:21:16 2 cursor c1 is select * from t_bitmap;
02:21:16 3 x number:=1;
02:21:16 4 begin
02:21:16 5 for i in c1 loop
02:21:16 6 update t_bitmap set sex=sex where no = x;
02:21:16 7 x := x+1;
02:21:16 8 end loop;
02:21:16 9 end;
02:21:16 10 /

PL/SQL procedure successfully completed.

02:22:08 SQL> rollback;

Rollback complete.

02:22:29 SQL> drop index bidx;

Index dropped.

02:22:32 SQL>
02:22:37 SQL>
02:22:37 SQL>
02:22:37 SQL> declare
02:22:37 2 cursor c1 is select * from t_bitmap;
02:22:37 3 x number:=1;
02:22:37 4 begin
02:22:37 5 for i in c1 loop
02:22:37 6 update t_bitmap set sex=sex where no = x;
02:22:37 7 x := x+1;
02:22:37 8 end loop;
02:22:37 9 end;
02:22:37 10 /

PL/SQL procedure successfully completed.

02:23:29 SQL>

[malay_biswal]

Do the test with at least millions of records and then post your finding.

[pranavgovind]

Malay, I did the test for 6 million records. I gained two minutes when i run with out bitmap index. But the two minutes is not huge big difference. But anyhow, i am able to see little difference. Please let me know if you have any other thoughts. Thanks

11:52:12 SQL> create table t_bitmap(no number,name varchar2(20),sex char(1));

Table created.

11:52:22 SQL> create bitmap index bidx on t_bitmap(sex);

Index created.

11:52:22 SQL> create index idx on t_bitmap(no);

Index created.

11:52:23 SQL> declare
11:52:39 2 cursor c1 is select * from t_bitmap;
11:52:39 3 x number:=1;
11:52:39 4 begin
11:52:39 5 for i in 1..3000000 loop
11:52:39 6 insert into t_bitmap values(x,'Scott','M');
11:52:39 7 x := x+1;
11:52:40 8 end loop;
11:52:40 9 commit;
11:52:40 10 end;
11:52:40 11 /

PL/SQL procedure successfully completed.

12:12:06 SQL>
12:13:43 SQL> declare
12:13:43 2 cursor c1 is select * from t_bitmap;
12:13:43 3 x number:=3000001;
12:13:43 4 begin
12:13:43 5 for i in 1..3000000 loop
12:13:43 6 insert into t_bitmap values(x,'Fran','F');
12:13:43 7 x := x+1;
12:13:43 8 end loop;
12:13:43 9 commit;
12:13:43 10 end;
12:13:43 11 /

PL/SQL procedure successfully completed.

12:35:10 SQL>
12:35:11 SQL> execute DBMS_STATS.gather_table_stats (ownname=> 'SCOTT',tabname => 'T_BITMAP',estimat
e_percent => 100,method_opt=> 'FOR ALL COLUMNS SIZE 1',CASCADE=> TRUE);

PL/SQL procedure successfully completed.

12:42:56 SQL>
12:44:44 SQL>
12:44:44 SQL>
12:44:45 SQL>
12:44:45 SQL> declare
12:44:45 2 cursor c1 is select * from t_bitmap;
12:44:45 3 x number:=1;
12:44:45 4 begin
12:44:45 5 for i in c1 loop
12:44:45 6 update t_bitmap set sex=sex where no = x;
12:44:45 7 x := x+1;
12:44:45 8 end loop;
12:44:45 9 end;
12:44:45 10 /

PL/SQL procedure successfully completed.

12:59:43 SQL> rollback;

Rollback complete.

13:13:40 SQL> drop index bidx;

Index dropped.

14:04:23 SQL> execute DBMS_STATS.gather_table_stats (ownname=> 'SCOTT',tabname => 'T_BITMAP',estimat
e_percent => 100,method_opt=> 'FOR ALL COLUMNS SIZE 1',CASCADE=> TRUE);

PL/SQL procedure successfully completed.

14:05:33 SQL> declare
14:06:28 2 cursor c1 is select * from t_bitmap;
14:06:28 3 x number:=1;
14:06:28 4 begin
14:06:28 5 for i in c1 loop
14:06:28 6 update t_bitmap set sex=sex where no = x;
14:06:28 7 x := x+1;
14:06:29 8 end loop;
14:06:29 9 end;
14:06:29 10 /

PL/SQL procedure successfully completed.

14:19:31 SQL>

[malay_biswal]

2 min may not seem big improvement.
BTW, i was asking to test with a table with millions of records not with millions looping update.
The reason why you should not compare the result
1. If Table got less amount of data
2. The table has only 3 columns,Only 3 columns, that means less space for physical storage. If you simulate with some kind of real time table, i think you'll able to gain more time.

[Bore]

You could try to simulate 10 sessions updating same table, different rows but with the same values in them

[PAVB]

If you are thinking in bitmap indexes you are probably thinking in doing star-transformation on a FACT table.

If this is the case it doesn't make any sense to test with a single index; your FACT table would probably have tens of bitmap indexes.

Do yourself a big favor and test on at least a 50 columns table with 30 bitmap indexes; 25 million rows or so. Your simulated ETL process should touch not least than 10% of the rows.

I would suggest not only look at the improvement in performance but also at the size of the bitmap indexes before and after the ETL simulation.

[pranavgovind]

Hi Pavb, Malay & Bore, I simulated the bitmap index as you said and i am able to see the performance slow down in bitmap index. Thanks again and appreicate every one in this thread.