Processing Selection Queries - Data Base Management System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Processing Selection Queries - Data Base Management System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Query Processing and Query

Optimization

What are we going to cover

1. Processing Selection Queries
2. Processing Projection Queries and Eliminating Duplicates
3. Processing Join Queries
4. Query Plans and Query Optimization for Complex Relational Expression
5. Query Optimization and Database Analysis in Oracle
6. Summary

1. Processing Selection Queries

• A Query Plan is a particular strategy for reading the indexes and files to process a query

Query plan for query 1

s elect * from Customer where accountId = 101

2. Processing Projection Queries and

Eliminating Duplicates

• Suppose we use the following query to eliminate the duplicates select distinct lastName, firstName from Customer
• The DBMS has two ways of doing this which are based on sorting and hashing
• The sorting method sorts the projected records and writes the nonduplicates
• The hashing method partitions the file into buckets then sorts within buckets to eliminate duplicates

3. Processing Join Queries

• Suppose we have following three tables:

Customer, Rental and Movie

• B: number of Blocks, R: number of rows
• The following example shows the same cost of

data blocks reads for both plans

Improvement

• block nested loops join
• Use the method readBlock() in the outer loop

to read the outer table into an array

• The inner loop reads the inner file(array)
• Reduce the cost to Bc + Bc x Br

Improvement

• indexed nested loops join
• Reduce the reads by using index
• If one of the attributes is indexed, we should

take advantage of it

• Use indexed read  readByAccountId()
• Two plans have different costs because of the

one-to-many relationship

Improvement

• indexed nested loops join
• Each rental has a customer but not every customer has a rental
• Plan-1 (Rental inner)reads every customer and every rental once, but plan-2 (Rental outer)reads some customers many times and some customer no times
• Which plan to use is depending on the size of the file(table).
• Chose plan-1 if the customer file is smaller

Improvement

• sort-merge join
• Sort both tables and merge the result
• The cost will depend on the cost of

sorting(typically on the order of NlogN for N records)

4. Query Plans and Query Optimization for

Complex Relational Expression

• Query Optimization Process:
  1. Enumerate the query plans
  2. Estimate the costs of the plans
  3. Choose the best plan for the query

4. Query Plans and Query Optimization

for Complex Relational Expression

4. Query Plans and Query Optimization

for Complex Relational Expression

4. Query Plans and Query Optimization

for Complex Relational Expression

• Estimating costs (Query Optimizer)
  • number of records
  • size of records in each table
  • location and types of indexes
  • ordering of files
  • memory available for disk cache
  • etc…