CODEWITHSUNDEEP
sqloracle-databasedenormalizationdenormalized
Sunny J
Sunny J

Reputation: 47

Cost (%CPU) around the same after denormalization?

Using SQL Oracle. I create a query to find the total counts of orders for food.

EXPLAIN PLAN FOR
SELECT FOOD.F_NAME, COUNT(ORDERS.O_ORDERID)
FROM ORDERS
INNER JOIN CUSTOMER ON O_CUSTID = C_CUSTID
INNER JOIN FOOD ON C_FOODKEY = F_FOODKEY
GROUP BY FOOD.F_NAME;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

This returns cost (%CPU) of 3250 at row ID 0 in the plan table output.

I learnt that denormalization will speed up the query and reduce the cost. In this case, I copied the food name from my table FOOD to ORDERS to avoid the INNER JOIN. I should get a better cost (%CPU) usage.

I used this query next

EXPLAIN PLAN FOR
SELECT ORDERS.F_NAME, COUNT(ORDERS.O_ORDERID)
FROM ORDERS
GROUP BY ORDERS.F_NAME;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

The cost (%CPU) did not change much at all - the value is 3120 at row ID 0 in the plan table output.

Isn't denormalization and removal of the INNER JOIN suppose to improve my cost? The improvement is so insignificant in my case. What's the issue here?

Upvotes: 0

Views: 94

Answers (2)

Antonín Lejsek
Antonín Lejsek

Reputation: 6103

C_FOODKEY should be most likely O_FOODKEY to make sense

You do not need denormalization. What you are doing is - you explode all the rows by joins and then you group them together. Do not do this in the first place and try something like this

SELECT FOOD.F_NAME, 
      (SELECT COUNT(*) 
       FROM ORDERS 
       WHERE O_FOODKEY = F_FOODKEY) AS fCount
FROM FOOD

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is too long for a comment. You would have to study the execution plan. However, joins on primary keys are often not particularly expensive.

What is expensive is the GROUP BY, because this requires moving data around. You could try adding an index on F_NAME in the second query.

Your data model is also unusual. It is unclear why a column called FOOD would be stored at the CUSTOMER level.

Upvotes: 0

Related Questions