Load using Java

Cary Millsap is writing good things about measuring when tuning performance. His paper Making friends "Optimizing the insert program" is talking how to insert 10000 rows using Java. There are presented only possibilities to insert using Statement in a loop or using PreparedStatement in a loop. Should there be a alternative way also presented? Avoid looping statements in Java and populate all rows just in single call to the database.

Timing results inserting 10000 rows:

Array
Executed in 0 min 0 s 313 ms.
Prepared
Executed in 0 min 2 s 985 ms.

ARRAY:

create table si (s number(19),s2 number(19));
create or replace type nums is object ( n1 number(19), n2 number(19));
create type sit is table of nums;

    private static void insertArray(Connection c, List elems) 
            throws SQLException {
        ArrayDescriptor ad = ArrayDescriptor.createDescriptor("SIT", c);
        ARRAY a = new ARRAY(ad, c, elems.toArray());
        OraclePreparedStatement ops 
           = (OraclePreparedStatement)c.prepareStatement(
                        "insert into si " +
                        " select *" +
                        "    from table(?)");
        ops.setARRAY(1, a);

        System.out.println(ops.executeUpdate());
        ops.close();
        c.commit();
    }

Prepared:

    private static void insertPrepared(Connection c, List elems) 
            throws SQLException {
        PreparedStatement ps = c.prepareStatement("insert into si values(?,?)");
        Iterator i = elems.iterator();
        while ( i.hasNext()) 
        {
            Object[] o = i.next();
            ps.setInt(1, (Integer)o[0]);
            ps.setInt(2, (Integer)o[1]);
            ps.executeUpdate();
        }
        ps.close();
        
        c.commit();
    }

Here are some timings for other number of rows. It shows that if you are inserting 100-1000 rows this approach might be worth considering. Measure yourself. Be sure to have enough memory available for your Java.

ms      localhost      remote db 
rows    prepared array prepared array
10      94       140   93       141
100     125      141   125      156
1000    313      203   844      265
10000   1500     344   16297    453
100000  12063    1422  206210   2078
1000000 182063   java.lang.OutOfMemoryError: Java heap space  

insertintoselect.java

Only one

Last September Tom Kyte wrote a yet another good article in Oracle magazine The Trouble with Triggers. The point about triggers is very good, but the example and the "Correct Answer" I do not like. He is suggesting to model currencies to two separate tables. In my opinion the data model is entirely wrong if the same thing is modeled in many places. "Less code equals fewer bugs. Look for ways to write less code." Here is my approach to the problem without a materialized view and materialized view logs.

The problem itself is that
-a country must have a default currency
-there is only one default currency per country

create table country(country varchar2(2) constraint country_pk primary key);

create table currency(country references country
                    , currency varchar(3)
                    , is_primary varchar2(1) not null 
                                             check (is_primary in ('Y','N'))
                    , primary_country 
                            as (case when is_primary = 'Y' then country end) 
                               virtual 
                        constraint only_one_primary_u unique 
                    , constraint currency_pk primary key (currency,country)
                    )
;

alter table country
  add constraint must_have_at_least_one_primary
  foreign key(country)
  references currency(primary_country)
  deferrable initially deferred;

And that is it. One may see that there is used the "bad hack" behind only_one_primary_u. The unique constraint generates a function-based normal index.
The good thing about this is that constraints are visible in constraint list, not implemented only in a index or a materialized view.

select * 
  from user_constraints 
 where constraint_name 
    in ('ONLY_ONE_PRIMARY_U','MUST_HAVE_AT_LEAST_ONE_PRIMARY');

Populating

insert into country (country) values ('US');

insert into currency (country,currency,is_primary) values ('US','USD','N');

insert into currency (country,currency,is_primary) values ('US','USN','N');

insert into currency (country,currency,is_primary) values ('US','USS','Y');

commit;

insert into country (country) values ('FI');

insert into currency (country,currency,is_primary) values ('FI','EUR','Y');

insert into currency (country,currency,is_primary) values ('FI','FIM','N');

commit;

Changing the default

update currency set is_primary = 'N' where country = 'US' and currency = 'USS';

update currency set is_primary = 'Y' where country = 'US' and currency = 'USD';

commit;

select * from country;

COUNTRY
US
FI

select * from currency;

COUNTRY	CURRENCY	IS_PRIMARY	PRIMARY_COUNTRY
US	USD		Y		US
US	USN		N	
US	USS		N	
FI	EUR		Y		FI
FI	FIM		N	

A country must have a default currency

insert into country (country) values ('NO');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.MUST_HAVE_AT_LEAST_ONE_PRIMARY) violated - parent key not found

insert into country (country) values ('SW');

insert into currency (country,currency,is_primary) values ('SW','EUR','N');

commit;

ORA-02091: transaction rolled back
ORA-02291: integrity constraint (RAFU.MUST_HAVE_AT_LEAST_ONE_PRIMARY) violated - parent key not found

