🔧 Materialized view in PLSQL

🕛 Zeit seit Veröffentlichung: 107 Tage, 3 Stunden 52 Minuten
📆 Veröffentlicht am: 01.10.2024 um 16:44 Uhr
Nachrichtenbereich: 🔧 Programmierung
🔗 Quelle: dev.to

Here’s a simple example of creating a materialized view with sample data.

Step 1: Create a sample table and insert data

CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
order_total NUMBER
);

INSERT INTO orders VALUES (1, 101, DATE '2024-09-01', 150);
INSERT INTO orders VALUES (2, 102, DATE '2024-09-02', 200);
INSERT INTO orders VALUES (3, 101, DATE '2024-09-03', 100);
INSERT INTO orders VALUES (4, 103, DATE '2024-09-03', 250);
INSERT INTO orders VALUES (5, 102, DATE '2024-09-04', 300);

COMMIT;

Step 2: Create the materialized view

CREATE MATERIALIZED VIEW customer_sales_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT customer_id, SUM(order_total) AS total_sales
FROM orders
GROUP BY customer_id;

Explanation:

BUILD IMMEDIATE: The view is populated immediately.

REFRESH COMPLETE: The view will be fully refreshed each time you refresh it.

ON DEMAND: The view will only be refreshed when you explicitly refresh it.

Step 3: Query the materialized view

SELECT * FROM customer_sales_mv;

Output:

This materialized view shows the total sales for each customer, based on the orders table.

Step 4: Refresh the materialized view (if new data is inserted)

If new data is added to the orders table, the materialized view will not be updated until you manually refresh it.

To refresh:

EXEC DBMS_MVIEW.REFRESH('customer_sales_mv');

Now the materialized view will include the new data after the refresh.

Step 5: Add new data and refresh

INSERT INTO orders VALUES (6, 101, DATE '2024-09-05', 50);
COMMIT;

EXEC DBMS_MVIEW.REFRESH('customer_sales_mv');

Query again:

SELECT * FROM customer_sales_mv;

Updated Output:

The materialized view now reflects the updated data after the refresh.

...