Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Материализованное представление?  [new]
__mike__
Member

Откуда: Белгород
Сообщений: 553
доброго времени суток!
есть
SQL> select * from v$version;
BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production


как в книге Кайта создаю материализованное представление

CREATE materialized view saldo200607
BUILD IMMEDIATE 
REFRESH ON DEMAND
enable query rewrite
AS
SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
            FROM pay_abonent pa, list_ceh lc
           WHERE (pa.pay_inf_num = 200607)
             AND (lc.lceh_cod = pa.pay_ceh)
             AND (lc.owner_id = 1)
group by pa.pay_abon_num             
/

create index saldo200607_idx on saldo200607(abon_num);


затем собираю статистику по схеме
и пытаюсь извлечь данные
 SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
              FROM pay_abonent pa, list_ceh lc
             WHERE (pa.pay_inf_num = 200607)
               AND (lc.lceh_cod = pa.pay_ceh)
               AND (lc.owner_id = 1)
  group by pa.pay_abon_num    
  ------------------------------------------------------------
    
  Statement Id=4203132   Type=
  Cost=2.64023809101798E-308  TimeStamp=09-10-06::16::47:37
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 478 002  Cost: 93
       (2)  TABLE ACCESS FULL IFS.SALDO200607  [Analyzed] 
       (2)   Blocks: 952 Est. Rows: 478 002 of 478 002  Cost: 93 

вроде бы все нормально, но если изменить запарос
  SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
              FROM pay_abonent pa, list_ceh lc
             WHERE (pa.pay_inf_num = 200607)
               AND (lc.lceh_cod = pa.pay_ceh)
               AND (lc.owner_id = 1)
               and (pa.pay_abon_num = :abon)
  group by pa.pay_abon_num    
  ------------------------------------------------------------
    
  Statement Id=6   Type=INDEX
  Cost=0  TimeStamp=09-10-06::16::50:36
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 1  Cost: 2
       (7)  SORT GROUP BY NOSORT 
     Est. Rows: 1  Cost: 2
           (6)  NESTED LOOPS 
                Est. Rows: 5  Cost: 2
               (3)  TABLE ACCESS BY INDEX ROWID IFS.PAY_ABONENT  [Analyzed] 
               (3)   Blocks: 275 352 Est. Rows: 5 of 53 010 722  Cost: 1 
                   (2)  UNIQUE INDEX RANGE SCAN IFS.M2_PAYABN_UK1  [Analyzed] 
                        Est. Rows: 5  Cost: 3
               (5)  TABLE ACCESS BY INDEX ROWID IFS.LIST_CEH  [Analyzed] 
               (5)   Blocks: 5 Est. Rows: 1 of 13  Cost: 1 
                   (4)  UNIQUE INDEX UNIQUE SCAN IFS.M2_LTCEH_PK  [Analyzed] 
                        Est. Rows: 1


получается что не берет он данные из материализованного представления
подскажите почему?
9 окт 06, 16:55    [3238233]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
tru55
Member

Откуда: СПб
Сообщений: 19790
Дык ведь Oracle не обязан использовать MView. Как я понимаю, он строит планы выполнения и с учетом query rewrite и без него, и выбирает самый дешевый...
9 окт 06, 17:11    [3238358]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
__mike__
Member

Откуда: Белгород
Сообщений: 553
select summa from saldo200607 where abon_num=111362;

     SUMMA
----------
    -193,2

План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SALDO200607' (Cost=1
           Card=1 Bytes=9)
   2    1     INDEX (RANGE SCAN) OF 'SALDO200607_IDX' (NON-UNIQUE)
           (Cost=3 Card=1)

Статистика
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          [color=red][b]5  consistent gets
          3  physical reads[/b][/color]
          0  redo size
        383  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
              FROM pay_abonent pa, list_ceh lc
             WHERE (pa.pay_inf_num = 200607)
               AND (lc.lceh_cod = pa.pay_ceh)
  5                 AND (lc.owner_id = 1)
  6  and (pa.pay_abon_num = 111362 )
  7  group by pa.pay_abon_num;

  ABON_NUM      SUMMA
---------- ----------
    111362     -193,2

План выполнения
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=24)
   1    0   SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=24)
   2    1     NESTED LOOPS (Cost=2 Card=5 Bytes=120)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PAY_ABONENT' (Cost=1
           Card=5 Bytes=85)

   4    3         INDEX (RANGE SCAN) OF 'M2_PAYABN_UK1' (UNIQUE) (Cost
          =3 Card=5)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'LIST_CEH' (Cost=1 Ca
          rd=1 Bytes=7)

   6    5         INDEX (UNIQUE SCAN) OF 'M2_LTCEH_PK' (UNIQUE)

Статистика
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         [color=red][b]17  consistent gets[/b][/color]
          0  physical reads
          0  redo size
        444  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

это понятно что СВО выбирает самый дешевый - но

17 блоков против 8 - это все-таки что-то должно значить
9 окт 06, 19:05    [3238933]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
g3d
Member

Откуда: Столица
Сообщений: 158
__mike__
доброго времени суток!
есть
SQL> select * from v$version;
BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production


как в книге Кайта создаю материализованное представление

