Oracle Articles
Designing Efficient ETL Processes in Oracle:
Introduction:
Data conversion - the process of migrating application data - is a major component of many database related projects. This process essentially entails three sub-processes: 1) Extracting data from the source system, 2) Transforming data to the format required by the target system and 3) Loading data into the target system - hence the abbreviation ETL. Oracle offers a variety of database features for implementing ETL processes. Additionally, there is the relatively new Oracle Warehouse Builder - a full featured ETL tool. There are also other tools by third party vendors such as Informatica. However, despite this recent proliferation of ETL tools, many Oracle based data migration projects continue to use homegrown programs (mainly PL/SQL) for ETL. This is mainly because smaller projects cannot justify the cost and learning time required for effective use of commercial ETL tools. Besides, doing it oneself offers much more control over the process.
As with any programming project there are efficient and (infinitely more) inefficient ways to design and implement ETL processes. This article offers some tips for the design of efficient ETL programs in an Oracle environment. The tips are listed in no particular order. I hope you find them helpful in your projects
Tip 1: Use external tables to load data from external flat files
The traditional workhorse for loading external (flat file) data into Oracle is the SQL*Loader utility. Since version 9i, external tables offer an easier, and almost as performant, route to load data. Essentially, external tables enable us to access data in an external file via an Oracle table. Here's a simple example:
The following data is available in a text file called test.txt:
scott,cio
kailash,dba
jim,developer
We want to pull this data into Oracle using an external table. The first step is to create a directory object in Oracle, which points to the operating system directory in which the above file resides. Assume this is an operating system directory called c:\test. The SQL to make this directory accessible from Oracle is:
create or replace directory test_dir as 'c:\test';
The user must have the create any directory privilege in order to execute this SQL. We then define an external table based on our text file:
create table ext_test(
name varchar2(10),
job varchar2(10))
organization external
(type oracle_loader
default directory test_dir
access parameters (fields terminated by ',')
location (test_dir:'test.txt'))
reject limit 0
It would take us too far afield to go into each of the clauses in the above statement. Check the Oracle docs for further details. The relevant book is the Oracle Utilities Guide. There are also several third-party articles available online - do a google search on "oracle external table" to locate some of these.
To access the external data we simply issue the following SQL statement from within Oracle:
select * from ext_test;
There are several access parameter options available to handle more complicated record formats - check the Oracle documentation for details.
Tip 2: Use set-based operations to process data
Most ETL processes deal with large numbers of records. Each record must be cleaned at the field level and then loaded into the target system. Programmers unfamiliar with SQL tend to use procedural methods ("for" loops, for example) to deal with such situations. With some thought, however, it is often possible to construct an SQL statement that is equivalent to the procedural technique. The method using SQL should be preferred, as SQL processes the entire batch of records as a single set. Provided the statement isn't too complex, the SQL statement will generally run faster than the equivalent procedural code. Here's an (admittedly contrived) example to illustrate the point:
--execute from SQL Plus
set timing on
create table test (
id number,
name varchar2(128));
alter table test add constraint pk_test primary key (id);
declare
cursor c_test is
select
object_id,
object_name
from
all_objects;
begin
for r_test in c_test loop
insert into test
(id, name)
values
(r_test.object_id,lower(r_Test.object_name));
end loop;
commit;
end;
.
/
truncate table test;
declare
begin
insert into test (id, name)
select
object_id,
lower(object_name)
from
all_objects;
commit;
end;
.
/
You may need a larger number of rows to see an appreciable difference between the two methods. The advantage of the SQL based technique becomes apparent with greater data volumes.
Here are some tips that may help in converting procedural code to SQL:
- Replace procedural control statements with SQL decode or case: Often a procedural statement such as if....then....else can be replaced by a single SQL statement that uses the decode function or case statement. For simple conditionals the SQL option will almost always outperform procedural code. The decode function is useful when your branching criterion is based on discrete values - for example "Male" or "Female". The case statement would be preferred if the criterion is based on a continuous range - eg: any criterion that uses > or <. Here's an example of the two in action:
--DECODE: Classify by gender
select
ename,decode(gender,'M','Male','F','Female','Unknown')
from
employees;
--CASE: is the current day a weekend or weekday?
select
case when to_char(sysdate,'D')>'1' and to_char(sysdate,'D')<'6' then
'Weekday'
else
'Weekend'
end as day_type
from
dual;
- Pivoting: A common requirement of ETL processes is to pivot a table about a particular column, i.e. to convert rows to columns based on values in a specific column. This is easily achieved using the decode function. Consider the following example: we want to flatten out data in a table called SALES based on the MONTH column (which contains numbers ranging from 1 to 12). The SALES table has the following columns: REGION, YEAR, MONTH and SALES_DOLLARS (primary key REGION, YEAR and MONTH). The sales data is to be flattened out with each month's dollars appearing in a separate column - JAN_SALES, FEB_SALES .... DEC_SALES - appearing as a separate column. Here's how this can be done:
select
region
year,
min(decode(month,1,amount)) jan_sales,
min(decode(month,2,amount)) feb_sales,
min(decode(month,3,amount)) mar_sales,
min(decode(month,4,amount)) apr_sales,
min(decode(month,5,amount)) may_sales,
min(decode(month,6,amount)) jun_sales,
min(decode(month,7,amount)) jul_sales,
min(decode(month,8,amount)) aug_sales,
min(decode(month,9,amount)) sep_sales,
min(decode(month,10,amount)) oct_sales,
min(decode(month,11,amount)) nov_sales,
min(decode(month,12,amount)) dec_sales
from
sales
group by
region,
year
The grouping is required to collapse twelve records for the region / year into a single record. Note that you can use any grouping function (AVG, MAX, SUM, for example) if all key columns other than MONTH are to be preserved in the flattened table. If this isn't so, you will have to use SUM as you will be summing over records for a fixed month. In either case you should be sure you understand what you're getting!
- "Unpivoting": This is the converse of the above. Here we want to move data from several columns into a single column, based on some criterion. Say we have a table called FLAT_SALES with columns REGION, YEAR, JAN_SALES,....DEC_SALES (produced by the above query). An ETL process requires us to get the data in normalised format, i.e. columns REGION, YEAR, MONTH, SALES_DOLLARS, as in the original SALES table. Here's a query that will achieve this:
select
t1.region region,
t1.year year,
t2.r month,
decode(t2.r,
1,jan_sales,
2,feb_sales,
3,mar_sales,
4,apr_sales,
5,may_sales,
6,jun_sales,
7,jul_sales,
8,aug_sales,
9,sep_sales,
10,oct_sales,
11,nov_sales,
12,dec_sales) sales_dollars
from
flat_sales t1,
(select
rownum r
from
all_objects
where
rownum<=12) t2
The table t2 serves to produce 12 rows for each original row via a cartesian product (no joins). The query uses a decode on rownum to pick out the sales value for each month from the flattened table. Note that we could have used any table or view with 12 or more rows in the definition of t2. I chose ALL_OBJECTS as this view is generally available to all schemas, and contains a large number of rows.
- Using built-in functions: One can achieve fairly complex data transformations using Oracle's character SQL functions. The nice thing about these functions is that they can be embedded in SQL, so you get the full advantage of set-based operations. The usual suspects include: LENGTH, LOWER, LPAD, REPLACE, RPAD, SUBSTR, UPPER, (L)(R)TRIM. Some "lesser known" functions I have found useful in my ETL efforts are:
- CHR Returns the character associated with the specified ASCII code. This is useful when you want to remove non-printable characters such as carriage return (CR) and linefeed (LF) from input strings. You would do this using CHR in tandem with REPLACE like so:
REPLACE(input_string,CHR(13)||CHR(10)), 13 and 10 being the ASCII codes for CR and LF respectively. - INITCAP This capitalizes the first letter in each word of the input string. Every other letter is lower cased.
- INSTR Returns the location of a specified string within the input string.
All these functions have a range of invocation options. Check the Oracle SQL documentation for full details.
- Analytics: Since version 8i, Oracle has introduced analytical extensions to SQL. These enable one to do a range of procedural operations using SQL (yes, you read that right - procedural operations using SQL). Some operations possible using analytical SQL include: sub-grouping (different levels of grouping within a statement), ranking and other statistics over subsets of data, comparing values in different rows, and presenting summary and detailed data using a single SQL statement. Check out my article on analytic SQL to find out more.
Tom Kyte's site is a great resource for tips on converting procedural code to SQL - try searching on the keywords "ETL" and "analytic functions" for a start.
OK, after extolling the virtues of using SQL we have to admit that there are situations in which procedural code becomes unavoidable. In such cases you can still take advantage of set based processing by using bulk binding within your procedural code. Sections of the Oracle documentation on bulk binding are available here and here (free registration required). An introductory article on bulk binding is available here (dbasupport.com).
Tip 3: TRUNCATE tables when deleting all data
Deleting all data from a table is best done using the truncate statement. A truncate runs faster than a delete because it simply resets the table's high water mark to zero. Truncate is a very quick operation. Indexes are also truncated along with the table. A truncate is DDL and therefore cannot be rolled back - be sure you consider this before using truncate. Note that tables referenced by enabled foreign key constraints cannot be truncated unless the keys are disabled first. Here's a block of code that uses truncate to clean out the SALES table (which is referenced by enabled foreign key constraints):
declare
cursor c_referenced_by is
select
t1.constraint_name constraint_name,
t1.table_name table_name
from
user_constraints t1,
user_constraints t2
where
t1.constraint_type='R'
and
t1.r_constraint_name=t2.constraint_name
and
t2.table_name ='SALES';
begin
for r_referenced_by in c_referenced_by loop
execute immediate
'alter table '||r_referenced_by.table_name||' disable constraint '
||r_referenced_by.constraint_name;
end loop;
execute immediate
'truncate table sales';
end;
Tip 4: Use direct path inserts for loading data
A direct path insert offers a quick way to load large volumes of data into a table. It does this by bypassing the buffer cache and writing directly to datafiles. A direct path insert is done using the insert into ...select.. idiom, together with an append hint. The append hint causes the rows to inserted above the table's high water mark - any free space below the high water mark is not used. Here is the syntax for a direct path insert into SALES from SALES_STAGE:
insert /*+ append*/ into
sales
(region,
year,
month,
sales_dollars)
select
region,
year,
month,
sales_dollars
from
sales_stage;
A direct path insert by itself is faster than a conventional insert. Just how much mileage one gets depends on the volume of data loaded. The performance of direct path inserts can be further enhanced by doing the following before the load: - Bypassing constraint checks by disabling all constraints. Remember to re-enable these after the load is done.
- Suppressing redo generation by putting the table in nologging mode. If your table has a large number of non-unique indexes, you might also consider setting these to unusable state. This will suppress redo generation associated with index maintenance. The indexes can then be rebuilt after the load. Note that unique indexes should not be set unusable because the load will fail with an ORA-26026: unique index initially in unusable state error. Unique indexes can, however, be dropped before and recreated after the load. This is, in effect, what happens when the primary key constraint is disabled (as discussed in the previous point) - the unique index associated with the primary key is dropped.
An important consequence of suppressing redo is that the operation is unrecoverable. Be sure to coordinate such operations with your DBA so that she can schedule a backup of the database or relevant tablespaces after the load.
Here's a block of code that bypasses constraints checks and suppresses redo generation for the above insert:
declare
cursor c_constraints is
select
table_name,
constraint_name
from
user_constraints
where
table_name='SALES';
cursor c_indexes is
select
index_name
from
user_indexes
where
table_name='SALES'
and
uniqueness<>'UNIQUE';
begin
for r_constraints in c_constraints loop
execute immediate
'alter table '||r_constraints.table_name||' disable constraint '
||r_constraints.constraint_name;
end loop;
--optional truncate to clean out the table
--disable referencing constraints, if needed (see tip 3)
--execute immediate
--'truncate table sales';
for r_indexes in c_indexes loop
execute immediate
'alter index '||r_indexes.index_name||' unusable';
end loop;
execute immediate
'alter session set skip_unusable_indexes=true';
execute immediate
'alter table sales nologging';
insert /*+ append*/ into
sales
(region,
year,
month,
sales_dollars)
select
region,
year,
month,
sales_dollars
from
sales_stage;
execute immediate
'alter session set skip_unusable_indexes=false';
for r_indexes in c_indexes loop
execute immediate
'alter index '||r_indexes.index_name||' rebuild nologging';
end loop;
execute immediate 'alter table sales logging';
for r_constraints in c_constraints loop
execute immediate
'alter table '||r_constraints.table_name||' enable constraint '
||r_constraints.constraint_name;
end loop;
commit;
end;
For small data volumes, the overhead of disabling constraints and indexes, and making the table nologging will swamp the benefits gained. In general, the larger the load the greater the benefit of the foregoing actions. As always, benchmark before implementation in your loads.
The above code uses a lot of dynamic SQL so it can be heavy on database resources. The use of dynamic SQL is unavoidable because we need to perform the operations within a module (PL/SQL block, procedure or package). However, this should not cause performance problems for business users because ETL batch processes normally run during off-peak hours.
Tip 5: Use the MERGE command for upserts
A common requirement is the need to perform an "upsert" - i.e.: update a row if it exists, insert it if it doesnt. In Oracle (versions 9i and better) this can be done in one step using the MERGE statement. Here's an example that uses the EMP table of SCOTT schema. The example uses a table EMP_STAGE that holds updated and new records that are to be upserted into EMP. You need to be logged into the SCOTT schema (or any other schema with a copy of SCOTT.EMP) in order to run the example:
--create EMP_STAGE
create table emp_stage as select * from emp where 1=2;
--insert update records in EMP_STAGE
insert into emp_stage values
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),1800,NULL,20);
insert into emp_stage values
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),2200,300,30);
insert into emp_stage values
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp_stage values
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),9500,NULL,10);
insert into emp_stage values
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),8500,NULL,10);
--insert new records in EMP_STAGE
insert into emp_stage values
(7940,'WEBSTER','DBA',7782,to_date('23-1-1985','dd-mm-yyyy'),7000,NULL,10);
insert into emp_stage values
(7945,'HAMILL','DEVELOPER',7782,to_date('21-5-1985','dd-mm-yyyy'),6000,NULL,10);
insert into emp_stage values
(7950,'PINCHON','ANALYST',7782,to_date('20-10-1985','dd-mm-yyyy'),6000,NULL,10);
commit;
--MERGE records into EMP
merge into
emp e
using
emp_stage es
on
(e.empno=es.empno)
when matched then
update
set
e.sal=es.sal
when not matched then
insert
(e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno)
values
(es.empno,
es.ename,
es.job,
es.mgr,
es.hiredate,
es.sal,
es.comm,
es.deptno);
commit;
Three rows are inserted and five existing rows are updated by the above.
In Oracle 10g one can also add a conditional clauses to the insert and update portions of the merge statement. For example:
--MERGE records into EMP, except for DEVELOPERS
merge into
emp e
using
emp_stage es
on
(e.empno=es.empno)
when matched then
update
set
e.sal=es.sal
when not matched then
insert
(e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno)
values
(es.empno,
es.ename,
es.job,
es.mgr,
es.hiredate,
es.sal,
es.comm,
es.deptno)
where
es.job<>'DEVELOPER';
In this case the record for employee 7945 is not inserted.
The conditional clause can be in the update portion as well. In 10g it is also possible to delete rows from the destination table based on conditional criteria. Check the documentation for details.
Tip 6: Use heterogeneous services to access data in non-Oracle relational databases
Database links are often used to transfer small volumes of data between Oracle databases. It is less well known that database links can also be set up between Oracle and non-Oracle databases. This is a useful feature, as ETL processes often need to access and transfer data to Oracle from third-party databases such as MS SQL Server. The standard way to do this is by exporting data from the non-Oracle database to a flat file, and then importing the data into Oracle via SQL Loader or external tables. Oracle Heterogeneous Services provides a single-step option to achieve the transfer. Heterogeneous Services come in two flavours: - Generic Heterogeneous Services: This option, which is bundled with the Oracle Server uses ODBC to connect to the non-Oracle database. A tutorial on accessing SQL Server using Generic Heterogeneous Services is available here .
- Transparent Gateways: These are extra cost options that are optimised for specific databases. They offer better performance compared to the Generic option because they are designed to exploit optimisations and features specific to particular databases. The mechanics of setting up Transparent Gateways is quite similar to the Generic option. Check the documentation that comes with the specific Transparent Gateway for further details. The documentation is hidden away in the relevant gateway installation directory. For example, the documentation for the MS SQL Server gateway sits in ORACLE_HOME/tg4msql.A tutorial on accessing SQL Server using Transparent Gateways is available here .
A warning on data transfer techniques that uses database links: the efficiency of the transfer depends on your network bandwidth and the quantity of data to be transferred. I have used it (with great success) to transfer small to moderate quantities of data (~100000 rows, rowsize ~1000 bytes) over co-located machines within corporate networks.
Closing Remarks
ETL processes present a technical challenge as they entail complex transformations and loads of large quantities of data within ever-shrinking time windows. As developers we need to use all the tricks in the book to speed up our ETL procedures. In this article I've outlined some of the techniques that I have used, with success, in several projects. I hope you find them useful in your work.
Back to the top |
