CODEWITHSUNDEEP
sqloracle-databaseperformance
Student Work
Student Work

Reputation: 31

Query optimisation / rewriting

I need to rewrite the following queries for optimization but I'm having a little trouble

SELECT w.wname, SUM(w.price) sold_total, FROM wine w GROUP BY w.wname;

SELECT * from class WHERE CID = 'RIES ' OR CID = 'CHARDN' OR CID = 'PINOT';

I think i need to create some kinda object for the first one and a bind variable for the second but I'm just not sure. Also unsure of best syntax.

Would love some help

Upvotes: 0

Views: 77

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Your first query:

SELECT w.wname, SUM(w.price) sold_total FROM wine w GROUP BY w.wname

cannot really be optimized, because it is using the SUM() function with aggregation, and ultimately every record in the table must be involved. Since there is no filtering here, there isn't much advantage to using a index, and Oracle would likely just decide to do a full table scan. For the second query:

SELECT * from class WHERE CID = 'RIES ' OR CID = 'CHARDN' OR CID = 'PINOT';

you may consider adding the following index:

CREATE INDEX idx_class ON class (CID);

This would cover the WHERE clause, which is the filtering step, letting the query execute faster.

Upvotes: 1

Related Questions