ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / проблема с фичей QUERY REWRITE + mv
13 сообщений из 13, страница 1 из 1
проблема с фичей QUERY REWRITE + mv
    #39382679
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
вопрос уже несколько отдалённый от моей изначальной темы поэтому создаю отдельный топик:

решил тут поиграться (на тестовой дб) с QUERY REWRITE + mv

создал базовую вьюху:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE MATERIALIZED VIEW DACODB.mv_ViTstMV 
ENABLE QUERY REWRITE
AS
select 
distinct T1.CMPCODE c1,   
      T1.EL1 c2,
      T2.EL6_CODE c3,
      T3.EL1_SNAME c4
from DACODB.OS_BALANCE T1, DACODB.OS_EL6_ELEMENT T2,  DACODB.OS_EL1_ELEMENT T3 
where T1.CMPCODE=T2.EL6_CMPCODE and T1.EL6=T2.EL6_CODE and  T1.CMPCODE=T3.EL1_CMPCODE and T1.EL1=T3.EL1_CODE;




когда делаю запрос 1 в 1 один с базовым, всё летает (и, разумеется, expain plan показывает, что MV юзается!)

Код: sql
1.
2.
3.
4.
5.
6.
7.
select
distinct T1.CMPCODE c1,
T1.EL1 c2,
T2.EL6_CODE c3,
T3.EL1_SNAME c4
from DACODB.OS_BALANCE T1, DACODB.OS_EL6_ELEMENT T2, DACODB.OS_EL1_ELEMENT T3
where T1.CMPCODE=T2.EL6_CMPCODE and T1.EL6=T2.EL6_CODE and T1.CMPCODE=T3.EL1_CMPCODE and T1.EL1=T3.EL1_CODE



но стоит добавить предикат, который всего-то должен только отфильтровывать что-то из базы - как MV уже не доступна (и это видно в explain plan !)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select
distinct T1.CMPCODE c1,
T1.EL1 c2,
T2.EL6_CODE c3,
T3.EL1_SNAME c4
from DACODB.OS_BALANCE T1, DACODB.OS_EL6_ELEMENT T2, DACODB.OS_EL1_ELEMENT T3
where T1.CMPCODE=T2.EL6_CMPCODE and T1.EL6=T2.EL6_CODE and T1.CMPCODE=T3.EL1_CMPCODE and T1.EL1=T3.EL1_CODE
[color=red]and T1.EL3<>'0200'[/color]



даже хинт не помог:

select /*+ REWRITE(mv_ViTstMV )*/


глупый Оракл не втыкает, что можно (очевидно же, что без проблем!) взять mv! Или это я глупый и что-то не знаю ещё?

Заранее спасибо за любую полезную инфу!
...
Рейтинг: 0 / 0
11.01.2017, 19:54
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39382685
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
DBAshnik,

возможно, что как раз из-за cursor_sharing=force...
...
Рейтинг: 0 / 0
11.01.2017, 19:56
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39382711
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
xtender,

запрос с хинтом cursor_sharing_exact не помог!
...
Рейтинг: 0 / 0
11.01.2017, 21:15
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39382749
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
DBAshnikглупый Оракл не втыкает, что можно (очевидно же, что без проблем!) взять mv! Или это я глупый и что-то не знаю ещё?


Ну хоть засомневался - уже прогресс. И из какой шляпы oracle вытянет значения T1.EL3 выбирая из MV?

SY.
...
Рейтинг: 0 / 0
11.01.2017, 22:15
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39382780
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Медитируй над:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
SQL> CREATE MATERIALIZED VIEW test_mv
  2    ENABLE QUERY REWRITE
  3    AS 
  4      select  distinct dname, 
  5                      job
  6        from  emp e,
  7              dept d
  8        where d.deptno = e.deptno
  9  /

Materialized view created.

SQL> explain plan for
  2  select  distinct dname, 
  3                  job
  4    from  emp e,
  5          dept d
  6    where d.deptno = e.deptno
  7  /

Explained.

SQL> set linesize 132
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 1627509066

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     9 |   153 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| TEST_MV |     9 |   153 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

8 rows selected.

SQL> explain plan for
  2  select  distinct dname, 
  3                  job
  4    from  emp e,
  5          dept d
  6    where d.deptno = e.deptno
  7      and job = 'CLERK'
  8  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1806513732

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    34 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE                  |         |     2 |    34 |     4  (25)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| TEST_MV |     2 |    34 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter("TEST_MV"."JOB"='CLERK')

14 rows selected.

