Query decomposition in data base

Query decomposition in data base

• 1.
  SALMAN MEMON 2K12/IT/109 1
• 2.
   Process ofminimizing the resource usage.  Aims of Query Decomposition  To transform a high-level query into a Relational Algebra query &  To check the query is syntactically and semantically correct  It is efficient way to retrieve data from database. 2
• 3.
  select * fromstaff s, branch b where s.branchNo=b.branchNo And (s.position=‘Manager’ And b.city=‘London’);  Three equivalent relational algebra queries 1. σ(position=‘Manager’)^(staff.branchNo=branc h.branchNo)(staff x branch) • Suppose staff have 1000 records and branch have 50 records 3
• 4.
  • This querycalculates the Cartesian product of staff and branch, (1000 + 50) disk accesses to read and creates relation with (1000*50) tuples. We then have to read each of these tuples again to test them against the selection predicate so it gives total cost of : (1000+50)+2*(1000*50)=101050 disk access 2. σ(position=‘Manager’)^(city=‘London’)(staff∞staff.branchNo=bran ch.branchNo branch) Gives total cost of: • 2*1000+(1000+50)=3050 disk accesses 3. (σposition=‘manager’(staff))∞staff.branchNo=branch.branchNo(σci ty=‘London’(branch)) Gives total cost of: • 1000+2*50+5+(50+5)=1160 disk accesses 4
• 5.
  1. Analysis 2. Normalization 3.Semantic Analysis 4. Simplification Or Redundancy eliminator 5. Query Restructuring 5
• 6.
  In this stagequery is syntactically analyzed  Verifies that the relations and attributes specified in the query are defined in the system catalog  Verifies that any operations applied to database objects are appropriate for the object type 6
• 7.
  • SELECT staffNumber FROMstaff where position >10; This query would be rejected on two grounds 1. in SELECT list the attribute staffNumber is not defended for staff relation(should be staffNo) 2. in WHERE clause comparison ’>10’ is incompatible with the data type position, which is a variable character string After completion this stage ,high level query has been transformed internal representation that is some kind of query tree. 7
• 8.
  ∞s.branchNo=b.branchNo Root intermediate operations σs.position=‘manager’σb.city=‘london’ leaves staff branch • A leaf node is created for each base relation in query • Each intermediate relation produced by a relational algebra operation • The Root of the tree represents the result of the query • The sequence of operation is directed from the leaves to the root 8
• 9.
   It normalizesquery for easy manipulation  Arbitrarily complex predicate (in SQL, the WHERE condition) can be converted into one of two forms by applying few transformation rules: 1. Conjunctive Normal form:- A sequence of conjuncts that are connected with the ^ (AND) operator. Each conjunct contains one or more terms connected by the v (OR) operator For Example :- (position=‘manager’ v salary > 20000) ^ branchNo=‘B003’ A conjunctive selection contains only those tuples that satisfy all conjuncts. 9
• 10.
  2. Disjunctive Normalform:- A sequence of disjuncts that are connected with the v (OR) operator. Each disjunct contains one or more terms connected by the ^ (AND) operator For Example : we can rewrite the above conjunctive normal form into disjunctive (position=‘manager’^ branchNo=‘B003’) v (salary >20000 ^ branchNo=‘B003’) A disjunctive selection contains those tuples formed by the union of all tuples that satisfy the disjuncts 10
• 11.
   It rejectnormalized queries that are incorrectly formulated or contradictory.  A query is incorrectly formulated if components do not contribute to the generation of the result, a query is contradictory if its predicate cannot be satisfied by any tuple.  For Example: the predicate (position=‘manager’ ^ position=‘assistant’)on staff relation is contradictory b/c a person can not the both manager and assistant simultaneously  However, the predicate((position=‘manager’ ^position=‘assistant’) v salary >2000) could be simplified 11
• 12.
   Simplifying thequalification of user query to eliminate redundancy  Transform the query to a semantically equivalent but more easily and efficiently computed form  Queries on relation that satisfy certain integrity and security constraints (access restriction) 12
• 13.
   Before eliminationof Redundancy SELECT Position FROM staff s, branch b WHERE (NOT(b.Position = ‘manager’) AND (b.Position = ‘manager’ OR b.Position=‘assistant’) AND NOT(b.Position = ‘assistant’)) OR (b.BID = s.SID AND s.Name = ‘Ali’)  After Elimination of Redundancy SELECT b.Position FROM staff s, branch b WHERE b.BID = s.SID AND s.Name = ‘ali 13
• 14.
   The finalstage of query decomposition.  The query is restructured to provide a more efficient implementation. 14
• 15.
  Advantages:-  all informationrequired to select an optimum strategy is up to date Disadvantage:  It takes time to decompose the query has to be parsed, validated, and optimized before it can be executed 15
• 16.
   Query isoptimized ,data can be retrieve easily and more efficiently 16
• 17.
  17