CREATE materialized view saldo200607
BUILD IMMEDIATE 
REFRESH ON DEMAND
enable query rewrite
AS
SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
            FROM pay_abonent pa, list_ceh lc
           WHERE (pa.pay_inf_num = 200607)
             AND (lc.lceh_cod = pa.pay_ceh)
             AND (lc.owner_id = 1)
group by pa.pay_abon_num             
/

create index saldo200607_idx on saldo200607(abon_num);


затем собираю статистику по схеме
и пытаюсь извлечь данные
 SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
              FROM pay_abonent pa, list_ceh lc
             WHERE (pa.pay_inf_num = 200607)
               AND (lc.lceh_cod = pa.pay_ceh)
               AND (lc.owner_id = 1)
  group by pa.pay_abon_num    
  ------------------------------------------------------------
    
  Statement Id=4203132   Type=
  Cost=2.64023809101798E-308  TimeStamp=09-10-06::16::47:37
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 478 002  Cost: 93
       (2)  TABLE ACCESS FULL IFS.SALDO200607  [Analyzed] 
       (2)   Blocks: 952 Est. Rows: 478 002 of 478 002  Cost: 93 

вроде бы все нормально, но если изменить запарос
  SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
              FROM pay_abonent pa, list_ceh lc
             WHERE (pa.pay_inf_num = 200607)
               AND (lc.lceh_cod = pa.pay_ceh)
               AND (lc.owner_id = 1)
               and (pa.pay_abon_num = :abon)
  group by pa.pay_abon_num    
  ------------------------------------------------------------
    
  Statement Id=6   Type=INDEX
  Cost=0  TimeStamp=09-10-06::16::50:36
  
       (1)  SELECT STATEMENT  CHOOSE 
     Est. Rows: 1  Cost: 2
       (7)  SORT GROUP BY NOSORT 
     Est. Rows: 1  Cost: 2
           (6)  NESTED LOOPS 
                Est. Rows: 5  Cost: 2
               (3)  TABLE ACCESS BY INDEX ROWID IFS.PAY_ABONENT  [Analyzed] 
               (3)   Blocks: 275 352 Est. Rows: 5 of 53 010 722  Cost: 1 
                   (2)  UNIQUE INDEX RANGE SCAN IFS.M2_PAYABN_UK1  [Analyzed] 
                        Est. Rows: 5  Cost: 3
               (5)  TABLE ACCESS BY INDEX ROWID IFS.LIST_CEH  [Analyzed] 
               (5)   Blocks: 5 Est. Rows: 1 of 13  Cost: 1 
                   (4)  UNIQUE INDEX UNIQUE SCAN IFS.M2_LTCEH_PK  [Analyzed] 
                        Est. Rows: 1


получается что не берет он данные из материализованного представления
подскажите почему?


Материализованные представления существуют только на основе запросов, это факттически и есть запрос, и поэтому если ты меняешь запрос, то Оракл видит что запрос другой и уже не направляет тебя в этому представлению.

Для твоего случая делается все так:

 Select * from (  SELECT pa.pay_abon_num abon_num , NVL (SUM (pay_saldo), 0) summa
              FROM pay_abonent pa, list_ceh lc
             WHERE (pa.pay_inf_num = 200607)
               AND (lc.lceh_cod = pa.pay_ceh)
               AND (lc.owner_id = 1)
group by pa.pay_abon_num  )
where pay_abon_num = :abon 


Поробуй так.

А вообще чтобы запросы были REWrite нужно использовать DIMENSIONS, через них как раз и задаешь, что кого определяет и оракл понимает куда отсылать твой запрос.
10 окт 06, 11:17    [3240436]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
to __mike__ проверьте параметр query_rewrite_integrity попробуйте trusted
10 окт 06, 11:25    [3240507]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
__mike__
Member

Откуда: Белгород
Сообщений: 553
g3d

так как вы предлагали получилось, тогда получается что должно быть полное совпадение после FROM, хотя в примере у Кайта частичное совпадение

на счет DIMENSIONS поробую
10 окт 06, 11:31    [3240564]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
DВА
Member

Откуда:
Сообщений: 5439
__mike__

17 блоков против 8 - это все-таки что-то должно значить

при использовании переменной - может и ничего не значить.
Поставьте хинт rewrite чтоб быть уверенным что Оракл не переписывает запрос из идиологических соображений.
10 окт 06, 11:38    [3240629]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
g3d
Member

Откуда: Столица
Сообщений: 158
__mike__
g3d

так как вы предлагали получилось, тогда получается что должно быть полное совпадение после FROM, хотя в примере у Кайта частичное совпадение

на счет DIMENSIONS поробую


Ну это Том=))... он же считай мистик, а так только увы полное совпадение
10 окт 06, 11:46    [3240711]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
__mike__
Member

Откуда: Белгород
Сообщений: 553
DBA


попробовал REWRITE - эффект тот же
10 окт 06, 11:46    [3240712]     Ответить | Цитировать Сообщить модератору
 Re: Материализованное представление?  [new]
__mike__
Member

Откуда: Белгород
Сообщений: 553
g3d
Ну это Том=))... он же считай мистик, а так только увы полное совпадение


вот и верь после этого людям :-)
10 окт 06, 12:00    [3240829]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить