Hi Josef

W dniu 18.04.2014 10:49, Josef Kokeš pisze:

> Hi!
>
> I am struggling with a particular select, trying to get it to execute as
> efficiently as possible. I have tables MASTER(ID, NAME, ...) and
> DETAIL(ID, MASTER_ID, VALUE1, VALUE2, VALUE3). The SELECT I want to get
> would display all fields from MASTER and an aggregation of fields from
> DETAIL, e.g. something like:
>
> SELECT MASTER.*,
> (SELECT SUM(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
> (SELECT MAX(VALUE2) FROM DETAIL WHERE DETAIL_ID=MASTER.ID),
> (SELECT MIN(VALUE1) FROM DETAIL WHERE DETAIL_ID=MASTER.ID)
> FROM MASTER
> WHERE NAME STARTING WITH 'A'
>
> I would like to replace the three similar subselects with one
> three-field subselect, but my attempts at using CTE rather failed:
>
> WITH DETAIL_AGGREGATE (MASTER_ID, VALUE1, VALUE2, VALUE3)
> AS (SELECT MASTER_ID, SUM(VALUE1), MAX(VALUE2), MIN(VALUE3)
> FROM DETAIL
> GROUP BY MASTER_ID)
> SELECT MASTER.*, DETAIL_AGGREGATE.*
> FROM MASTER
> JOIN DETAIL_AGGREGATE ON MASTER_ID=MASTER.ID
> WHERE NAME STARTING WITH 'A'
>
> This is very slow, presumably because it calculates the aggregates over
> all rows in DETAIL, regardless of whether the rows are actually included
> in the result.
>
> I could probably write a traditional:
>
> SELECT MASTER.ID, MASTER.NAZEV, MASTER.xyz, ...,
> SUM(DETAIL.VALUE1), MAX(DETAIL.VALUE2), MIN(DETAIL.VALUE3)
> FROM MASTER
> LEFT JOIN DETAIL ON DETAIL.MASTER_ID=MASTER.ID
> WHERE MASTER.NAME STARTING WITH 'A'
> GROUP BY 1, 2, 3, ...

I would consider the this second option, but I would change the join:

SELECT ...
FROM DETAIL
LEFT JOIN MASTER

Check on your real structure and data if it helps

Marcin