sql ques ans(part Y)

121. Differentiate Nested Loop, Hash Join and Merge Join.

Nested loop (loop over loop):
An outer loop within an inner loop is formed consisting of fewer entries and then for individual entry, inner loop is individually processed.
E.g.
Select col1.*, col2.* from coll, col2 where coll.col1=col2.col2;
It’s processing takes place in this way:
For i in (select * from col1) loop
For j in (select * from col2 where col2=i.col1) loop
Results are displayed;
End of the loop;
End of the loop;
The Steps of nested loop are:

Identify outer (driving) tableAssign inner (driven) table to outer table.For every row of outer table, access the rows of inner table.
Nested Loops is executed from the inner to the outer as:
outer_loopinner_loopHash join
While joining large tables, the use of Hash Join is preferred.
Algorithm of Hash Join is divided into:
Build: It is a hash table having in-memory which is present on the smaller table.Probe: this hash value of the hash table is applicable for each second row element.Sort merge join
Two independent sources of data are joined in sort merge join. They performance is better as compared to nested loop when the data volume is big enough but it is not good as hash joins generally.The full operation can be divided into parts of two:
Sort join operation :
Get first row R1 from input1Get first row R2 from input2.
Merge join operation:
‘while’ is not present at either loop’s end.
if R1 joins with R2
next row is got R2 from the input 2
return (R1, R2)
else if R1 < style=””> next row is got from R1 from input 1
else
next row is got from R2 from input 2
end of the loop

122. What is Database partitioning?
Division of logical database into independent complete units for improving its management, availability and performance is called Database partitioning.

123. Explain the importance of partitioning.
Splitting of one table which is large into smaller database entities logically is called database partitioning. Its benefits are:

To improve query performance in situations dramatically when mostly rows which are heavily accessed are in one partition.Accessing large parts of a single partitionSlower and cheaper storage media can be used for data which is seldom used.

124. Define Database system.
DBMS along with database is called Database system.

125.What do you mean by Query Evaluation Engine?
Query Evaluation Engine executes the low-level instructions that are generated by the compiler.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s