SQL> explain plan for
  2  select  distinct dname, 
  3                  job
  4    from  emp e,
  5          dept d
  6    where d.deptno = e.deptno
  7      and sal = 1000
  8  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3189933151

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    28 |     7  (15)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |     1 |    28 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |    28 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |     1 |    15 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - filter("SAL"=1000)

17 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
12.01.2017, 00:12
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39382831
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Кроме того, учти:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
SQL> explain plan for
  2  select  distinct dname, 
  3                  job
  4    from  emp e,
  5          dept d
  6    where d.deptno = e.deptno
  7      and job = 'CLERK'
  8  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1806513732

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    34 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE                  |         |     2 |    34 |     4  (25)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| TEST_MV |     2 |    34 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter("TEST_MV"."JOB"='CLERK')

14 rows selected.

SQL> update dept set dname = dname where 1 = 2 -- даже так и приехали не говоря о банальном stale MV.
  2  /

0 rows updated.

SQL> explain plan for
  2  select  distinct dname, 
  3                  job
  4    from  emp e,
  5          dept d
  6    where d.deptno = e.deptno
  7      and job = 'CLERK'
  8  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3189933151

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    72 |     7  (15)| 00:00:01 |
|   1 |  HASH UNIQUE        |      |     3 |    72 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     3 |    72 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |     3 |    33 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("D"."DEPTNO"="E"."DEPTNO")
   3 - filter("JOB"='CLERK')

17 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
12.01.2017, 03:53
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39383343
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
SYDBAshnikглупый Оракл не втыкает, что можно (очевидно же, что без проблем!) взять mv! Или это я глупый и что-то не знаю ещё?


Ну хоть засомневался - уже прогресс. И из какой шляпы oracle вытянет значения T1.EL3 выбирая из MV?

SY.

спасибо! Теперь мне всё полностью понятно, вроде бы.
...
Рейтинг: 0 / 0
12.01.2017, 16:31
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39383376
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
но что продолжает оставаться непонятным, так это вот что:
cоздаю вью на базовых таблицах, БЕЗ distinct! В твоём примере было бы:

Код: sql
1.
2.
3.
4.
5.
6.
CREATE MATERIALIZED VIEW test_mv
  ENABLE QUERY REWRITE
  AS 
    select dname, job
     from emp e, dept d
   where d.deptno = e.deptno;



запускаю запрос без distinct:

Код: sql
1.
2.
3.
    select dname, job
     from emp e, dept d
   where d.deptno = e.deptno;



- mv юзается!
Тоже самое с distinct!:
Код: sql
1.
select distinct dname, job


- mv НЕ юзается!

снова запускаю запрос без distinct - снова юзается mv! В чём фишка?
...
Рейтинг: 0 / 0
12.01.2017, 16:57
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39383394
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
DBAshnikВ чём фишка?

Верcия?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> CREATE MATERIALIZED VIEW test_mv
  2    ENABLE QUERY REWRITE
  3    AS 
  4      select dname, job
  5       from emp e, dept d
  6     where d.deptno = e.deptno;

Materialized view created.

SQL> explain plan for
  2      select dname, job
  3       from emp e, dept d
  4     where d.deptno = e.deptno;

Explained.

SQL> set linesize 132
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1627509066

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   224 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| TEST_MV |    14 |   224 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

8 rows selected.

SQL> explain plan for
  2  select distinct dname, job
  3       from emp e, dept d
  4     where d.deptno = e.deptno;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 1806513732

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |    11 |   176 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE                  |         |    11 |   176 |     4  (25)| 00:00:01 |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| TEST_MV |    14 |   224 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

9 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
12.01.2017, 17:16
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39383416
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
версия: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Хм.... Странно! :-(
...
Рейтинг: 0 / 0
12.01.2017, 17:36
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39383486
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
ха! А вот если я пишу:
авторselect /*+REWRITE*/ distinct ...
то MV берётся таки! То есть хинт помогает!!! :-)
...
Рейтинг: 0 / 0
12.01.2017, 18:35
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39383510
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
DBAshnikто MV берётся таки! То есть хинт помогает!!! :-)




Они сущеcтвуют!!!

SY.
...
Рейтинг: 0 / 0
12.01.2017, 18:51
| Ответить | Цитировать | Написать  
проблема с фичей QUERY REWRITE + mv
    #39383928
DBAshnik
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DBAshnik
Участник
SY Они сущеcтвуют!!!

чудеса-то? ))) Похоже да. Кстати потом и без хинта стало всё работать. Баги какие-то явно.
...
Рейтинг: 0 / 0
13.01.2017, 11:36
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / проблема с фичей QUERY REWRITE + mv
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39382679&tid=1886663]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 163ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 212ms
total:  462ms
созд. / загр.: 462ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]