Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Немного магии оптимизатора Оракла / 25 сообщений из 27, страница 1 из 2
  1  все
19.09.2019, 12:22
    #39863863
Misha111
Misha111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Оптимизатор настолько оптимален, что иногда решает просто не возвращать строки - ведь так быстрее)
Собственно сами запросы и результаты (первый просто показывает что dbms_random.value работает как задумано):

Код: 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.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as sever11
 
SQL> 
select *
  from (
        select tst, count(*)
          from (
                select rownum rn, trunc(dbms_random.value(0,100)) tst
                  from dual connect by level<=1e4
               )
         group by tst
       )
 where rownum<=10
 order by 1;
 
       TST   COUNT(*)
---------- ----------
         0        100
         1         98
         2        107
         3         86
         4        101
         5         99
         6         94
         7        103
         8        107
         9        118
 
10 rows selected
 
Executed in 0,156 seconds
select row_number() over (order by rn) ord, rn
  from (
        select rn, tst
          from (
                select rn, trunc(dbms_random.value(0,100))  tst
                  from (select rownum rn from dual connect by level<=1e4)
               )  a
         where tst=0
       )
where rownum<=10;
 
       ORD         RN
---------- ----------
         1         47
         2         81
         3         93
         4        157
         5        270
         6        307
         7        362
         8        414
         9        566
        10        742
 
10 rows selected
 
Executed in 0,047 seconds
select rn from
(
select row_number() over (order by rn) ord, rn
  from (
        select rn, tst
          from (
                select rn, trunc(dbms_random.value(0,100))  tst
                  from (select rownum rn from dual connect by level<=1e4)
               )  a
         where tst=0
       )
)
where rownum<=10;
 
        RN
----------
 
Executed in 0,015 seconds
 
SQL>



Проверено и воспроизводится на:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Понятно, что можно добавить хинт NO_MERGE или прибить гвоздями
Код: plsql
1.
... rownum rn ... ) where rn>0


Морали у басни не будет
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 12:32
    #39863873
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Misha111Проверено и воспроизводится на:
Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0+ Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 13:32
    #39863933
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Misha111Оптимизатор настолько оптимален, что иногда решает просто не возвращать строки - ведь так быстрее)
Ну наступили на багу оптимизатора.
Не Вы первый, не Вы последний.
По wrong results много смешных багов было.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 13:47
    #39863954
booby
booby
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
andrey_anonymous,

до тех пор, пока no_merge и "прибить гвоздями" работает, имхо, это спорный вопрос -
баг ли это вообще.
Попытка его "исправить", скорее всего, приведет к худшим последствиям.
Мне кажется, дешевле считать в списке особенностей, необходимых к учету
при работе с псевдостолбцами.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 14:42
    #39863993
Misha111
Misha111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
andrey_anonymousПо wrong results много смешных багов было.
и будет)

впервые вижу баг wrong results без джоинов и крокодилов, да еще и на dual`е
да и не часто их тут выкладывают...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 15:21
    #39864028
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
boobyбаг ли это вообще.+1
Misha111да еще и на dual`еМногим непонимание dbms_random-а кажется багом. Поначалу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 15:27
    #39864034
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
booby до тех пор, пока no_merge и "прибить гвоздями" работает, имхо, это спорный вопрос -
баг ли это вообще.


Да нет - это баг. Нельзя так глубоко проталкивать предикат с ROWNUM внутрь если там есть WHERE clause. Оптимизатор должен понимать что

where tst=0


rownum <= 10

и

rownum <= 10

а затем

where tst=0

совершенно неэквивалентны.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 16:07
    #39864063
Misha111
Misha111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
SY,

там rownum <= 10 для красоты тесткейса прикручено. без него работает аналогично

вот планы запросов без rownum <= 10
Код: 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.
-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |     1 |    26 |     3  (34)| 00:00:01 |
|   1 |  WINDOW SORT                     |      |     1 |    26 |     3  (34)| 00:00:01 |
|*  2 |   VIEW                           |      |     1 |    26 |     2   (0)| 00:00:01 |
|   3 |    VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |     COUNT                        |      |       |       |            |          |
|*  5 |      CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   6 |       FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TST"=0)
   5 - filter(LEVEL<=1e4)

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  FILTER                         |      |       |       |            |          |
|   2 |   VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    COUNT                        |      |       |       |            |          |
|*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TRUNC("DBMS_RANDOM"."VALUE"(0,100))=0)
   4 - filter(LEVEL<=1e4)