There is only one default currency per country

insert into currency (country,currency,is_primary) values ('FI','USD','Y');

ORA-00001: unique constraint (RAFU.ONLY_ONE_PRIMARY_U) violated

Multi table insert atomic?

Reading SQL and Relational Theory: How to Write Accurate SQL Code. Talking about constraints and data modifications. There should be able to update several tables in one atomic operation. Oracle multi table insert could be something to that direction, but no atomicy. Bug still open from version not any more supported.

SQL> create table a (a_id number(1) primary key
  2                , b_id number(1) not null);

Table created.

SQL> create table b (b_id number(1) primary key
  2                , a_id not null constraint b_a_fk references a );

Table created.

SQL> alter table a add
  2    constraint a_b_fk foreign key (b_id) references b
  3      deferrable initially deferred;

Table altered.

SQL> insert all into b values (x,x)
  2  into a values (x,x)
  3  select 0 x
  4    from dual;
insert all into b values (x,x)
*
ERROR at line 1:
ORA-02291: integrity constraint (RAFU.B_A_FK) violated - parent key not found


SQL> insert all into a values (x,x)
  2  into b values (x,x)
  3  select 1 x
  4    from dual;

2 rows created.

SQL> insert into a values (2,2);

1 row created.

SQL> insert into b values (2,2);

1 row created.

SQL> select * from a;

      A_ID       B_ID
---------- ----------
         1          1
         2          2

SQL> rollback;

Rollback complete.

SQL Donut

Oracle version of http://code.openark.org/blog/mysql/sql-pie-chart. Just change the val query to greate your own donuts.

######
Note 17.8.2009 wm_concat does not quarantee the right col_number order. Update to this post coming in future.
Note 2.9.2009 replaced wm_concat implementation to listagg. New in 11.2
######

:s size
:r radius
:t stretch

with val as (
select 'red' name_column, 1 value_column from dual 
union all
select 'blue' name_column, 2 value_column from dual 
union all
select 'orange' name_column, 3 value_column from dual 
union all
select 'white' name_column, 4 value_column from dual 
), ratio as (
select row_number() over (order by v.value_column,rownum) name_order
     , v.name_column
     , v.value_column
     , sum(value_column) over (order by value_column,rownum) accumulating_value
     , sum(value_column) over (order by value_column,rownum) 
            / sum(value_column) over () accumulating_value_ratio
     , 2*3.15*sum(value_column) over (order by value_column,rownum) 
            / sum(value_column) over () accumulating_value_radians
     , trunc(100*value_column / sum(value_column) over (),2) pc
  from val v
), t1 as (
select level-1 value from dual connect by level <= to_number(:s)*to_number(:t)
), t2 as (
select level-1 value from dual connect by level <= :s
), c as (
select '#;o:X"@+-=123456789abcdef' as colors from dual
), ro as (
select t1.value col_number
     , t2.value row_number
     , t1.value/:t - (:s-1)/2 dx
     , (:s-1)/2 - t2.value dy
  from t1, t2
)
, ro2 as (
select ro.*, case when dx = 0 
                  then 3.14/2 
                  else (atan(abs(dy/dx))) 
              end abs_radians
  from ro)
, ro3 as (
select ro2.*, case when sign(dy) >= 0 and sign(dx) >=0 then abs_radians
                   when sign(dy) >= 0 and sign(dx) <=0 then 3.14-abs_radians  
                   when sign(dy) <= 0 and sign(dx) <=0 then 3.14+abs_radians  
                   when sign(dy) <= 0 and sign(dx) >=0 then 2*3.14-abs_radians  
               end radians 
  from ro2
) 
select listagg(case when round(sqrt(power(col_number/:t-0.5-(:s-1)/2, 2) 
                       + power(row_number-(:s-1)/2, 2))) BETWEEN :r/2 AND :r
                    then rpad(name_order,:t,name_order)
                    else rpad(' ',:t,' ')
                    end) within group (order by col_number) circle
  from (select substr(colors, name_order, 1) name_order
       , row_number
       , col_number
       , row_number() over (partition by col_number,row_number 
                                order by name_order) c
    from ratio, ro3, c
   where accumulating_value_radians >= radians 
) where c = 1
group by row_number
union all
select rpad(substr(colors,name_order,1),:t,substr(colors,name_order,1))||' '||
       name_column||' '||
       value_column||' ('|| 
       pc||'%)'
  from ratio, c
;


CIRCLE
        oooooo;;;;;;;;      
    oooooooooo;;;;;;;;;;;;  
  oooooooo          ;;######
  oooooooo          ########
  oooooooo          ::::::::
    oo::::::::::::::::::::  
        ::::::::::::::      
## red 1 (10%)
;; blue 2 (20%)
oo orange 3 (30%)
:: white 4 (40%)