Научите уму разуму : Oracle

Mr_Vito · █ 31.07.2009 18:45

состряпал запросик, а он считается аш 30 мин, подскажите что с ним можно сделать? что бы ускорить

select suppl,
store,
article,
kardname,
sum(q_ost_start),
sum(s_ost_start),
sum(q_in),
sum(s_in),
sum(q_prod),
sum(s_prod),
sum(q_vozv_p),
sum(s_vozv_p),
sum(q_zakaz),
sum(s_zakaz),
sum(q_ost_end),
sum(s_ost_end)
from (select dd1.name as store,
dd2.name as suppl,
d1.article,
m.name as kardname,
0 as q_ost_start,
0 as s_ost_start,
d1.quantity as q_in,
d1.itemprice * d1.quantity as s_in,
0 as q_prod,
0 as s_prod,
0 as q_vozv_p,
0 as s_vozv_p,
0 as q_zakaz,
0 as s_zakaz,
0 as q_ost_end,
0 as s_ost_end
from smspec d1,
smdocuments d2,
smcard m,
sacardclass t,
smstorelocations dd1,
smclientinfo dd2
where (d2.id = d1.docid)
and (d2.opcode in (0, 9, 11, 16)) -- приход (накладные)
and (d2.docstate = 3)
and (d2.createdat >= TO_DATE('01.07.2009', 'DD.MM.YYYY'))
and (d2.createdat <= TO_DATE('30.07.2009', 'DD.MM.YYYY'))
and d2.clientindex IS NOT NULL
and d2.locationto > -2
AND (t.TREE LIKE '2.%')
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
and (d2.locationto = dd1.id)
and (dd2.id = d2.clientindex)
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
union all
select dd1.name as store,
dd2.name as suppl,
d1.article,
m.name as kardname,
0 as q_ost_start,
0 as s_ost_start,
0 as q_in,
0 as s_in,
0 as q_prod,
0 as s_prod,
d1.quantity as q_vozv_p,
d1.itemprice * d1.quantity as s_vozv_p,
0 as q_zakaz,
0 as s_zakaz,
0 as q_ost_end,
0 as s_ost_end
from smspec d1,
smdocuments d2,
smcard m,
sacardclass t,
smstorelocations dd1,
smclientinfo dd2
where (d2.id = d1.docid)
and (d2.opcode in (2, 7, 8, 10, 17)) -- расход (накладные)
and (d2.docstate = 3)
and (d2.createdat >= TO_DATE('01.07.2009', 'DD.MM.YYYY'))
and (d2.createdat <= TO_DATE('30.07.2009', 'DD.MM.YYYY'))
and d2.clientindex IS NOT NULL
and d2.locationto > -2
AND (t.TREE LIKE '2.%')
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
and (d2.locationfrom = dd1.id)
and (dd2.id = d2.clientindex)
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
union all
select dd1.name as store,
'-1' as suppl,
d1.article,
m.name as kardname,
0 as q_ost_start,
0 as s_ost_start,
0 as q_in,
0 as s_in,
d1.quantity as q_prod,
d1.itemprice * d1.quantity as s_prod,
0 as q_vozv_p,
0 as s_vozv_p,
0 as q_zakaz,
0 as s_zakaz,
0 as q_ost_end,
0 as s_ost_end
from smspec d1,
smdocuments d2,
smcard m,
sacardclass t,
smstorelocations dd1
where (d2.id = d1.docid)
and (d2.opcode = 1) -- продажа (касса)
and (d2.docstate = 3)
and (d2.createdat >= TO_DATE('01.07.2009', 'DD.MM.YYYY'))
and (d2.createdat <= TO_DATE('30.07.2009', 'DD.MM.YYYY'))
and d2.locationfrom > -2
AND (t.TREE LIKE '2.%')
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
and (d2.locationfrom = dd1.id)
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
union all
select dd1.name as store,
'-1' as suppl,
d1.article,
m.name as kardname,
0 as q_ost_start,
0 as s_ost_start,
0 as q_in,
0 as s_in,
-d1.quantity as q_prod,
-d1.itemprice * d1.quantity as s_prod,
0 as q_vozv_p,
0 as s_vozv_p,
0 as q_zakaz,
0 as s_zakaz,
0 as q_ost_end,
0 as s_ost_end
from smspec d1,
smdocuments d2,
smcard m,
sacardclass t,
smstorelocations dd1
where (d2.id = d1.docid)
and (d2.opcode = 3) -- возврат (касса)
and (d2.docstate = 3)
and (d2.createdat >= TO_DATE('01.07.2009', 'DD.MM.YYYY'))
and (d2.createdat <= TO_DATE('30.07.2009', 'DD.MM.YYYY'))
and d2.locationfrom > -2
AND (t.TREE LIKE '2.%')
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
and (d2.locationfrom = dd1.id)
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
union all
select dd1.name as store,
dd2.name as suppl,
d1.article,
m.name as kardname,
0 as q_ost_start,
0 as s_ost_start,
0 as q_in,
0 as s_in,
0 as q_prod,
0 as s_prod,
0 as q_vozv_p,
0 as s_vozv_p,
d1.quantity as q_zakaz,
d1.itemprice * d1.quantity as s_zakaz,
0 as q_ost_end,
0 as s_ost_end
from smspec d1,
smdocuments d2,
smcard m,
sacardclass t,
smstorelocations dd1,
smclientinfo dd2
where (d2.id = d1.docid)
and (d2.doctype = 'OR') -- заказы (не исполненные)
and (d2.docstate = 2)
and (d2.createdat >= TO_DATE('01.07.2009', 'DD.MM.YYYY'))
and (d2.createdat <= TO_DATE('30.07.2009', 'DD.MM.YYYY'))
and d2.clientindex IS NOT NULL
and d2.location > -2
AND (t.TREE LIKE '2.%')
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass)
and (d2.location = dd1.id)
and (dd2.id = d2.clientindex)
AND (M.ARTICLE = d1.ARTICLE)
AND (t.id = m.idclass))
group by suppl, store, article, kardname

