Working with Tables and Materialized Views in Oracle: A Practical Guide
Materialized views in Oracle are a powerful feature that allows you to store query results for improved performance, particularly in data warehousing and reporting environments. However, it’s important to understand how materialized views behave when the underlying data changes and how to keep them synchronized with the base tables. In this post, we’ll walk through creating a table, inserting data, creating a materialized view, and understanding the need for refreshing the materialized view to keep it up-to-date.
Creating a Table and Index
We start by creating a table called ABC, which will store basic user information, including first name,
last name, ID, and an update timestamp. We also define a primary key constraint on the ID column.
CREATE TABLE ABC (
FNAME VARCHAR2(20 BYTE),
LNAME VARCHAR2(20 BYTE),
ID NUMBER NOT NULL,
UPD_TSTAMP TIMESTAMP(6) DEFAULT SYSTIMESTAMP
) TABLESPACE USERS;
CREATE UNIQUE INDEX ABC_PK ON ABC (ID ASC)
LOGGING
TABLESPACE USERS;
ALTER TABLE ABC
ADD CONSTRAINT ABC_PK PRIMARY KEY (ID)
USING INDEX ABC_PK
ENABLE;
Inserting Data into the Table
Next, we insert some sample data into the table. This data includes various names and timestamps, giving us records to work with in our example.
INSERT INTO ABC (FNAME, LNAME, ID, UPD_TSTAMP)
VALUES ('ravi', 'reddy', 2, TO_TIMESTAMP('09-JAN-15 11.44.39.000000000 AM', 'DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO ABC (FNAME, LNAME, ID, UPD_TSTAMP)
VALUES ('arvind', 'reddy', 3, TO_TIMESTAMP('16-JAN-15 12.06.01.293176000 PM', 'DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO ABC (FNAME, LNAME, ID, UPD_TSTAMP)
VALUES ('raghu', 'kumar', 4, TO_TIMESTAMP('20-JAN-15 04.33.35.616985000 PM', 'DD-MON-RR HH.MI.SSXFF AM'));
INSERT INTO ABC (FNAME, LNAME, ID, UPD_TSTAMP)
VALUES ('ranii', 'boy', 5, TO_TIMESTAMP('20-JAN-15 04.47.08.157119000 PM', 'DD-MON-RR HH.MI.SSXFF AM'));
Creating a Materialized View
A materialized view (MV) is then created based on the data from the ABC table.
This materialized view stores a snapshot of the data, which can be used for reporting or other operations
without querying the base table directly.
CREATE MATERIALIZED VIEW MV AS
SELECT * FROM ABC;
Viewing the Data
To see the current state of the data in the table, you can run:
SELECT * FROM ABC;
Updating the Table Data
Now, let’s update some records in the table to observe how this affects the materialized view. Notice that after performing the updates, the data in the materialized view will not automatically reflect these changes.
UPDATE ABC SET LNAME = UPPER(LNAME) WHERE ID = 3;
UPDATE ABC SET LNAME = UPPER(LNAME) WHERE ID = 4;
After the updates, the table data will be updated, but the materialized view will still show the old data. This demonstrates the need for refreshing the materialized view to ensure consistency.
Refreshing the Materialized View
To synchronize the materialized view with the base table, you need to refresh it. This can be done manually using the following command:
EXECUTE DBMS_MVIEW.REFRESH('MV');
Cleanup
To clean up after our operations, you can drop the materialized view and reset any changes made during the demonstration:
DROP MATERIALIZED VIEW MV;
-- Drop view if it exists; this part is included for completion.
DROP VIEW V;
-- Update the table back to its original state.
UPDATE ABC SET LNAME = LOWER(LNAME);
COMMIT;
Conclusion
Materialized views are a great tool for performance optimization but require careful management to keep data consistent with the underlying tables. Refreshing materialized views manually or configuring them for automatic refresh ensures that your data remains up-to-date, allowing for accurate and efficient reporting.
By understanding how materialized views work and how to manage them effectively, you can leverage them to improve query performance while maintaining data integrity in your Oracle database environment.
Home 