Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / проблема с фичей QUERY REWRITE + mv / 13 сообщений из 13, страница 1 из 1
11.01.2017, 19:54
    #39382679
DBAshnik
DBAshnik
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
проблема с фичей QUERY REWRITE + mv
вопрос уже несколько отдалённый от моей изначальной темы поэтому создаю отдельный топик:

решил тут поиграться (на тестовой дб) с 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:56
    #39382685
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
проблема с фичей QUERY REWRITE + mv
DBAshnik,

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

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


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

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

Код: 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, 03:53
    #39382831
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
проблема с фичей QUERY REWRITE + mv
Кроме того, учти:

Код: 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, 16:31
    #39383343
DBAshnik
DBAshnik
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
проблема с фичей QUERY REWRITE + mv
SYDBAshnikглупый Оракл не втыкает, что можно (очевидно же, что без проблем!) взять mv! Или это я глупый и что-то не знаю ещё?


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

SY.

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




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

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

чудеса-то? ))) Похоже да. Кстати потом и без хинта стало всё работать. Баги какие-то явно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / проблема с фичей QUERY REWRITE + mv / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 389ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1886663]:
 0ms
get settings:
 5ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 152ms
get topic data:
 5ms
get forum data:
 2ms
get page messages:
 25ms
get tp. blocked users:
 1ms
others: 186ms
total:  389ms
x
x
Закрыть


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