The success of a data warehouse is decided at design time

Consistent coding standards and a sound design let you respond flexibly to load peaks and changing requirements — often without additional hardware.

The loading process

Data is generally loaded with Oracle's SQL*Loader, which is flexible and easy to handle. One of its key advantages is that data can be loaded over SQL*Net. Higher load performance and tighter integration into the transformation process are achieved with external tables. The loader can generate the DDL for the external table from the control file. External tables created this way can then be used in select statements during the subsequent transformation process just like ordinary Oracle tables.

SQL / PL/SQL

sqlldr
  user_id/password
  control=control.ctl
  log=table_load.log
  external_table=generate_only

The transformation process

Oracle offers a wide range of options here. For more complex transformations, table functions are the tool of choice. Data from the external table some_table can be transformed by the function my_table_function, and the result of the following view is then simply added to the database.

SQL / PL/SQL

select * from
  table (
        my_table_function(
                       CURSOR(select
                                 field1, field2, field3
                                FROM
                                 some_table
                              )
                         )
        );

Update statements should always be coded as bulk updates. In the first step the update view is defined:

SQL / PL/SQL

create view v_upd_table as
 select a.rowid z_rowid, b.new_value
  from
 table_target a, table_source b
 where
  a.pk_column = b.pk_column;

This view is then used inside a package, where bulk updates should always be combined with a limit clause.

SQL / PL/SQL

declare
     type t_rowid is table of varchar2(18) index by pls_integer;
     type t_new_value is table of v_upd_table.new_value%type index by pls_integer;

     l_t_rowid t_rowid;
     l_t_new_value t_new_value;

     cursor c_upd is
       select * from v_upd_table;

     fetch_limit constant number := 10000;

begin
   open c_upd;

   loop
     fetch c_upd bulk collect
        into l_t_rowid, l_t_new_value
        limit fetch_limit ;

     pkg_common_logging.fetched(c_upd%ROWCOUNT);

   if c_upd%ROWCOUNT > 0 THEN
        forall i in l_t_rowid.first .. l_t_rowid.last

      update table_target set
         old_value = l_t_new_value(i)
         WHERE ROWID = CHARTOROWID (l_t_rowid(i));

      commit;

    end if;
   exit when l_t_rowid.count() < fetch_limit;

   end loop;
   close c_upd;

exception
  when others then
    pkg_common_logging.failed;
    close c_upd;
    raise;

end;