update is not slow with bitmap index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: update is not slow with bitmap index

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    update is not slow with bitmap index

    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>

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Do the test with at least millions of records and then post your finding.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Dec 2005
    Posts
    195
    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>

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    Last edited by malay_biswal; 12-28-2007 at 12:41 AM.
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    You could try to simulate 10 sessions updating same table, different rows but with the same values in them

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2005
    Posts
    195
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width