Query Plan. Pleas draw 3 different querry plans(trees) for the following SQL. St
ID: 3812335 • Letter: Q
Question
Query Plan. Pleas draw 3 different querry plans(trees) for the following SQL. Statement and schema. you have
to write the necessary table names and column names as the tree node and leafs. Italicized and Bold words
means foreign key.
Custmoer(customer_id, login_id, password,first_name, last_name,tel,address, city,zipcode,state)
producct(product_id, name, description, vendor_id, cost, sell_price, quantity,employee_id)
Orders(order_id,customer _id, date)
Product_order(order_id, product_id, quantity)
select c.first_name, p.name,po.qunatity,o.date
from Customer c, Product p. Product_order po, orders o
where p. product_ id=po.product_id and po.order_id=o.order_id and
c.customer_id=o. customer_id and c.last_name='test';
3.1 Query plan 1 (draw).
3.2 Qurey plan 2 (draw).
3.3 Query plan 3 (draw)
3.4 please expalin which plan will give the best perfomance and why?
Explanation / Answer
Plan 1
1. Projection of c.first_name, p.name,po.qunatity,o.date
2, Selection on the basis of condition
where p. product_ id=po.product_id and po.order_id=o.order_id and
c.customer_id=o. customer_id and c.last_name='test';
3. Display the results
Plan B
1. Selection operation will be followed by projection
Plan C
1. Select all data using foreign key.
Among these approaches first approach is correct, as projection is column wise operations . So the sample space to be processed will be more in case 2 and three,