Re: Simple SQL waiting on 'log file sync'

Hi Deepak,

> As I just explained in my other email, it was not the

> Bitmap Indexes (we were suspecting that too), but the

> 5-column concatenated index that was causing it.

Yes, in my observation in a *single threaded* application the bitmap index in insert performs comparable better than the b*tree index (probably as they are much smaller).

You may also try (if the time to rebuild/recreate the indices after each insert is to high) to use insert with append option - this leads with a large number of rows in many cases to much better performance as the indices are not updated on row per row basis but only once after the insert.

The one thing to be avoided on a table with bitmap indices on it is a large number of small inserts. This is not only slow but makes the bitmap index quickly grow in size and height. See the test case below.

(I saw in a similar case first time in my live an index with blevel=12:)

HTH Jaromir D.B. Nemec

test case

Note the difference in time and space in case when the 600k rows are inserted in one statement or chunked in small pieces into a table with a bitmap index.

Well the greatest difference is in clustering factor of the bitmap index, but I guess this is not relevant for the cost estimation:)

drop table t_bit;

create table t_bit (x number);

create bitmap index t_bit_ix on t_bit(x);

--

drop table t_bit2;

create table t_bit2 (x number);

create bitmap index t_bit2_ix on t_bit2(x);



SQL> insert /*+ append(t_bit) */ into t_bit select mod(rownum, 2000) from 
dual connect by level <= 600000;



600000 rows created.



Elapsed: 00:00:07.17

SQL> commit;



Commit complete.



Elapsed: 00:00:00.04

SQL> ---

SQL> declare

  2   j number;

  3  begin

  4   for j in 1 .. 300 loop

  5    insert /*+ append(t_bit2) */ into t_bit2 select mod(rownum, 2000) 
from dual connect by level <= 2000;

  6    commit;

  7   end loop;

  8  end;

  9  /



PL/SQL procedure successfully completed.



Elapsed: 00:13:59.68

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user, tabname=>'t_bit', 
cascade => true, estimate_percent => 100);



PL/SQL procedure successfully completed.



Elapsed: 00:00:04.42

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user, tabname=>'t_bit2', 
cascade => true, estimate_percent => 100);



PL/SQL procedure successfully completed.



Elapsed: 00:00:22.46

SQL> --

SQL> select

  2  table_name,blevel, leaf_blocks,clustering_factor

  3  from dba_indexes a

  4  where table_name in ('T_BIT','T_BIT2');



TABLE_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR

------------------------------ ---------- ----------- -----------------

T_BIT                                   1         250              2000

T_BIT2                                  2        2469            600000



Elapsed: 00:00:00.98

SQL> quit;

----- Original Message ----- 
From: "Deepak Sharma" <sharmakdeep_oracle_at_yahoo.com>
To: "Arul Ramachandran" <contactarul_at_gmail.com>
Cc: <oracle-l_at_freelists.org>
Sent: Friday, November 18, 2005 7:42 PM
Subject: Re: Simple SQL waiting on 'log file sync'



--
http://www.freelists.org/webpage/oracle-l