Query Optimization, Recovery of transaction and Multiple Granularity | DBMS

In this article, we will learn what is query optimization, recovery of the transaction and multiple granularity protocols?
Submitted by Bharti Parmar, on November 10, 2018

1) Query Optimization

There are generally a variety of methods for computing the answer. It is the responsibility of the system to transform the query as entered by the user into the equivalent query that can be computed more efficiently. The process of finding a good strategy for processing a query called query optimization.

Query optimization is a function of RDBMS. The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query by considering the possible query plan. Generally, the query optimizer cannot be accessed by the user, once queries are submitted to the database server and parsed by the parser. They are then passed in query optimizer where optimization occurs. However, the database engine allows guiding the query optimizer with hints.

2) Recovery of transaction

In the database system must take actions in advance to ensure that the atomicity and durability properties of a transaction. An integral part of a database system that restores the database to the consistent state that existed before the failure. This recovery scheme must also provide high availability that it minimizes the time for which database not usable after failure.

3) Multiple granularities

Multiple granularities | DBMS

Locks: Shared lock, exclusive lock, intentionally shared lock (IS), intentional exclusive lock (IX), shared & Intentional executive (SIX).

Shared lock: Shared locks support read integrity. There are some circumstances for this lock: request for a shared lock must wait if any parallel task owns an exclusive lock on the resource.

Request for an exclusive lock must wait if other tasks paralleled individually shared locks on this resource. A new request for a shared lock must wait if another task is waiting for an exclusive lock on a resource that already has a shared lock.

Exclusive lock: Exclusive locks support write integrity. They can be owned by only one transaction at a time. Any transaction that requires an exclusive lock must wait if another task currently owns an exclusive lock or a shared lock against the requested resource.

IS: Explicitly locking of lower level achieve but only with a shared lock.

IX: Explicitly locking at a lower level with exclusive (R1, R2 …. Rn) or shared lock (T1, T2 … Ta).

SIX: Subtree rooted by that node is locked explicitly in shared mode & explicit locking is done at a lower level with the explicit mode.

Comparative matrix:

Comparative matrix | DBMS


In this article, we have learned what is query optimization, recovery of transaction and multiple granularity protocol? Have a nice day! Happy Learning!

Related Tutorials


Comments and Discussions!

Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.