Заставить ораклу не просчитывать повторно вьюху

VicSO
Дата: 05.03.2011 07:20:14
проблема вот такая, есть views назовем views_M возвращаемые поля (M1,M2,M3,S1) она выдает результат 1-2 минуты
потом есть запрос

select M1,M2,M3,sum(S1)
from views_M
group by M1,M2,M3
union all
select M1,M2,'Итого' M3,sum(S1)
from views_M
group by M1,M2
union all
select M1,'Итого' M2,'Итого' M3,sum(S1)
from views_M
group by M1
union all
select 'Итого' M1, M2, M3,sum(S1)
from views_M
group by M2,M3
union all
select 'Итого' M1,M2,'Итого' M3,sum(S1)
from views_M
group by M2
union all
select 'Итого' M1,'Итого' M2,'Итого' M3,sum(S1)
from views_M

вот как заставить ораклу повторно не просчитывать views_M так как данные не меняются
создать временную таблицу предварительно запихнуть данные вьюхи и дальше уже делать, это плохой вариант.

какую подсказку можно использовать в оракле чтоб просчитав views_M уже в остальных использовала результат, а не повторно считало?

oracle 9.2 версия
Добрый Э - Эх
Дата: 05.03.2011 07:37:27
Открыть, наконец, для себя:
1) WITH-кляузу (subquery factoring) + HINT /*+ materialize*/
2) Расширенные операции группировки: rollup, cube, grouping sets.
VicSO
Дата: 05.03.2011 07:59:23
ага спасибо, кто знал что есть такое :)
просто спрашивал тут на предприятии у спецов которые уже 5-10 лет с ораклой проработали, и не кто не мог подсказать.

но вернуть к предыдущему вопросу вообще есть подсказка или как оракле сказать чтоб не просчитывала вьюшку? :)
если конечно есть такое?

rollup решил то что мне надо было :)
Добрый Э - Эх
Дата: 05.03.2011 08:08:32
Ещё раз - если вьюшку "рассчитать" в секции WITH, то Оракл и сам поймет, что при многократном обращении к ней повторного расчета делать не нужно.
Ну а если не поймет - то хинт /*+ materialize*/ ему выкрутит руки и заставит "железно" сделать расчет один раз.

Но запрос придется записать примерно в таком виде:
with views_M_tmp as (select /*+ materialize*/ * from views_M)
------
select M1,M2,M3,sum(S1)
from views_M_tmp
group by M1,M2,M3
union all
select M1,M2,'Итого' M3,sum(S1)
from views_M_tmp
group by M1,M2
union all
select M1,'Итого' M2,'Итого' M3,sum(S1)
from views_M_tmp
group by M1
union all
select 'Итого' M1, M2, M3,sum(S1)
from views_M_tmp
group by M2,M3
union all
select 'Итого' M1,M2,'Итого' M3,sum(S1)
from views_M_tmp
group by M2
union all
select 'Итого' M1,'Итого' M2,'Итого' M3,sum(S1)
from views_M_tmp
VicSO
Дата: 05.03.2011 09:55:32
спасибо большое :)
но вот возникла немного проблема.

у меня есть шесть М1,М2,М3,М4,М5
как вы и посоветовал я сделал
GROUP BY rollup (М1,М2,М3,М4,М5)
но мне получается нужно

GROUP BY rollup (М1,М2), rollup(М3,М4,М5)

та как мне нужно получить
М3 М4 М5
М1
М2

и когда меняю в GROUP BY rollup (М1,М2,М3,М4,М5)
на GROUP BY cube (М1,М2,М3,М4,М5) выдает ошибку

