Category Archives: Data Base

Database Syatems

Oracle 11g Joins and Subqueries

Convert a subquery to a join in Oracle 11g

SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE LOCATION_ID = 1700)

Answer:

SELECT first_name, department_id, location_id
FROM employees JOIN departments USING (department_id)
WHERE location_id = 1700;

Winner: Ambuj

lateral clauses in SQL

What alternatives offered for lateral clauses in SQL server, MySQL and Oracle:

select name, salary, avg_salary
from instructor I1,
lateral (select avg(salary) as avg_salary
from instructor I2
where I2.dept_name= I1.dept_name);

 

SQL;2003 allows a subquery in the from clause that is prefixed by lateral key word to access the attributes of preceding tables or sub queries in the from clause. Example SQL is given in the question.  Without the lateral clause, the sub query cannot access the correlation variable I1 from the outer query. Currently only few SQL implementations such as IBM DB2 support the lateral clause.

The Apply operator invokes a table-valued function for every row in the outer record set. The table valued function is evaluated for each row of the outer record set. Rows returned from table valued function are joined to the row of the outer record set using UNION ALL operation. There are two types – Cross Apply or Outer Apply.
Syntax: 

SELECT <column list> FROM
left_table_source { CROSS | OUTER } APPLY right_table_source

CROSS APPLY returns only rows from the outer table (left_table_source) that produce a result set from the table-valued function (right_table_source). It is like the Left Outer
join. OUTER APPLY returns both rows that produce a result set, and rows that do not match, with NULL values in the columns produced by the table-valued function. It is like
the Full outer join operation.

MySQL – We can achieve the lateral functionality (eg: using correlated sub queries in the FROM clause) by using ON clause of a JOIN operation. But it would then fetch the
entire inner query row set to join against the outer row which may not very efficient. Correlated subquery is a subquery that uses the information from the outer query. In
where clause, the correlated sub query can be used which references columns of outer query and the inner query executes for every row of outer query.

Oracle – The LATERAL key word is supported and is used to designate sub query as a lateral inline view. In the lateral inline view, we can denote tables that appear on left of
lateral inline view in the FROM clause of a query. This kind of left correlation can be specified anywhere within the sub query (like SELECT, FROM, and WHERE clauses)
and at any nesting level.

Example:

SELECT * FROM employees e, LATERAL (SELECT * FROM departments d
WHERE e.department_id = d.department_id);

Also equivalent of the SQL server’s APPLY clause in Oracle is a lateral JOIN. A lateral join in Oracle is when you join a table A with a function F that outputs rows and this
function has columns of A as parameters.

 

In RDBMS how does Statistical Data Get Populated?

RDBMS stores statistical information for every relation in the database using the DDL used to create the relations (number of rows, size of rows, block size and factores (for optimal storage), as well as how distinct values for each for each attribute).  Stats about indices, etc.

Each time a DDL command is issued (such as alter table structure) stats gets updated, also with every DML command the stats gets updated (update command).

This is generally from the RDBMS and in real life applications stats are also maintained about data usage and this information is used for Query Optimization and to calculate the cost of a query. 

DBMS: Strict 2PL locking protocol

Strict 2PL locking protocol
Cascading rollbacks can be avoided by a modification of two-phase locking called the strict two-phase locking protocol. This protocol requires not only that locking be two phase, but also that all exclusive-mode locks taken by a transaction be held until that transaction commits. This requirement ensures that any data written by an uncommitted transaction are locked in exclusive mode until the transaction commits, preventing any other transaction from reading the data.
Strict Two-Phase is the most widely used locking protocol in concurrency control. This protocol has two rules:
(1) If a transaction T wants to read (respectively, modify) an object, it first requests a shared (respectively exclusive) lock on the object.
(2) All locks held by a transaction are released when the transaction is completed.
If strict two-phase locking is used for concurrency control, locks held by a transaction T may be released only after the transaction has been rolled back as described. Once transaction T (that is being rolled back) has updated a data item, no other transaction could have updated the same data item, because of the concurrency-control requirements. Therefore, restoring the old value of the data item will not erase the effects of any other transaction