избежать двойного прохода по таблице

olzhas
Дата: 17.03.2011 09:23:30
Привет всем!
Не подскажете как по другому переписать запрос чтобы избежать 2-го прохода по таблице masterTab?
with 
masterTab as (
select 1 as idMaster, 123 as value  from dual union all
select 2 as idMaster, 132 as value  from dual union all
select 3 as idMaster, 342 as value  from dual union all
select 4 as idMaster, 343 as value  from dual union all
select 5 as idMaster, 124 as value  from dual union all
select 6 as idMaster, 545 as value  from dual
),
detailTab as (
select 1 as idMaster, 2 as rec from dual union all
select 1 as idMaster, 3 as rec from dual union all
select 1 as idMaster, 4 as rec from dual union all
select 2 as idMaster, 2 as rec from dual union all
select 2 as idMaster, 3 as rec from dual union all
select 2 as idMaster, 4 as rec from dual union all
select 3 as idMaster, 2 as rec from dual union all
select 3 as idMaster, 3 as rec from dual union all
select 3 as idMaster, 4 as rec from dual union all
select 3 as idMaster, 5 as rec from dual 
),
finalTable as (
select 1 as rec, sum(value) as allSum 
from masterTab
union all
select b.rec, sum(value) as allSum
from masterTab a
inner join detailTab b on a.idMaster = b.idMaster
group by b.rec
)
select *
from finalTable
order by rec
__vvp_
Дата: 17.03.2011 09:30:43
olzhas,

Сдается мне, что должен быть left join

А так, STFF ROLLUP
olzhas
Дата: 17.03.2011 11:36:46
rollup работает не так как мне нужно. он считает итоговую сумму. а мне нужно сумму по уникальных записей по таблице masterTable и сумму в разрезе записей по таблице detailTable и эти 2 цифры не обязательно должны быть равны.
env
Дата: 17.03.2011 11:43:09
olzhas,

left join + case
olzhas
Дата: 17.03.2011 11:55:41
env
olzhas,

left join + case

left join чего? можете запрос привести?
dbms_photoshop
Дата: 17.03.2011 12:16:34
olzhas
избежать 2-го прохода по таблице masterTab
with
finalTable as (
select a.idMaster, b.rec, value
from masterTab a
left join detailTab b on a.idMaster = b.idMaster
)
select t.rec, sum(value) allSum
from finalTable t
where rec is not null
group by t.rec
union all
select 1 as rec, sum(max(value)) as allSum 
from finalTable t
group by idMaster
order by rec
AmKad
Дата: 17.03.2011 12:19:36
olzhas
избежать 2-го прохода по таблице masterTab?

+ Уверен, что оно тебе надо?
with
masterTab as (
select 1 as idMaster, 123 as value  from dual union all
select 2 as idMaster, 132 as value  from dual union all
select 3 as idMaster, 342 as value  from dual union all
select 4 as idMaster, 343 as value  from dual union all
select 5 as idMaster, 124 as value  from dual union all
select 6 as idMaster, 545 as value  from dual
),
detailTab as (
select 1 as idMaster, 2 as rec from dual union all
select 1 as idMaster, 3 as rec from dual union all
select 1 as idMaster, 4 as rec from dual union all
select 2 as idMaster, 2 as rec from dual union all
select 2 as idMaster, 3 as rec from dual union all
select 2 as idMaster, 4 as rec from dual union all
select 3 as idMaster, 2 as rec from dual union all
select 3 as idMaster, 3 as rec from dual union all
select 3 as idMaster, 4 as rec from dual union all
select 3 as idMaster, 5 as rec from dual
)
select
decode(grouping_id(b.rec), 1, 1, b.rec) rec,
decode(grouping_id(b.rec),
            1, sum(decode(nvl(rn, 1), 1, value)),
            sum(value)
      ) sm
from       masterTab a
left join (select t.*, row_number() over (partition by idMaster order by null) rn
           from detailTab t
          ) b on a.idMaster = b.idMaster
group by rollup(b.rec)
having grouping_id(b.rec) = 1 or b.rec is not null
order by 1;

       REC         SM
---------- ----------
         1       1609
         2        597
         3        597
         4        597
         5        342


План выполнения
----------------------------------------------------------
Plan hash value: 1095109097

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     6 |   150 |    36  (12)| 00:00:01 |
|   1 |  SORT ORDER BY         |      |     6 |   150 |    36  (12)| 00:00:01 |
|*  2 |   FILTER               |      |       |       |            |          |
|   3 |    SORT GROUP BY ROLLUP|      |     6 |   150 |    36  (12)| 00:00:01 |
|*  4 |     HASH JOIN OUTER    |      |     6 |   150 |    34   (6)| 00:00:01 |
|   5 |      VIEW              |      |     6 |    36 |    12   (0)| 00:00:01 |
|   6 |       UNION-ALL        |      |       |       |            |          |
|   7 |        FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |        FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   9 |        FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  10 |        FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |        FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |        FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  13 |      VIEW              |      |    10 |   190 |    21   (5)| 00:00:01 |
|  14 |       WINDOW SORT      |      |    10 |    60 |    21   (5)| 00:00:01 |
|  15 |        VIEW            |      |    10 |    60 |    20   (0)| 00:00:01 |
|  16 |         UNION-ALL      |      |       |       |            |          |
|  17 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  18 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  19 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  20 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  21 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  22 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  23 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  24 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  25 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|  26 |          FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("B"."REC",1,0,SYS_O
              P_BITVEC)))=1 OR "B"."REC" IS NOT NULL)
   4 - access("A"."IDMASTER"="B"."IDMASTER"(+))


Статистика
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        451  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          5  rows processed
dbms_photoshop
Дата: 17.03.2011 12:20:02
Только едва ли ты вышеуказанным избеганием что-то выиграешь.
Model тоже позволит за одно обращение посчитать, но опять де response time ты таким образом не уменьшишь.
olzhas
Дата: 17.03.2011 12:31:46
получается выиграть по производительности все равно не получится. Жаль.. думал может у Оракла есть специфические sql команды.
AmKad
Дата: 17.03.2011 12:39:18
olzhas,

Я бы сказал - у тебя специфичный отчет.