OlegON · █ 31.07.2009 22:58

Ыхх, неплохо бы:
1) План этого зверства
2) Цель его выполнения
3) Форматировать бы, наверняка не в блокноте его писал
4) as так не использовать
5) Количество возвращаемых строк

Busotir · █ 01.08.2009 10:20

Наверняка среди всего этого попадаются столбцы с низкой кардинальностью (мало различных значений) - построить БИТОВЫЕ индексы по таким столбцам.

vdm · █ 01.08.2009 11:07

Ну прям сразу индексы клепать.
Для начала - там ошибка в связи smspec и smdocuments.
Нет проверки типа документа (d2.doctype=d1.doctype).
Возможно стоит добавить и сравнение самих типов ('WI', 'WO', 'CS', 'CR').

MirProd · █ 02.08.2009 16:32

Многое не ясно. Откуда должны взяться остатки на начало и конец (q_ost...)? Почему в накладных учитываются именно эти операции? Слишком много запросов на выборку и объединение к одним и тем же таблицам. Но прежде, чем что-то менять, напиши по-подробней что и зачем ты хочешь получить в результате.

Mr_Vito · █ 03.08.2009 09:40

Цитата:

OlegON ➤ Ыхх, неплохо бы:
1) План этого зверства
3) Форматировать бы, наверняка не в блокноте его писал

