Showing posts with label Oracle L1. Show all posts
Showing posts with label Oracle L1. Show all posts

Jan 22, 2024

Materialized Views in Oracle

Materialized Views in Oracle 

 

A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.




Basic Syntax- 


The full syntax description for theCREATE MATERIALIZED VIEWcommand is available in the documentation. Here we will only concern ourselves with the basics. 

--Normal 


CREATE MATERIALIZED VIEW view-name 

BUILD [IMMEDIATE | DEFERRED] 

REFRESH [FAST | COMPLETE | FORCE ] 

ON [COMMIT | DEMAND] 

[[ENABLE | DISABLE] QUERY REWRITE] 

AS 

SELECT ...; 


-- Pre-Built 


CREATE MATERIALIZED VIEW view-name  ON PREBUILT TABLE 

REFRESH [FAST | COMPLETE | FORCE ] 

ON [COMMIT | DEMAND] 

[[ENABLE | DISABLE] QUERY REWRITE] 

AS 

SELECT ...; 

 

TheBUILDclause options are shown below. 


IMMEDIATE-

The materialized view is populated immediately. 



DEFERRED-

The materialized view is populated on the first requested refresh. 

The following refresh types are available. 


FAST-

A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.


 

COMPLETE-

The table segment supporting the materialized view is truncated and repopulated completely using the associated query. 



FORCE-

A fast refresh is attempted. If one is not possible a complete refresh is performed. 

A refresh can be triggered in one of two ways. 



ON COMMIT –


The refresh is triggered by a committed data change in one of the dependent tables.

 


ON DEMAND-

The refresh is initiated by a manual request or a scheduled task. 

TheQUERY REWRITEclause tells the optimizer if the materialized view should be considered. For query rewrite operations. An example of the query rewrite functionality is shown below. 


TheON PREBUILT TABLEclause tells the database to use an existing. table segment, which must have the same name as the materialized view and support the same column structure as the query. 


 

Check Privileges -

Check the user who will own the materialized views has the correct privileges. At minimum they will require theCREATE MATERIALIZED VIEWprivilege. If they are creating materialized views using database links, you may want to grant themCREATE DATABASE LINKprivilege also. 


GRANT CREATE MATERIALIZED VIEW TO username;  

GRANT CREATE DATABASE LINK TO username;  

 

 

Create Materialized View- 


Connect to the materialized view owner and create the database link and the materialized view itself. 

CREATE MATERIALIZED VIEW A2.employee_salary_summary 

BUILD IMMEDIATE REFRESH FAST ON

COMMIT 

AS 

SELECT employee_id, SUM(salary) as total_salary 

FROM A2.employees 

GROUP BY employee_id; 





Create Materialized View Logs-


Since a complete refresh involves truncating the materialized view segment and re-populating it is using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table. To reduce the replication costs, materialized view logs can be created to capture all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view. 

To take advantage of the of the fast refresh, connect to the master instance and create the materialized view log 


CREATE MATERIALIZED VIEW LOG ON employees;





Refresh Materialized Views-


If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible. 

For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group. 

The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it. 

Information about refresh groups and the materialize views in a refresh group can be queried from theDBA_RGROUPandDBA_RCHILDviews respectively. 

A materialized view can be manually refreshed using theDBMS_MVIEWpackage. 

Rather than using a refresh group, you can scheduleDBMS_MVIEW.REFRESHcalled using theOracle Scheduler


Cleaning Up-

To clean up we must remove all objects. 

DROP MATERIALIZED VIEW your_materialized_view_name; 

DROP DATABASE LINK your_database_link_name; 


Considerations-


Before using materialized views and materialized view logs, consider the following: 

Populating a materialized view adds load to both servers involved. The source server is queried to capture the data, which is inserted into the destination server. Be sure the additional load does not adversely affect your primary system. 


Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DML on the base table. Check the additional work does not adversely affect performance on the primary system. 

If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits. 

Depending on the Oracle version and the complexity of the associated query, fast refreshes may not be possible. 

When using materialized views to improve performance of transformations and aggregations

theQUERY_REWRITE_INTEGRITYandQUERY_REWRITE_ENABLEDparameters must be set, or the server will not be able to automatically take advantages of query rewrites. These parameters may be set in the pfile or spfile file if they are needed permanently. Later releases have them enabled by default.


--BY Tarif Ahmed

Jan 18, 2024

Oracle Database Locking Mechanism

Oracle Database Locking Mechanism


In the field of database management, maintaining data integrity and controlling concurrent access is paramount. Oracle Database, a robust relational database management system, employs a sophisticated locking mechanism to achieve these objectives. In this comprehensive guide, we delve into the intricacies of the Oracle Database Locking Mechanism, exploring key terms and concepts integral to its functionality.




Oracle Database:

Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation. It is widely used for managing and storing data in a structured manner.

 

Locking Mechanism:

In a database management system, a locking mechanism is a mechanism that prevents multiple transactions from accessing the same data simultaneously. It ensures data integrity and consistency by controlling access to shared resources.

 

Transaction:

A transaction in a database represents a sequence of one or more SQL statements executed as a single unit of work. It is either completed in its entirety or not at all to maintain the integrity of the data.

 

Database Lock:

Definition: A database lock is a mechanism used to control access to a database resource (e.g., a row, table, or page) to ensure that only one transaction can modify it at a time. Locks can be shared or exclusive, depending on the type of access required.

 

Concurrency Control:

Concurrency control is a mechanism that allows multiple transactions to access the database simultaneously while maintaining data consistency. Locking is one of the key methods used for concurrency control.

 

Lock Types:

Shared Lock:

A shared lock allows multiple transactions to read a resource simultaneously, but it prevents any of them from modifying it until the lock is released.

 

Exclusive Lock:

An exclusive lock prevents any other transaction from accessing a resource, either for reading or writing, until the lock is released.

 

Deadlock:

A deadlock occurs when two or more transactions are unable to proceed because each is waiting for the other to release a lock. This can lead to a standstill in transaction processing.

 

Oracle Lock Modes:

 

Row-Level Locking:

Oracle supports row-level locking, allowing locks to be applied at the granularity of individual rows.

 

Table-Level Locking:

Locks can also be applied at the table level, preventing any transactions from accessing the entire table.

 

Transaction Isolation Levels:

Oracle supports different transaction isolation levels (e.g., Read Committed, Serializable) that determine the visibility of uncommitted changes to other transactions.

 

Lock Escalation:

Lock escalation is a process where a database system automatically converts many fine-grained locks into fewer coarser-grained locks to reduce overhead.

 

In conclusion, understanding the Oracle Database Locking Mechanism is crucial for managing concurrent transactions effectively, ensuring data integrity, and preventing conflicts. By grasping the various lock types, isolation levels, and mechanisms, database administrators can optimize system performance and maintain a high level of data consistency.