Top features of ORACLE 11g ETL capabilities

Oracle 11g features

Oracle Database 11g offers terrific ETL capabilities that enable a newer way to load data into a database: the transform-while-loading method. By using the Oracle database to perform all the ETL steps, you can efficiently perform the typically laborious ETL processes.

Oracle provides you with a whole set of complementary tools and techniques aimed at reducing the time needed to load data into the database while simplifying the work involved.

Oracle's ETL solution includes the following components:

External tables: External tables provide a way to merge the loading and transformation processes. Using external tables will enable you to eliminate cumbersome and time-consuming intermediate staging tables during data loading.

Multitable inserts: Using the multitable insert feature, you can insert data into more than one table at the same time, using different criteria for the various tables. This capability eliminates the additional step of first dividing data into separate groupings and then performing data loading.

Upserts: This is simply a made-up name indicating the technique by which you can either insert data into a table or just update the rows with a single SQL statement: MERGE. The MERGE statement will insert new data and update data if the rows already exist in the table.

• This simplifies your loading process because you don't need to worry about whether a table already contains the data.

Table functions: Table functions produce a set of rows as output. Table functions return a collection type instance (nested table and VARRAY data types). Table functions are similar to views, but, instead of defining the transform declaratively in SQL, you define it procedurally in PL/SQL.

Table functions are a great help when you're doing large and complex transformations, because you can perform the transformations before loading data into a data warehouse.

Transportable tablespaces: These tablespaces provide you with an efficient and speedy way to move data from one database to another. For example, you can migrate data between an OLTP database and a data warehouse using transportable tablespaces.