как то так получилось :-[

Код:

  SQL Statement from editor:
   
   
  select suppl, store, article, kardname, 
         sum(q_ost_start), sum(s_ost_start), 
         sum(q_in), sum(s_in), 
         sum(q_prod), sum(s_prod), 
         sum(q_vozv_p), sum(s_vozv_p), 
         sum(q_zakaz),  sum(s_zakaz), 
         sum(q_ost_end), sum(s_ost_end) 
    from (
      select dd1.name as store, dd2.name as suppl, d1.article, m.name as kardname, 
              0 as q_ost_start, 0 as s_ost_start, 
              d1.quantity as q_in, d1.itemprice*d1.quantity as s_in, 
              0 as q_prod, 0 as s_prod, 
              0 as q_vozv_p, 0 as s_vozv_p, 
              0 as q_zakaz, 0 as s_zakaz, 
              0 as q_ost_end, 0 as s_ost_end 
      from smspec d1, smdocuments d2 , smcard m , sacardclass t, smstorelocations dd1, smclientinfo dd2 
      where (d2.id=d1.docid) 
          and (d2.opcode in (0, 9, 11, 16))  ---- приход (накладные)
          and (d2.docstate=3) 
          and (d2.createdat>=TO_DATE('01.07.2009','DD.MM.YYYY')) 
          and (d2.createdat<=TO_DATE('30.07.2009','DD.MM.YYYY')) 
      and d2.clientindex  IS NOT NULL 
      and d2.locationto  > -2 
   AND ( t.TREE LIKE  '2.%' ) AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass)
          and (d2.locationto = dd1.id) 
          and (dd2.id=d2.clientindex) 
          AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass) 
  union all
      select dd1.name as store, dd2.name as suppl, d1.article, m.name as kardname, 
              0 as q_ost_start, 0 as s_ost_start, 
              0 as q_in, 0 as s_in, 
              0 as q_prod, 0 as s_prod, 
              d1.quantity as q_vozv_p, d1.itemprice*d1.quantity as s_vozv_p, 
              0 as q_zakaz, 0 as s_zakaz, 
              0 as q_ost_end, 0 as s_ost_end 
      from smspec d1, smdocuments d2 , smcard m , sacardclass t, smstorelocations dd1, smclientinfo dd2 
      where (d2.id=d1.docid) 
          and (d2.opcode in (2, 7, 8, 10, 17))  -- расход (накладные)
          and (d2.docstate=3) 
          and (d2.createdat>=TO_DATE('01.07.2009','DD.MM.YYYY')) 
          and (d2.createdat<=TO_DATE('30.07.2009','DD.MM.YYYY')) 
      and d2.clientindex  IS NOT NULL 
      and d2.locationto  > -2 
   AND ( t.TREE LIKE  '2.%' ) AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass)
          and (d2.locationfrom = dd1.id) 
          and (dd2.id=d2.clientindex) 
          AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass) 
   union all
       select dd1.name as store, '-1' as suppl, d1.article, m.name as kardname, 
              0 as q_ost_start, 0 as s_ost_start, 
              0 as q_in, 0 as s_in, 
              d1.quantity as q_prod, d1.itemprice*d1.quantity as s_prod, 
              0 as q_vozv_p, 0 as s_vozv_p, 
              0 as q_zakaz, 0 as s_zakaz, 
              0 as q_ost_end, 0 as s_ost_end 
      from smspec d1, smdocuments d2 , smcard m , sacardclass t, smstorelocations dd1 
      where (d2.id=d1.docid) 
          and (d2.opcode = 1)  -- продажа (касса)
          and (d2.docstate=3) 
          and (d2.createdat>=TO_DATE('01.07.2009','DD.MM.YYYY')) 
          and (d2.createdat<=TO_DATE('30.07.2009','DD.MM.YYYY')) 
          and d2.locationfrom  > -2 
   AND ( t.TREE LIKE  '2.%' ) AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass)
          and (d2.locationfrom = dd1.id) 
          AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass) 
   union all
       select dd1.name as store, '-1' as suppl, d1.article, m.name as kardname, 
              0 as q_ost_start, 0 as s_ost_start, 
              0 as q_in, 0 as s_in, 
              -d1.quantity as q_prod, -d1.itemprice*d1.quantity as s_prod, 
              0 as q_vozv_p, 0 as s_vozv_p, 
              0 as q_zakaz, 0 as s_zakaz, 
              0 as q_ost_end, 0 as s_ost_end 
      from smspec d1, smdocuments d2 , smcard m , sacardclass t, smstorelocations dd1 
      where (d2.id=d1.docid) 
          and (d2.opcode = 3)  -- -- возврат (касса)
          and (d2.docstate=3) 
          and (d2.createdat>=TO_DATE('01.07.2009','DD.MM.YYYY')) 
          and (d2.createdat<=TO_DATE('30.07.2009','DD.MM.YYYY')) 
      and d2.locationfrom  > -2 
   AND ( t.TREE LIKE  '2.%' ) AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass)
          and (d2.locationfrom = dd1.id) 
          AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass) 
   union all
       select dd1.name as store, dd2.name as suppl, d1.article, m.name as kardname, 
              0 as q_ost_start, 0 as s_ost_start, 
              0 as q_in, 0 as s_in, 
              0 as q_prod, 0 as s_prod, 
              0 as q_vozv_p, 0 as s_vozv_p, 
              d1.quantity as q_zakaz, d1.itemprice*d1.quantity as s_zakaz, 
              0 as q_ost_end, 0 as s_ost_end 
      from smspec d1, smdocuments d2 , smcard m , sacardclass t, smstorelocations dd1, smclientinfo dd2 
      where (d2.id=d1.docid) 
          and (d2.doctype = 'OR')  -- заказы (не исполненные)
          and (d2.docstate=2) 
          and (d2.createdat>=TO_DATE('01.07.2009','DD.MM.YYYY')) 
          and (d2.createdat<=TO_DATE('30.07.2009','DD.MM.YYYY')) 
      and d2.clientindex  IS NOT NULL 
      and d2.location  > -2 
   AND ( t.TREE LIKE  '2.%' ) AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass)
          and (d2.location = dd1.id) 
          and (dd2.id=d2.clientindex) 
          AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass)  )
   group by suppl, store, article, kardname        
  ------------------------------------------------------------
    
  Statement Id=88228624   Type=
  Cost=2,0960068036193E-317  TimeStamp=03-08-09::10::11:13
  
       (1)  SELECT STATEMENT  ALL_ROWS 
     Est. Rows: 71  Cost: 168668
       (82)  HASH GROUP BY 
     Est. Rows: 71  Cost: 168668
           (81)  VIEW (Embedded SQL) 
                Est. Rows: 71  Cost: 168667
               (80)  UNION-ALL
                   (18)  HASH JOIN 
                        Est. Rows: 67  Cost: 146221
                       (16)  HASH JOIN 
                            Est. Rows: 117  Cost: 146212
                           (5)  VIEW VIEW SUPERMAG.index$_join$_006 
                                Est. Rows: 39  Cost: 3
                               (4)  HASH JOIN
                                   (2)  INDEX (UNIQUE) INDEX RANGE SCAN SUPERMAG.SMCSTORELOCATIONS_PK  [Analyzed] 
                                        Est. Rows: 39  Cost: 2
                                   (3)  INDEX (UNIQUE) INDEX FAST FULL SCAN SUPERMAG.SMCSTORELOCATIONS_NAME  [Analyzed] 
                                        Est. Rows: 39  Cost: 1
                           (15)  HASH JOIN 
                                Est. Rows: 278  Cost: 146209
                               (7)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMDOCUMENTS  [Analyzed] 
                                    Blocks: 23750 Est. Rows: 794 of 1884655  Cost: 5039
                                   (6)  INDEX INDEX RANGE SCAN SUPERMAG.SMDOCUMENTS_CREATEDAT  [Analyzed] 
                                        Est. Rows: 77909  Cost: 255
                               (14)  HASH JOIN 
                                    Est. Rows: 641550  Cost: 141159
                                   (11)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCARD  [Analyzed] 
                                   (11)   Blocks: 1194 Est. Rows: 44 of 50507  Cost: 15 
                                        Tablespace: USERS
                                       (10)  NESTED LOOPS 
                                            Est. Rows: 495  Cost: 156
                                           (8)  TABLE TABLE ACCESS FULL SUPERMAG.SACARDCLASS  [Analyzed] 
                                           (8)   Blocks: 13 Est. Rows: 11 of 1274  Cost: 5 
                                                Tablespace: USERS
                                           (9)  INDEX INDEX RANGE SCAN SUPERMAG.SMCARDCLASSIFID  [Analyzed] 
                                                Est. Rows: 44  Cost: 1
                                   (13)  PARTITION LIST ALL 
                                        Est. Rows: 65437530  Cost: 139861
                                       (12)  TABLE TABLE ACCESS FULL SUPERMAG.SMSPEC  [Analyzed] 
                                            Blocks: 568578 Est. Rows: 65437530 of 65437530  Cost: 139861
                       (17)  TABLE TABLE ACCESS FULL SUPERMAG.SMCLIENTINFO  [Analyzed] 
                       (17)   Blocks: 28 Est. Rows: 917 of 917  Cost: 8 
                            Tablespace: USERS
                   (34)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMSPEC  [Analyzed] 
                   (34)   Blocks: 568578 Est. Rows: 1 of 65437530  Cost: 11 
                        Tablespace: USERS
                       (33)  NESTED LOOPS 
                            Est. Rows: 1  Cost: 7520
                           (31)  NESTED LOOPS 
                                Est. Rows: 218  Cost: 5117
                               (28)  MERGE JOIN CARTESIAN 
                                    Est. Rows: 5  Cost: 5049
                                   (25)  NESTED LOOPS 
                                        Est. Rows: 1  Cost: 5043
                                       (22)  HASH JOIN 
                                            Est. Rows: 1  Cost: 5042
                                           (20)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMDOCUMENTS  [Analyzed] 
                                                Blocks: 23750 Est. Rows: 8 of 1884655  Cost: 5039
                                               (19)  INDEX INDEX RANGE SCAN SUPERMAG.SMDOCUMENTS_CREATEDAT  [Analyzed] 
                                                    Est. Rows: 77909  Cost: 255
                                           (21)  TABLE TABLE ACCESS FULL SUPERMAG.SMSTORELOCATIONS  [Analyzed] 
                                           (21)   Blocks: 5 Est. Rows: 39 of 39  Cost: 3 
                                                Tablespace: USERS
                                       (24)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCLIENTINFO  [Analyzed] 
                                       (24)   Blocks: 28 Est. Rows: 1 of 917  Cost: 1 
                                            Tablespace: USERS
                                           (23)  INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCCLIENTINFO_PK  [Analyzed] 
                                                Est. Rows: 1
                                   (27)  BUFFER SORT 
                                        Est. Rows: 11  Cost: 5048
                                       (26)  TABLE TABLE ACCESS FULL SUPERMAG.SACARDCLASS  [Analyzed] 
                                       (26)   Blocks: 13 Est. Rows: 11 of 1274  Cost: 5 
                                            Tablespace: USERS
                               (30)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCARD  [Analyzed] 
                               (30)   Blocks: 1194 Est. Rows: 44 of 50507  Cost: 15 
                                    Tablespace: USERS
                                   (29)  INDEX INDEX RANGE SCAN SUPERMAG.SMCARDCLASSIFID  [Analyzed] 
                                        Est. Rows: 44  Cost: 1
                           (32)  INDEX INDEX RANGE SCAN SUPERMAG.SMSPEC_ART  [Analyzed] 
                                Est. Rows: 1  Cost: 10
                   (48)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMSPEC  [Analyzed] 
                        Blocks: 568578 Est. Rows: 1 of 65437530  Cost: 11
                       (47)  NESTED LOOPS 
                            Est. Rows: 1  Cost: 5587
                           (45)  NESTED LOOPS 
                                Est. Rows: 48  Cost: 5058
                               (42)  MERGE JOIN CARTESIAN 
                                    Est. Rows: 1  Cost: 5043
                                   (39)  NESTED LOOPS 
                                        Est. Rows: 1  Cost: 5038
                                       (36)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMDOCUMENTS  [Analyzed] 
                                            Blocks: 23750 Est. Rows: 1 of 1884655  Cost: 5037
                                           (35)  INDEX INDEX RANGE SCAN SUPERMAG.SMDOCUMENTS_CREATEDAT  [Analyzed] 
                                                Est. Rows: 77909  Cost: 255
                                       (38)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMSTORELOCATIONS  [Analyzed] 
                                       (38)   Blocks: 5 Est. Rows: 1 of 39  Cost: 1 
                                            Tablespace: USERS
                                           (37)  INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCSTORELOCATIONS_PK  [Analyzed] 
                                                Est. Rows: 1
                                   (41)  BUFFER SORT 
                                        Est. Rows: 11  Cost: 5042
                                       (40)  TABLE TABLE ACCESS FULL SUPERMAG.SACARDCLASS  [Analyzed] 
                                       (40)   Blocks: 13 Est. Rows: 11 of 1274  Cost: 5 
                                            Tablespace: USERS
                               (44)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCARD  [Analyzed] 
                               (44)   Blocks: 1194 Est. Rows: 44 of 50507  Cost: 15 
                                    Tablespace: USERS
                                   (43)  INDEX INDEX RANGE SCAN SUPERMAG.SMCARDCLASSIFID  [Analyzed] 
                                        Est. Rows: 44  Cost: 1
                           (46)  INDEX INDEX RANGE SCAN SUPERMAG.SMSPEC_ART  [Analyzed] 
                                Est. Rows: 1  Cost: 10
                   (62)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMSPEC  [Analyzed] 
                        Blocks: 568578 Est. Rows: 1 of 65437530  Cost: 11
                       (61)  NESTED LOOPS 
                            Est. Rows: 1  Cost: 5246
                           (59)  NESTED LOOPS 
                                Est. Rows: 17  Cost: 5058
                               (56)  MERGE JOIN CARTESIAN 
                                    Est. Rows: 1  Cost: 5043
                                   (53)  NESTED LOOPS 
                                        Est. Rows: 1  Cost: 5038
                                       (50)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMDOCUMENTS  [Analyzed] 
                                            Blocks: 23750 Est. Rows: 1 of 1884655  Cost: 5037
                                           (49)  INDEX INDEX RANGE SCAN SUPERMAG.SMDOCUMENTS_CREATEDAT  [Analyzed] 
                                                Est. Rows: 77909  Cost: 255
                                       (52)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMSTORELOCATIONS  [Analyzed] 
                                       (52)   Blocks: 5 Est. Rows: 1 of 39  Cost: 1 
                                            Tablespace: USERS
                                           (51)  INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCSTORELOCATIONS_PK  [Analyzed] 
                                                Est. Rows: 1
                                   (55)  BUFFER SORT 
                                        Est. Rows: 11  Cost: 5042
                                       (54)  TABLE TABLE ACCESS FULL SUPERMAG.SACARDCLASS  [Analyzed] 
                                       (54)   Blocks: 13 Est. Rows: 11 of 1274  Cost: 5 
                                            Tablespace: USERS
                               (58)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCARD  [Analyzed] 
                               (58)   Blocks: 1194 Est. Rows: 44 of 50507  Cost: 15 
                                    Tablespace: USERS
                                   (57)  INDEX INDEX RANGE SCAN SUPERMAG.SMCARDCLASSIFID  [Analyzed] 
                                        Est. Rows: 44  Cost: 1
                           (60)  INDEX INDEX RANGE SCAN SUPERMAG.SMSPEC_ART  [Analyzed] 
                                Est. Rows: 1  Cost: 10
                   (79)  TABLE TABLE ACCESS BY GLOBAL INDEX ROWID SUPERMAG.SMSPEC  [Analyzed] 
                        Blocks: 568578 Est. Rows: 1 of 65437530  Cost: 11
                       (78)  NESTED LOOPS 
                            Est. Rows: 1  Cost: 4094
                           (76)  NESTED LOOPS 
                                Est. Rows: 78  Cost: 3234
                               (73)  MERGE JOIN CARTESIAN 
                                    Est. Rows: 2  Cost: 3207
                                   (70)  NESTED LOOPS 
                                        Est. Rows: 1  Cost: 3201
                                       (67)  NESTED LOOPS 
                                            Est. Rows: 1  Cost: 3200
                                           (64)  PARTITION LIST SINGLE 
                                                Est. Rows: 1  Cost: 3199
                                               (63)  TABLE TABLE ACCESS FULL SUPERMAG.SMDOCUMENTS  [Analyzed] 
                                                    Blocks: 23750 Est. Rows: 1 of 1884655  Cost: 3199
                                           (66)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMSTORELOCATIONS  [Analyzed] 
                                           (66)   Blocks: 5 Est. Rows: 1 of 39  Cost: 1 
                                                Tablespace: USERS
                                               (65)  INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCSTORELOCATIONS_PK  [Analyzed] 
                                                    Est. Rows: 1
                                       (69)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCLIENTINFO  [Analyzed] 
                                       (69)   Blocks: 28 Est. Rows: 1 of 917  Cost: 1 
                                            Tablespace: USERS
                                           (68)  INDEX (UNIQUE) INDEX UNIQUE SCAN SUPERMAG.SMCCLIENTINFO_PK  [Analyzed] 
                                                Est. Rows: 1
                                   (72)  BUFFER SORT 
                                        Est. Rows: 11  Cost: 3206
                                       (71)  TABLE TABLE ACCESS FULL SUPERMAG.SACARDCLASS  [Analyzed] 
                                       (71)   Blocks: 13 Est. Rows: 11 of 1274  Cost: 5 
                                            Tablespace: USERS
                               (75)  TABLE TABLE ACCESS BY INDEX ROWID SUPERMAG.SMCARD  [Analyzed] 
                               (75)   Blocks: 1194 Est. Rows: 44 of 50507  Cost: 15 
                                    Tablespace: USERS
                                   (74)  INDEX INDEX RANGE SCAN SUPERMAG.SMCARDCLASSIFID  [Analyzed] 
                                        Est. Rows: 44  Cost: 1
                           (77)  INDEX INDEX RANGE SCAN SUPERMAG.SMSPEC_ART  [Analyzed] 
                                Est. Rows: 1  Cost: 10

