1.     COMMIT is used in Oracle to permanently save all changes made by the current transaction to the database, such as INSERT, UPDATE, or DELETE operations. Once a COMMIT is issued, the changes become visible to other users and sessions.

2.     After a COMMIT, the transaction is considered complete and cannot be rolled back. Oracle clears the undo information for that transaction, meaning you cannot use ROLLBACK to undo the changes anymore.

3.     COMMIT releases all locks held by the transaction. Any rows or tables locked during the transaction become available for other users to modify, improving concurrency in the database.

4.     Oracle automatically performs a COMMIT in certain situations, such as when a DDL statement (CREATE, ALTER, DROP, TRUNCATE) is executed, or when the user exits the database normally.

5.     COMMIT does not end the database session; it only ends the current transaction. After a COMMIT, a new transaction automatically begins when the next DML statement is executed.

6.     Using COMMIT frequently can reduce undo space usage and prevent long-running transactions, but committing too often may negatively affect performance by increasing I/O operations.

7.     COMMIT ensures data consistency and durability, which aligns with the “D” (Durability) property of ACID transactions in Oracle databases.

8.     COMMIT does not affect SELECT statements, as they do not change data. Only DML operations are controlled by COMMIT and ROLLBACK.

9.     If a system failure occurs after a COMMIT, Oracle’s recovery mechanism ensures that the committed changes are not lost and are restored when the database is restarted.

10.  In distributed transactions, COMMIT works with Oracle’s two-phase commit mechanism to ensure that changes across multiple databases are either all committed or all rolled back, maintaining data integrity.

 

IMMEDIATE Refresh

•   IMMEDIATE Refresh is also called FAST Refresh.

•   It updates a materialized view (MV) only with the changes made to the base tables.

•   Materialized view logs are used to track what has changed.

•   Only the new changes since the last refresh are applied.

•   The refresh can happen automatically at commit (ON COMMIT) or manually when needed.

 

-- 1. Base Table Creation

CREATE TABLE sales_mvi (

sale_id    NUMBER PRIMARY KEY,

product_id NUMBER,

sale_date  DATE,

amount     NUMBER

);

 

-- 2. Materialized View Log Creation

-- Required for FAST refresh

CREATE MATERIALIZED VIEW LOG ON sales_mvi

WITH ROWID, SEQUENCE (product_id, sale_date, amount)

INCLUDING NEW VALUES;

 

-- 3. Materialized View (FAST REFRESH ON COMMIT)

CREATE MATERIALIZED VIEW sales_mv

REFRESH FAST ON COMMIT

AS

SELECT product_id, SUM(amount) AS total_amount

FROM sales_mvi

GROUP BY product_id;

 

-- 4. Materialized View (FAST REFRESH on Schedule)

-- Example: Hourly Refresh

CREATE MATERIALIZED VIEW sales_mv

REFRESH FAST

START WITH SYSDATE

NEXT SYSDATE + 1/24

AS

SELECT product_id, SUM(amount) AS total_amount

FROM sales_mvi

GROUP BY product_id;

 

-- 5. Sample Data Insert

INSERT INTO sales_mvi VALUES (1, 101, SYSDATE, 500);

COMMIT;

 

-- 6. Query Materialized View

SELECT * FROM sales_mv;

 

Expected Output:

PRODUCT_ID | TOTAL_AMOUNT

101        | 500