Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
MLight
Member

Откуда: Москва
Сообщений: 264
Есть материализованное представление

CREATE MATERIALIZED VIEW XX_MTV_HEADERS_LINES     
AS 
SELECT GH.DEFAULT_EFFECTIVE_DATE, 
       SUM( NVL( gl.accounted_dr,0)-NVL( gl.accounted_cr,0)) ACCOUNTED_SUM       
  FROM gl.gl_je_lines gl, gl.gl_je_headers gh
 WHERE gl.je_header_id = gh.je_header_id
GROUP BY GH.DEFAULT_EFFECTIVE_DATE

Есть отчетность в OEBSе, где пользователь должен задать период дат и получить результат

SELECT xxgl_rep14_pkg.uf_checkperiod (gh.default_effective_date,
                                           TO_DATE (:b2, 'dd.mm.yyyy'),
                                           TO_DATE (:b1, 'dd.mm.yyyy')
                                          ), 
       SUM (NVL (gl.accounted_dr, 0) - NVL (gl.accounted_cr, 0))   
  FROM gl.gl_je_lines gl, gl.gl_je_headers gh
 WHERE gl.je_header_id = gh.je_header_id     
GROUP BY xxgl_rep14_pkg.uf_checkperiod (gh.default_effective_date,
                                           TO_DATE (:b2, 'dd.mm.yyyy'),
                                           TO_DATE (:b1, 'dd.mm.yyyy')
                                          )
Plan
SELECT STATEMENT  CHOOSECost: 228,265  			
	4 SORT GROUP BY  Cost: 228,265  Bytes: 29,079  Cardinality: 1,077  		
		3 HASH JOIN  Cost: 163,495  Bytes: 618,042,960  Cardinality: 22,890,480  	
			1 TABLE ACCESS FULL GL.GL_JE_HEADERS Cost: 66,645  Bytes: 122,763,298  Cardinality: 8,768,807  
			2 TABLE ACCESS FULL GL.GL_JE_LINES Cost: 77,244  Bytes: 297,576,240  Cardinality: 22,890,480  

Все плохо, мат. представление не используеться. Но как только я ручками меняю bind-переменные на конкретные значение [вместо TO_DATE (:b2, 'dd.mm.yyyy') пишу TO_DATE ('30.08.2008', 'dd.mm.yyyy') и. т.д.] , то все становиться в порядке.

Plan
SELECT STATEMENT  CHOOSECost: 5,669  Bytes: 11,858  Cardinality: 1,078  		
	2 SORT GROUP BY  Cost: 5,669  Bytes: 11,858  Cardinality: 1,078  	
		1 TABLE ACCESS FULL APPS.XX_MTV_HEADERS_LINES Cost: 1,822  Bytes: 25,995,530  Cardinality: 2,363,230  

Как обойти это ограничение? На сейчас есть 2 варианта: испоользовать подзапрос или exec 'insert into select ...' || :b2 || '...'. Проблема в том, что таких отчетов много и переделывать каждый не очень хочется. Можно ли это обойти настройками БД? Как рассказать оптимизатору, что независимо от того какие будут переменные, надо использовать мат. представления?
15 дек 08, 16:28    [6573066]     Ответить | Цитировать Сообщить модератору
 Re: MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
а вы внутрь xxgl_rep14_pkg.uf_checkperiod залезть не пробовали?
15 дек 08, 17:04    [6573350]     Ответить | Цитировать Сообщить модератору
 Re: MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
MLight
Member

Откуда: Москва
Сообщений: 264
Пробовал :). Пробовал переделать на CASE, но результат не поменялся.

FUNCTION uf_checkperiod( p_date DATE, p_period_from DATE, p_period_to DATE) RETURN VARCHAR2
  AS
    l_check_status VARCHAR2(1) := 'N';
   BEGIN
     IF p_date >= p_period_from AND p_date <= p_period_to THEN
       l_check_status := 'Y';
     END IF;
     RETURN l_check_status;
   END uf_checkperiod;
15 дек 08, 17:14    [6573412]     Ответить | Цитировать Сообщить модератору
 Re: MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
SCSI
Guest
Use /*+REWRITE(XX_MTV_HEADERS_LINES) */ (directly or through outline)
or
alter session set query_rewrite_enabled=force;
15 дек 08, 20:36    [6574193]     Ответить | Цитировать Сообщить модератору
 Re: MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
MLight
Member

Откуда: Москва
Сообщений: 264
/*+REWRITE(XX_MTV_HEADERS_LINES) */ - не помагает
параметры стоят правильные:
query_rewrite_enabled = true
query_rewrite_integrity = enforced
16 дек 08, 10:24    [6575277]     Ответить | Цитировать Сообщить модератору
 Re: MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
MLight
Member

