C HA PTER 13 LOA DI NG A ND T RANS FORMING DA TA in Font

Printer Code-39 in Font C HA PTER 13 LOA DI NG A ND T RANS FORMING DA TA

unconditional inserts. The structure of the multitable insert varies depending on whether all or only some of the source table s rows are being loaded into the target tables.

The performance gain from using a multitable insert is directly proportional to the complexity of the data and the number of target tables. Oracle claims that you can achieve a processing speed gain of 400 percent or more.

When you load all rows of a table, you can use either an unconditional all row insert or a conditional all row insert. In the following example, the source table is called sales_activity, whose data is loaded at the same time into two destination tables: sales and cost. The unconditional insert uses the keywords INSERT ALL, meaning that all the source rows (sales_activity) are loaded into the sales and cost tables. SQL> INSERT ALL INTO target1 VALUES (product_id, customer_id, sysdate, product_quantity) INTO target2 VALUES (product_id,sysdate,product_price,product_discount) SELECT s.product_id, s.customer_id, sysdate, s.product_quantity, s.product_price, s.product_discount FROM source s; After the INSERT ALL keywords, there are two INTO statements, each denoting an insert into a separate table. Notice that the SELECT statement contains all the necessary columns required by both INTO statements for inserting into the two tables. The conditional insert of all rows from the source table is similar to the unconditional version, except that the keyword WHEN indicates the conditions under which the inserts will be made. The following example shows how to perform a conditional all-row insert: SQL> INSERT ALL WHEN product_id IN(SELECT product_id FROM primary) THEN INTO target1 VALUES (product_id, customer_id, sysdate, product_quantity) WHEN product_id IN (SELECT product_id FROM secondary) THEN INTO target2 VALUES (product_id, sysdate, product_price, product_discount) SELECT s.product_id, s.customer_id, sysdate, s.product_quantity, s.product_price, s.product_discount FROM source s; This example still inserts all the rows from sales_data, because it uses the key phrase INSERT ALL.

Sometimes, you re interested in loading only some rows from a table, either based on a condition or unconditionally. You can do this in a multitable insert by using the keywords INSERT FIRST. Listing 13-11 shows how only some of the source table s rows are loaded into each target table, based on a separate condition for each table.

Listing 13-11. Partial Loading of Rows from the Source Table SQL> INSERT FIRST WHEN (quantity_sold > 10 AND product_id <1000) THEN INTO targetA VALUES (sysdate,product_id, customer_id, quantity_sold)) WHEN quantity_sold <= 10 and product_id >10000 THEN INTO targetB VALUES (sysdate,product_id, customer_id, quantity_sold) ELSE INTO targetC VALUES (time_id, cust_id, prod_id, sum_quantity_sold) SELECT s.time_id, s.cust_id, s.prod_id, p.prod_weight_class, SUM(amount_sold) AS sum_amount_sold, SUM(quantity_sold) AS sum_quantity_sold FROM sales s, products p WHERE s.prod_id = p.prod_id AND s.time_id = TRUNC(sysdate) GROUP BY s.time_id, s.cust_id, s.prod_id, p.prod_weight_class;

You can use Oracle s table functions to perform efficient data transformations. Table functions produce a collection of transformed rows that can be queried just like a regular table s data. Oracle table functions are an excellent example of Oracle s sophisticated transform-while-loading paradigm. Table functions can take a set of rows as input and return a transformed set of rows. When you query a table function in a statement, the function returns a collection type instance representing the rows in a table. The collection types can be either a VARRAY or a nested table. Table functions allow you to use PL/SQL, C, or Java with SQL without any problems. Table functions make the traditional use of staging tables redundant. You don t need to create any intermediate tables to perform data transformations before loading data into the final data warehouse tables. Three features make table functions a powerful means of performing fast transformation of data sets: Streaming: This refers to the direct transmission of results from one process to the other without any intermediate steps. The way in which a table function orders or clusters rows that it fetches from cursor arguments is called data streaming. Parallel execution: This refers to the concurrent execution of the functions on multiprocessor systems. Pipelining: This technique lets you see the results of a query iteratively, instead of waiting for the entire result set to be batched and returned. Pipelining can thus help table functions reduce the response time by sending results as soon as they are produced in batches. You also have the option of having the table function immediately return rows from a collection by using pipelining. The elimination of (sometimes multiple) staging tables and the lack of need for any manual coding of parallel processing makes the pipelined parallel processing provided by table functions very attractive during large-scale data loading and transformation.