Query rewrite examples

Materialized view query rewrite:

Consider the following tables:

CREATE TABLE orders (  order_id NUMBER PRIMARY KEY,

  customer_id NUMBER,

  order_date DATE,

  order_total NUMBER

);

CREATE TABLE customers (

  customer_id NUMBER PRIMARY KEY,

  customer_name VARCHAR2(50),

  customer_city VARCHAR2(50)

);

We can create a materialized view that aggregates the orders table by customer and stores the results in a separate table:

CREATE MATERIALIZED VIEW mv_order_totals

BUILD IMMEDIATE

REFRESH COMPLETE ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT customer_id, SUM(order_total) as total_orders

FROM orders

GROUP BY customer_id;

Once this materialized view is created and enabled for query rewrite, Oracle can automatically use it to rewrite queries that involve the orders table and aggregate by customer. For example, the following query can be rewritten to use the materialized view instead of querying the orders table directly:

SELECT c.customer_name, m.total_orders

FROM customers c, mv_order_totals m

WHERE c.customer_id = m.customer_id;

Subquery factoring:

Consider the following query:

SELECT customer_id, SUM(order_total) as total_orders

FROM orders

WHERE order_date BETWEEN '01-JAN-2022' AND '31-DEC-2022'

GROUP BY customer_id;

Oracle can automatically rewrite this query using subquery factoring to improve performance:

WITH order_totals AS (

  SELECT customer_id, SUM(order_total) as total_orders

  FROM orders

  WHERE order_date BETWEEN '01-JAN-2022' AND '31-DEC-2022'

  GROUP BY customer_id

)

SELECT c.customer_name, o.total_orders

FROM customers c, order_totals o

WHERE c.customer_id = o.customer_id;

By using a common table expression (CTE) to compute the order totals first and then joining the customers table, Oracle can potentially reduce the amount of work needed to compute the query result.

View merging:

Consider the following view:

CREATE VIEW order_summary AS

SELECT customer_id, order_date, SUM(order_total) as total_orders

FROM orders

GROUP BY customer_id, order_date;

We can create another view that joins the order_summary view with the customers table:

CREATE VIEW customer_orders AS

SELECT c.customer_name, o.order_date, o.total_orders

FROM customers c, order_summary o

WHERE c.customer_id = o.customer_id;

If we execute the following query:

SELECT customer_name, SUM(total_orders) as yearly_orders

FROM customer_orders

WHERE order_date BETWEEN '01-JAN-2022' AND '31-DEC-2022'

GROUP BY customer_name;

Oracle can automatically merge the two views and produce a rewritten query that directly joins the orders and customers tables and aggregates by customer name:

SELECT c.customer_name, SUM(o.total_orders) as yearly_orders

FROM customers c, orders o

WHERE c.customer_id = o.customer_id

  AND o.order_date BETWEEN '01-JAN-2022' AND '31-DEC-2022'

GROUP BY c.customer_name;

By merging the two views, Oracle can potentially reduce the amount of work needed to compute the query result.