Query Plan. Pleas draw 3 different querry plans(trees) for the following SQL. St

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,

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.