ORA-00604: error occurred at recursive SQL level 1
ORA-00918: column ambiguously defined
VicSO
Дата: 05.03.2011 09:56:35
та как мне нужно получить
____М3 М4 М5
М1
_М2
VicSO
Дата: 05.03.2011 10:10:52
вот запрос выдает ошибку
SELECT 
         nvl(tp                ,' Итого')  tp                               
       , nvl(agnabbr           ,' Итого')  agnabbr                            
       , nvl(grp_category      ,' Итого')  grp_category                       
       , nvl(GROUP_NAME        ,' Итого')  GROUP_NAME                         
       , nvl(GRP_CATEGORY_BREND,' Итого')  GRP_CATEGORY_BREND                 
       , nvl(NOMEN_NAME        ,' Итого')  NOMEN_NAME                       
       ,count (DISTINCT   agnfifo) TT       
       ,SUM(sdal) sdal                      
       ,SUM(dal_C) dal_C                   
       ,SUM(dal_C_s) dal_C_s                

       ,count (DISTINCT   agnfifo_1) TT_D        
       ,SUM(sdal_1) sdal_D                      
       ,SUM(dal_C_1) dal_C_D                    
       ,SUM(dal_C_s_1) dal_C_s_D              
       
from V_VICSO_SALE_OTDO

 GROUP BY rollup(
         tp                                 
       , agnabbr )                           
       , rollup(grp_category                      
       , GROUP_NAME                         
       , GRP_CATEGORY_BREND                
       , NOMEN_NAME)

а так все работает
GROUP BY rollup(
         tp                                 
       , agnabbr                           
       , grp_category                       
       , GROUP_NAME                         
       , GRP_CATEGORY_BREND                 
       , NOMEN_NAME)


ошибка
ORA-00604: error occurred at recursive SQL level 1
ORA-00918: column ambiguously defined
Добрый Э - Эх
Дата: 05.03.2011 10:23:42
возможно, что оптимизатор некорректно преобразовал запрос, раскрыв твое представление.
попробуй заставить сервер не "вскрывать" текст представления, выполнить его как "черный ящик":
SELECT /*+ NO_MERGE(V_VICSO_SALE_OTDO)*/
         nvl(tp                ,' Итого')  tp                               
       , nvl(agnabbr           ,' Итого')  agnabbr                            
       , nvl(grp_category      ,' Итого')  grp_category                       
       , nvl(GROUP_NAME        ,' Итого')  GROUP_NAME                         
       , nvl(GRP_CATEGORY_BREND,' Итого')  GRP_CATEGORY_BREND                 
       , nvl(NOMEN_NAME        ,' Итого')  NOMEN_NAME                       
       ,count (DISTINCT   agnfifo) TT       
       ,SUM(sdal) sdal                      
       ,SUM(dal_C) dal_C                   
       ,SUM(dal_C_s) dal_C_s                

       ,count (DISTINCT   agnfifo_1) TT_D        
       ,SUM(sdal_1) sdal_D                      
       ,SUM(dal_C_1) dal_C_D                    
       ,SUM(dal_C_s_1) dal_C_s_D              
       
from V_VICSO_SALE_OTDO

 GROUP BY rollup(
         tp                                 
       , agnabbr )                           
       , rollup(grp_category                      
       , GROUP_NAME                         
       , GRP_CATEGORY_BREND                
       , NOMEN_NAME)
VicSO
Дата: 05.03.2011 10:26:35
как выяснилось ему не нравится DISTINCT в count (DISTINCT agnfifo_1) TT_D
как ток убираю DISTINCT все работает, но без DISTINCT там не обойтись :(
Добрый Э - Эх
Дата: 05.03.2011 10:39:02
VicSO
как выяснилось ему не нравится DISTINCT в count (DISTINCT agnfifo_1) TT_D
как ток убираю DISTINCT все работает, но без DISTINCT там не обойтись :(
Не думаю, что дело в этом.
Ибо у меня такой вот синтетический тест вполне себе успешно отрабатывает:
with
  t as 
    (select level l1, level l2, level l3, level l4, level l5, level l6 from dual connect by level <= 10)
--
select sum(distinct l1), count(distinct l1),
       sum(distinct l2), count(distinct l2),
       sum(distinct l3), count(distinct l3),
       sum(distinct l4), count(distinct l4),
       sum(distinct l5), count(distinct l5),
       sum(distinct l6), count(distinct l6)
  from t
 group by rollup(l1,l2), 
          rollup(l3,l4,l5,l6)