Database-19-QueryProcessing.pptx

# Cost analysis of cartesian product if r has n records

• 62

This preview shows pages 25–30. Sign up to view the full content.

Cost analysis of CARTESIAN PRODUCT If R has n records and j attributes and S has m records and k attributes, the result relation will have n*m records and j+k attributes. CARTESIAN PRODUCT operation is very expensive and should be avoided if possible. Query Processing and Optimization 25

This preview has intentionally blurred sections. Sign up to view the full version.

Algorithms for PROJECT and SET Operations Algorithm for SET operations (contd.) UNION (See Figure 19.3c) Sort the two relations on the same attributes. Scan and merge both sorted files concurrently, whenever the same tuple exists in both relations, only one is kept in the merged results. INTERSECTION (See Figure 19.3d) Sort the two relations on the same attributes. Scan and merge both sorted files concurrently, keep in the merged results only those tuples that appear in both relations. SET DIFFERENCE R-S (See Figure 19.3e) Keep in the merged results only those tuples that appear in relation R but not in relation S. Query Processing and Optimization 26
Implementing Aggregate Operations and Outer Joins Implementing Aggregate Operations: Aggregate operators : MIN, MAX, SUM, COUNT and AVG Options to implement aggregate operators: Table Scan Index Example SELECT MAX (SALARY) FROM EMPLOYEE; If an (ascending) index on SALARY exists for the employee relation, then the optimizer could decide on traversing the index for the largest value, which would entail following the right most pointer in each index node from the root to a leaf. Query Processing and Optimization 27

This preview has intentionally blurred sections. Sign up to view the full version.

Implementing Aggregate Operations and Outer Joins Implementing Aggregate Operations (contd.): SUM, COUNT and AVG For a dense index (each record has one index entry): Apply the associated computation to the values in the index. For a non-dense index : Actual number of records associated with each index entry must be accounted for With GROUP BY : the aggregate operator must be applied separately to each group of tuples. Use sorting or hashing on the group attributes to partition the file into the appropriate groups; Computes the aggregate function for the tuples in each group. What if we have Clustering index on the grouping attributes? Query Processing and Optimization 28
Implementing Aggregate Operations and Outer Joins Implementing Outer Join: Outer Join Operators : LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN . The full outer join produces a result which is equivalent to the union of the results of the left and right outer joins. Example: SELECT FNAME, DNAME FROM (EMPLOYEE LEFT OUTER JOIN DEPARTMENT ON DNO = DNUMBER); Note: The result of this query is a table of employee names and their associated departments. It is similar to a regular join result, with the exception that if an employee does not have an associated department, the employee's name will still appear in the resulting table, although the department name would be indicated as null.

This preview has intentionally blurred sections. Sign up to view the full version.

This is the end of the preview. Sign up to access the rest of the document.
• Fall '09
• SUNANHAN
• Query optimizer, JOIN Operations

{[ snackBarMessage ]}

### What students are saying

• As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

Kiran Temple University Fox School of Business ‘17, Course Hero Intern

• I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

Dana University of Pennsylvania ‘17, Course Hero Intern

• The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

Jill Tulane University ‘16, Course Hero Intern