Let me describe the situation I am in. I have a table of 2000 objects and each object has a range of different prices associated with it, stored in another table.
I have created a view which serves to list the objects as well as their average price. At present I have something along the lines of
CREATE VIEW objview AS
select o.*, (select sum(op.price)/count(*) from objprice op where op.objectid=o.objectid) averageprice
from obj o
I have a feeling that the above might not be very efficient.
Does anyone have any input regarding speed and efficiency on
- using a join in the above instead of a nested query
- having a fixed field in the original obj table which gets updated via a trigger when new values are inserted into objprice
Which is the best solution?