похоже во втором случае оракл считает что dbms_random.value возвращает одинаковые значения для всех строк и вычисляет random только один раз.
это подтверждается если изменить на dbms_random.value(0,5) - примерно каждый пятый запуск будет возвращать результат. вот только подтвердить не могу - при добавления поля tst в верхний селект магия пропадает...

Elic, а в чем непонимание dbms_random-а? только не нужно посылать в RTFM и STF. на словах можно?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 16:22
    #39864069
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Misha111на словахКто изощрённо использует в запросах недетерминированные функции - тот ССЗБ.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 16:52
    #39864082
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Elicboobyбаг ли это вообще.+1
Misha111да еще и на dual`еМногим непонимание dbms_random-а кажется багом. Поначалу.Это вполне конкретный баг связанный с query transformations.
Итоговый запрос не эквивалентен изначальному.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select rn from
(
select row_number() over (order by rn) ord, rn
  from (
        select rn, tst
          from (
                select rn, trunc(dbms_random.value(0,100))  tst
                  from (select rownum rn from dual connect by level<=1e4)
               )  a
         where tst=0
       )
)
where rownum<=10;

Некорректно преобразуется в
Код: plsql
1.
2.
3.
4.
SELECT "from$_subquery$_004"."RN" "RN"
  FROM (SELECT ROWNUM "RN" FROM "SYS"."DUAL" "DUAL" CONNECT BY LEVEL <= 1e4) "from$_subquery$_004"
 WHERE ROWNUM <= 10
   AND TRUNC("SYS"."DBMS_RANDOM"."VALUE"(0, 100)) = 0


В принципе, итоговый текст запроса в трассе 10043 тоже может не оражать действительность, но вот предикаты в плане точно показывают проблему.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 16:56
    #39864086
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
SYbooby до тех пор, пока no_merge и "прибить гвоздями" работает, имхо, это спорный вопрос -
баг ли это вообще.


Да нет - это баг. Нельзя так глубоко проталкивать предикат с ROWNUM внутрь если там есть WHERE clause. Оптимизатор должен понимать что

where tst=0


rownum <= 10

и

rownum <= 10

а затем

where tst=0

совершенно неэквивалентны.

SY.
оракля что-то дополнительно (бажно) знает об dbms_random.value

если заменить на пользоательскую ф-цию то записи возвращаются,
так-же если заменить константы, напр dbms_random.value(rn-rn,200)

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 16:59
    #39864089
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Misha111впервые вижу баг wrong results без джоинов и крокодилов, да еще и на dual`еБагов с трансформациями запросов и недетерминированными функциями вагон и маленькая тележка.
Если сделать в твоем запроса генератор от 0 до 99 через mod + ora_hash + sys_guid вместо dbms_random.value то воспроизводится ещё проще - без всякой аналитики.
Код: 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.
SQL> select rn
  2    from (select rn, tst
  3            from (select rn, mod(ora_hash(sys_guid()), 100) tst
  4                    from (select rownum rn from dual connect by level <= 1e4)) a
  5           where tst = 0)
  6   where rownum <= 10;

no rows selected

SQL>
SQL> select rn
  2    from (select rn, tst
  3            from (select rownum rn, mod(ora_hash(sys_guid()), 100) tst
  4                    from dual connect by level <= 1e4) a
  5           where tst = 0)
  6   where rownum <= 10;

        RN
----------
        39
       208
       246
       302
       403
       447
       498
       549
       597
       777

10 rows selected.


Misha111да и не часто их тут выкладывают...Ну вот ещё пример 19464417 .
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 17:05
    #39864095
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
недокопипастил
Планы одинаковые
Код: 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.
SQL> /

        RN
----------
       578
       759
       954
      1150
      1229
      1270
      1580
      1654
      1788
      1830

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3714659054

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                  |      |       |       |            |          |
|*  2 |   VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |    COUNT                        |      |       |       |            |          |
|*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)
   2 - filter(TRUNC("DBMS_RANDOM"."VALUE"("RN"-"RN",200))=0)
   4 - filter(LEVEL<=1e4)