Цитата:

OlegON ➤ 2) Цель его выполнения

Получить оборотку по товарам по документам, в ценах документов

Цитата:

OlegON ➤ 4) as так не использовать

Хорошо, но объясни подробнее, почему?

Цитата:

OlegON ➤ 5) Количество возвращаемых строк

зависит от периода, выбраной группы или поставщика, в данном случае 10000, но это по одной группе и за месяц, соответственно выборка будет в разы больше (если запустят по всему

Mr_Vito · █ 03.08.2009 09:51

Цитата:

vdm ➤ Для начала - там ошибка в связи smspec и smdocuments.
Нет проверки типа документа (d2.doctype=d1.doctype).
Возможно стоит добавить и сравнение самих типов ('WI', 'WO', 'CS', 'CR').

Это не ошибка, у нас номера документов не пересекаются, поэтому это сравнение я сознательно опустил

Mr_Vito · █ 03.08.2009 10:03

Цитата:

MirProd ➤ Многое не ясно. Откуда должны взяться остатки на начало и конец (q_ost...)?

другим запросиком, он пробегал на форуме:

Код:

     select dd.names as suppl, dd1.name as store, dd.article, dd2.name as kardname, sum(dd.q) as q_ost_end, sum(dd.itemprice*dd.q) as s_ost_end,
            0 as q_in, 0 as s_in, 0 as q_prod, 0 as s_prod, 0 as q_vozv_p, 0 as s_vozv_p, 0 as q_zakaz, 0 as s_zakaz, 0 as q_ost_start, 0 as s_ost_start  from
        (select t1.article, t1.locationto, t1.name as names, t1.itemprice,
       	case
       	when t2.quantity-sum(t1.quantity) over (partition by t1.article, t1.locationto, t1.itemprice order by t1.createdat desc rows unbounded preceding)+t1.quantity>=t1.quantity
       	then t1.quantity
       	when t2.quantity-sum(t1.quantity) over (partition by t1.article, t1.locationto, t1.itemprice order by t1.createdat desc rows unbounded preceding)+t1.quantity<t1.quantity
       	and t2.quantity-sum(t1.quantity) over (partition by t1.article, t1.locationto, t1.itemprice order by t1.createdat desc rows unbounded preceding)+t1.quantity>0
       	then t2.quantity-sum(t1.quantity) over (partition by t1.article, t1.locationto, t1.itemprice order by t1.createdat desc rows unbounded preceding)+t1.quantity
     	else 0
     	end q
     from
       (select t.article, t.locationto, t.createdat, t.id, t.quantity, t.itemprice, t.lcl, cl.name
     from (select d1.article, d2.locationto, d2.createdat, d2.id,  d1.quantity, d1.itemprice, max(d2.clientindex) keep (dense_rank last order by d2.createdat) lcl
     	from smspec d1, smdocuments d2 , smcard m , sacardclass t
     	where d2.id=d1.docid
     	and d2.opcode=0       -- остаток начало
     	and d2.docstate=3
     	and d2.clientindex  IS NOT NULL 
     	and d2.locationto  > -2 
     AND ( t.TREE LIKE  '3.3.%' ) 
     AND (M.ARTICLE = d1.ARTICLE) AND (t.id = m.idclass)
     	group by d1.article, d2.locationto, d2.createdat, d2.id,  d1.quantity, d1.itemprice) t, smclientinfo cl
     where t.lcl=cl.id
         and t.locationto  > -2 
     order by t.article, t.createdat desc, t.itemprice desc) t1, ttremains t2
     where t1.article=t2.article and t2.quantity>0 and t1.locationto=t2.storeloc) dd, smstorelocations dd1, smcard dd2
     where (dd.q>0) and (dd.locationto=dd1.id) and (dd2.ARTICLE=dd.article)
     group by dd.names, dd.article, dd2.name, dd1.name

Этим считаю и на начало и на конец остатки с предварительным запуском
supermag.remains.calc

kadr · █ 03.08.2009 12:29

Цитата:

Mr_Vito ➤ Это не ошибка, у нас номера документов не пересекаются, поэтому это сравнение я сознательно опустил

Ты опустил, а Oracle же не Оракул, он не догадается что ты от него хочешь, ему надо четко указывать типы чтобы по возможности индексы подцепились

MirProd · █ 03.08.2009 12:58

Тот же результат по сути твоего запроса, добавь что еще нужно..

Код:

select c.article,
	c.name,
	t2.loc,
	l.name,
	t2.clientindex,
	sp.name,
	t2.inq,
	t2.outq,
	t2.saleq,
	t2.zakq
from smcard c,
	sacardclass cl,
	smstorelocations l,
	smclientinfo sp,
	(select decode(s.doctype, 'WO', locationfrom, 'WI', locationto, 'OR', location, 'CS', locationfrom, 'CR', locationto) loc,
		article,
		clientindex,
		sum(decode(s.doctype, 'WI', quantity, 0)) inq,
		sum(decode(s.doctype, 'WO', quantity, 0)) outq,
		sum(decode(s.doctype, 'OR', quantity, 0)) zakq,
		sum(decode(s.doctype, 'CS', quantity, 'CR', -quantity, 0)) saleq
	from smspec s,
		smdocuments d
	where s.docid=d.id
		and s.doctype=d.doctype
		and d.createdat between :dat1 and :dat2
		and d.doctype in ('WI', 'WO', 'OR', 'CS', 'CR')
		and (case when (d.doctype='OR' and d.docstate=2) then 1
			when (d.doctype in ('WI', 'WO', 'CS', 'CR') and d.docstate=3) then 1
			else 0 end)=1
	group by decode(s.doctype, 'WO', locationfrom, 'WI', locationto, 'OR', location, 'CS', locationfrom, 'CR', locationto), article, clientindex) t2
where c.article=t2.article
	and c.idclass=cl.id
	and cl.tree like :tree||'%'
	and t2.loc=l.id
	and t2.clientindex=sp.id(+)