Oracle Enhancements in Analytical SQL and Materialized Views

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Enhancements in the MERGE Statement

The basic MERGE statement has the following structure:

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
When MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>

Example

MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
    c.first_name = e.first_name,
    c.last_name = e.last_name,
    ...
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name,
    e.last_name, e.email, e.phone_number,
    e.hire_date, e.job_id, e.salary,
    e.commission_pct, e.manager_id,
    e.department_id)

In Oracle 10g, you can use a WHERE clause in a MERGE statement�s UPDATE or INSERT clause:

MERGE USING product_Changes s
INTO products p
ON (p.prod_id = s.prod_id)
WHEN MATCHED THEN UPDATE
SET p.prod_list_price = s.prod_new_price
WHERE p.prod_status <> "EXPIRED"
WHEN NOT MATCHED THEN INSERT
SET p.prod_list_price = s.prod_new_price
WHERE s.prod_status <> "EXPIRED"

You can use DELETE caluse with MERGE statement and it must be embedded inside the UPDATE statement.

The DELETE clause in a MERGE operation will evaluate only the updated values (values updated by the UPDATE clause) and not the original values that were evaluated by the UPDATE clause.

MERGE USING product_changes s
INTO products p ON (d.prod_id = s.prod_id)
WHEN MATCHED THEN
UPDATE SET d.prod_list_price =
s.prod_new_price,
d.prod_status = s.prod_new_status
DELETE WHERE (d.prod_status = "OLD_ITEM")
WHEN NOT MATCHED THEN
INSERT (prod_id, prod_list_price, prod_status)
VALUES (s.prod_id, s.prod_new_price,
s.prod_new_status)

Using Partitioned Outer Joins

Partitioned outer joins help turn sparse data into dense data, you thus have faster performance and a better reporting format.

The partitioned outer join is ideal for time dimensions, but it can be used for any kind of dimensions.

SELECT .....
FROM table_reference
PARTITION BY (expr [, expr ]... )
RIGHT OUTER JOIN table_reference

and

SELECT .....
FROM table_reference
LEFT OUTER JOIN table_reference
PARTITION BY {expr [,expr ]...)

Using the SQL MODEL Clause

MODEL clause enables you to generate multidimensional output query in the database.

Example:

SELECT country, product, year, sales
FROM sales_view
WHERE country IN ('Mexico', 'Canada')
MODEL
PARTITION BY (country)
DIMENSION BY (product, year)
MEASURES (sale sales)
RULES
(sales['Kleenex', 2005] =
         sales['Kleenex', 2004] +
         sales['Kleenex',2003],
   sales['Pampers', 2005] =
         sales['Pampers', 2004],
   sales['All_Products', 2005] =
         sales['Kleenex', 2005] +
         sales['Pampers',2005])
ORDER BY country, product, year

You can specify that Oracle should evaluate the rules in either of the following two ways:

SEQUENTIAL ORDER: Oracle will evaluate a rule in the order it appears in the MODEL clause.

AUTOMATIC ORDER: Oracle will evaluate the rule on the basis of the dependencies between the various rules in the MODEL clause.

By default, the RULES keyword operates with the UPSERT specification. You can use the UPDATE option. This specification can be applied in the RULES level or in a specific rule-level.