SQL> ed
Wrote file afiedt.buf

  1  select rn from
  2  (
  3  select row_number() over (order by rn) ord, rn
  4    from (
  5          select rn, tst
  6            from (
  7                  select rn, trunc(dbms_random.value(0,200))  tst
  8                    from (select rownum rn from dual connect by level<=1e4)
  9                 )  a
 10           where tst=0
 11         )
 12  )
 13* where rownum<=10
SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1995716690

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                   |      |       |       |            |          |
|*  2 |   FILTER                         |      |       |       |            |          |
|   3 |    VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |     COUNT                        |      |       |       |            |          |
|*  5 |      CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   6 |       FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)
   2 - filter(TRUNC("DBMS_RANDOM"."VALUE"(0,200))=0)
   5 - filter(LEVEL<=1e4)



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 17:38
    #39864122
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Staxоракля что-то дополнительно (бажно) знает об dbms_random.value

если заменить на пользоательскую ф-цию то записи возвращаются,
так-же если заменить константы, напр dbms_random.value(rn-rn,200)Дело не в dbms_random.value (или как было показано выше mod ora_hash sys_guid), а в недетминированной природе результата.
Если предположить, что выражение детерминировано, то трансформации в этом случае эквивалентны.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 17:51
    #39864130
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Кобанчег,

імхо
если ЯВНО не обьявлено deterministic то ф-цию считать недетминированной

поетому такое поведение считаю багом (а не изощренностью)

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 17:54
    #39864137
Misha111
Misha111
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
КобанчегНу вот ещё пример 19464417 .
да. там NLK приводит пример с asktoma который еще красивее:
Код: plsql
1.
2.
3.
select * 
  from (select rownum rn from dual connect by level <= 1e2) 
 where dbms_random.value(0,1) <= 0.10;



запрос вместо ожидаемых 10% данных возвращает все 100 строк (редко), либо ничего.

согласен с
Staxоракля что-то дополнительно (бажно) знает об dbms_random.value
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 18:11
    #39864146
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Staxпоетому такое поведение считаю багом (а не изощренностью)Одни считают, а другие не ходят по тонкому льду.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2019, 18:14
    #39864148
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Misha111ожидаемыхКто-то ожидает, кто-то (и Том Кайт тоже) нет.

Можно принудить функцию в where вызываться на каждую строку как-то так
Код: plsql
1.
2.
3.
select * 
  from (select rownum rn from dual connect by level <= 1e2) 
 where dbms_random.value(0,sign(rownum)) <= 0.10;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2019, 02:26
    #39864316
booby
booby
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
SY... Нельзя так глубоко проталкивать предикат с ROWNUM внутрь если там есть WHERE clause. Оптимизатор должен понимать что ...

Всё-таки - не уверен.
Мне продолжает казаться, что правда где-то между тем, на что намёкивает, но не рассказывает Elic и убежденностью Stax в своей правоте.
Вот эта убежденность и является предметом сомнения.

Вот балбесья версия:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select rn  -- уровень 1
from
(
select row_number() over (order by rn) ord, rn -- уровень 2
  from (
        select rn, tst   -- уровень 3
          from (
                select rn, trunc(dbms_random.value(0,100))  tst -- уровень 4
                  from (select rownum rn -- уровень 5
                           from dual connect by level<=1e4)
               )  a
         where tst=0
       )
)
where rownum<=10;



1) В списке Select верхнего (назовем первым сверху) уровня присутствует только rn, определенный на самом глубоком (5-ом) уровне запроса.
Формально, остальные поля, по возможности, не должны вычисляться вообще.
ord, заявленный на втором уровне, в результате не требуется, поэтому может быть целиком выкинут из списка вычисления второго уровня, после чего там остаётся только rn,
и становится не видно никаких разумных причин, по которым второй уровень не должен быть целиком выкинут (смержен с третьим).
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select rn  -- уровень 1
from
(
--select /* row_number() over (order by rn) ord , */ rn -- уровень 2 
--                                                                      становится ненужным после отбрасывания ord
--  from 
--      (
        select rn, tst   -- уровень 3
          from (
                select rn, trunc(dbms_random.value(0,100))  tst -- уровень 4
                  from (select rownum rn -- уровень 5
                           from dual connect by level<=1e4)
               )  a
         where tst=0
--       )
) where rownum<=10;


2) Присутствующий на уровне 3 tst не используется на уровнях выше и не должен был бы совсем вычисляться в списке Select 3-го уровня, если бы не потребность использовать его в фильтре 3-го уровня, где он единственный участник. И, так как tst - значение функции,
не используемой на вышележащих уровнях, и вычисляемой на 4м уровне,
то следующий оптимизирующий ход практически очевиден - надо:
а) выкинуть третий уровень целиком (смержить с четвертым), и
б) вычисляющую tst функцию целиком переместить из списка Select 4-го уровня
в фильтр подзапроса четвертого уровня.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select rn  -- уровень 1
from
(
-- ... -- это уже выбросили, мержим 3й и 4й
--        select rn, tst   -- уровень 3
       -- tst - это неиспользуемый выше trunc(dbms_random.value(0,100)), уносим его на 4й уровень
--          from (
                -- 4й уровень
            select rn --, trunc(dbms_random.value(0,100))  tst -- в списке Select поле tst уже не нужно
            from (select rownum rn -- уровень 5
                     from dual connect by level<=1e4)
            Where 
                  trunc(dbms_random.value(0,100)) = 0 -- это приехавший с 3го уровня tst=0
               )  a
--         where tst=0

) where rownum<=10;




3) теперь запрос становится неотличим от
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select rn  -- уровень 1
from
(
-- ... -- уже выбросили, 2й, 3й и 4й
                   select rownum rn -- уровень 5
                     from dual 
                     where 
                         trunc(dbms_random.value(0,100)) = 0 -- это приехавший с 3го уровня tst=0
                     connect by level<=1e4
                    
) where rownum<=10;


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

Пусть даже разумно.
Но Том Кайт призывал не делать на сей счет никаких предположений...

И результат-то запрос все равно вернет, даже при единственном входе в dbms_random.
Просто произойдет это в среднем в одном случае из ста запусков, в соответствии с фильтром.

А чтобы предотвратить смерживание, кроме всех прочих вариантов,
в этом примере достаточно в верхнем список Select воспользоваться значением ord.

Ну, можно еще, в стиле Филиппа Филипповича Преображенского, требовавшего с
Петра Александровича настоящую бумажку,
требовать с Oracle знания об окончательно, фактически недерминированных функциях, обязанных выполняться в фильтре на каждую строку источника,
даже если их вызов не зависит от потока данных и они не используются в списках выбора.

Боюсь только, что угроза отъезда из Москвы в Сочи, в данном случае, никого не испугает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2019, 03:56
    #39864323
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
в целом Elic прав: ElicКто изощрённо использует в запросах недетерминированные функции - тот ССЗБ.

Вижу, тут у многих недопонимание, чем отличается операция FILTER от FILTER PREDICATES в других операциях: операция FILTER сначала пытается вычислить все предикаты, которые не зависят от дочерних row sources, и если они заведомо не удовлетворяют, то она даже не будет запускать дочерние операции.
вот простой пример с двумя разными фильтрами и фильтр предикатом в дочернем row-source
 
Код: 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.
SQL> ;
  1  explain plan for
  2  select *
  3    from (select mod(rownum,2) rn from dual connect by level <= 1e2)
  4   where 1=0 -- 2-й FILTER
  5     and dbms_random.value(rn,rn*2) >=1 -- FILTER PREDICATE во VIEW
  6     and dbms_random.value(0,1) <= 0.5 -- 2-й FILTER
  7     and 1=:x --  2-й FILTER
  8     and dbms_random.value(0,rownum) <= 0.5 -- 1-й FILTER
  9*    and dbms_random.value(0,rownum+1) <= 0.5 -- 1-й FILTER
SQL> /

Explained.


Plan hash value: 740301946

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |     1 |    13 |     0   (0)|          |
|   1 |  COUNT                            |      |       |       |            |          |
|*  2 |   FILTER                          |      |       |       |            |          |
|*  3 |    FILTER                         |      |       |       |            |          |
|*  4 |     VIEW                          |      |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |      COUNT                        |      |       |       |            |          |
|*  6 |       CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   7 |        FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - filter("DBMS_RANDOM"."VALUE"(0,ROWNUM)<=0.5 AND
              "DBMS_RANDOM"."VALUE"(0,ROWNUM+1)<=0.5)
   3 - filter(NULL IS NOT NULL AND TO_NUMBER(:X)=1 AND
              "DBMS_RANDOM"."VALUE"(0,1)<=0.5)
   4 - filter("DBMS_RANDOM"."VALUE"("RN","RN"*2)>=1)
   6 - filter(LEVEL<=1e2)

NULL IS NOT NULL - переписанный 1=0


Staxесли заменить на пользоательскую ф-цию то записи возвращаются
 
да ну?
Код: 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.
SQL> create or replace function rand(low number,high number) return number as
  2  begin
  3     return dbms_random.value(low,high);
  4  end;
  5  /

Function created.

SQL> select count(*)
  2    from (select rownum rn from dual connect by level <= 5)
  3   where rand(0,1) <= 0.50;

  COUNT(*)
----------
         5

SQL> /

  COUNT(*)
----------
         5

SQL> /

  COUNT(*)
----------
         0

SQL> /

  COUNT(*)
----------
         5

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2019, 08:47
    #39864374
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
xtenderStaxесли заменить на пользоательскую ф-цию то записи возвращаются
 
да ну?
Код: 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.
SQL> create or replace function rand(low number,high number) return number as
  2  begin
  3     return dbms_random.value(low,high);
  4  end;
  5  /

Function created.

SQL> select count(*)
  2    from (select rownum rn from dual connect by level <= 5)
  3   where rand(0,1) <= 0.50;

  COUNT(*)
----------
         5

SQL> /

  COUNT(*)
----------
         5

SQL> /

  COUNT(*)
----------
         0

SQL> /

  COUNT(*)
----------
         5



для такой ф-ции (без dbms_random) можете добится "оптимизации"
Код: 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.
SQL> create or replace function rand(low number,high number) return number as
  2    begin
  3       return sign(low+1)-sign(high+1);
  4    end;
  5  /

Function created.

SQL> select rn from
  2  (
  3  select row_number() over (order by rn) ord, rn
  4    from (
  5          select rn, tst
  6            from (
  7                  select rn, trunc(rand(0,100))  tst
  8                    from (select rownum rn from dual connect by level<=1e4)
  9                 )  a
 10           where tst=0
 11         )
 12  )
 13  where rownum<=10
 14  /

        RN
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select rn from
  2  (
  3  select row_number() over (order by rn) ord, rn
  4    from (
  5          select rn, tst
  6            from (
  7                  select rn, trunc(dbms_random.value(0,200))  tst
  8                    from (select rownum rn from dual connect by level<=1e4)
  9                 )  a
 10           where tst=0
 11         )
 12  )
 13* where rownum<=10
SQL> /

no rows selected

SQL>



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2019, 08:52
    #39864377
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
 
Staxдля такой ф-ции (без dbms_random) можете добится "оптимизации"
Саян, зная косноязычность Станислава, легко можно было понять, что он имел в виду детерминированную функцию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2019, 08:57
    #39864380
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
xtender,

о кажись понял, был неправ оптимизирует (вызывает раз)

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2019, 09:01
    #39864385
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Elic,

я был неправ насчет

Staxоракля что-то дополнительно (бажно) знает об dbms_random.value

если заменить на пользоательскую ф-цию то записи возвращаются,




зы
на неудачной ф-ции попробовал (детермінірованной)

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2019, 09:48
    #39864401
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Немного магии оптимизатора Оракла
Это же получается? Если один и тот же запрос дает разные результаты с и без использования no_query_transformation, то это перестало быть багом? Теперь и non-deterministic функции в запросах использовать нельзя, а иначе - ССЗБ? Бейте меня ногами, но я не готов принять вашу философию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Немного магии оптимизатора Оракла / 25 сообщений из 27, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 181ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1882057]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 62ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 13ms
total:  181ms
x
x
Закрыть


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