Откуда: Москва
Сообщений: 264
Вот еще пример:
Создаю mat.view
CREATE MATERIALIZED VIEW XX_MTV_BALANCES
     AS 
SELECT gb.PERIOD_NAME
       , SUM(gb.period_net_dr) period_net_dr
       , SUM(gb.period_net_cr) period_net_cr
FROM gl_balances gb
WHERE gb.set_of_books_id = 10
AND (gb.period_net_dr <> 0 OR gb.period_net_cr <> 0)   -- (1) проблемный участок
GROUP BY gb.PERIOD_NAME

Делаю запрос - mat.view не используеться

select SUM(gb.period_net_dr)
from gl_balances gb
WHERE gb.set_of_books_id = 10
and gb.period_name in ('01-08','03-08','02-08')
AND (gb.period_net_dr <> 0 OR gb.period_net_cr <> 0)  -- (1) проблемный участок

Plan
SELECT STATEMENT  CHOOSECost: 18,743  Bytes: 15  Cardinality: 1  		
  2 SORT AGGREGATE  Bytes: 15  Cardinality: 1  	
     1 TABLE ACCESS FULL GL.GL_BALANCES Cost: 18,743  Bytes: 4,218,705  Cardinality: 281,247  


Как только создаю mat.view без (1) и делаю запрос без (1), то все проходит на ура:

Plan
SELECT STATEMENT  CHOOSECost: 6,771  Bytes: 22  Cardinality: 1  		
  2 SORT AGGREGATE  Bytes: 22  Cardinality: 1  	
    1 TABLE ACCESS FULL APPS.XX_MTV_BALANCES Cost: 6,771  Bytes: 801,416  Cardinality: 36,428  
16 дек 08, 14:52    [6577699]     Ответить | Цитировать Сообщить модератору
 Re: MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
SCSI
Guest
query_rewrite_enabled = true
means oracle will rewrite the query only if plan with the MV has better coast.

query_rewrite_integrity = enforced
means oracle will rewrite the query only if it guaranties the same result.


To make sure it is able to rewrite the query I would try it with
query_rewrite_enabled = force (actually the same thing as +REWRITE )
query_rewrite_integrity = stale_tolerated

(on session level)

Those two parameters mean do not worry about the coast and rewrite even if MV is stale.


I oracle does not use MV even with those parameters set then I would try DBMS_MVIEW.EXPLAIN_REWRITE procedure to see why.


Alternatively you can define equivalence for rewrite using DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE.


By the way I do not see why would you need (gb.period_net_dr <> 0 OR gb.period_net_cr <> 0)
It does not make any difference for SUM.
16 дек 08, 21:24    [6580048]     Ответить | Цитировать Сообщить модератору
 Re: MATERIALIZED VIEW + bind переменные (9.2.0.4)  [new]
MLight
Member

Откуда: Москва
Сообщений: 264
Применяя условие (gb.period_net_dr <> 0 OR gb.period_net_cr <> 0) я отсекаю 90% нулей в таблице и MV получается в 10 раз меньше

query_rewrite_enabled = force - не помагает
DBMS_ADVANCED_REWRITE - с 10.1, у меня 9.2

Вот что говорит DBMS_MVIEW.EXPLAIN_REWRITE, :

exec DBMS_MVIEW.EXPLAIN_REWRITE('SELECT SUM(gb.period_net_dr) period_net_dr
       , SUM(gb.period_net_cr) period_net_cr
  FROM gl_balances gb
  WHERE gb.set_of_books_id = 10
  AND gb.CURRENCY_CODE = ''RUR''
  and period_name = ''01-08''
  AND (gb.period_net_dr <> 0 OR gb.period_net_cr <> 0)     
GROUP BY gb.SET_OF_BOOKS_ID, gb.PERIOD_NAME, gb.CODE_COMBINATION_ID, 
gb.ACTUAL_FLAG, gb.BUDGET_VERSION_ID', 'XX_MTV_BALANCES')
;

select * from REWRITE_TABLE

QSM-01082: Соединение материализованного представления, XX_MTV_BALANCES, с таблицей, GL_BALANCES, невозможно
QSM-01102: для материализованного представления, XX_MTV_BALANCES, требуется join back с таблицей, GL_BALANCES, в столбце, PERIOD_NET_CR
QSM-01102: для материализованного представления, XX_MTV_BALANCES, требуется join back с таблицей, GL_BALANCES, в столбце, PERIOD_NET_DR

Пока что решил вручную изменять запросы в некоторых отчетах. Подскажите как проверить валидность MV т.е. не изменялись ли данные в родителской таблице или параметр query_rewrite_integrity = stale_tolerated ?
Хочу сделать примерно такой алгоритм:
IF VALID(XX_MTV_BALANCES) = TRUE
   select .. from XX_MTV_BALANCES;
ELSE
   select .. from gl_balances;
END IF;
25 дек 08, 11:15    [6618195]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить