ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Немного магии оптимизатора Оракла
25 сообщений из 27, страница 1 из 2
  1  все
Немного магии оптимизатора Оракла
    #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:22
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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, 12:32
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39863933
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Misha111Оптимизатор настолько оптимален, что иногда решает просто не возвращать строки - ведь так быстрее)
Ну наступили на багу оптимизатора.
Не Вы первый, не Вы последний.
По wrong results много смешных багов было.
...
Рейтинг: 0 / 0
19.09.2019, 13:32
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39863954
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
andrey_anonymous,

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

впервые вижу баг wrong results без джоинов и крокодилов, да еще и на dual`е
да и не часто их тут выкладывают...
...
Рейтинг: 0 / 0
19.09.2019, 14:42
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39864028
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
boobyбаг ли это вообще.+1
Misha111да еще и на dual`еМногим непонимание dbms_random-а кажется багом. Поначалу.
...
Рейтинг: 0 / 0
19.09.2019, 15:21
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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, 15:27
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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:07
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39864069
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Misha111на словахКто изощрённо использует в запросах недетерминированные функции - тот ССЗБ.
...
Рейтинг: 0 / 0
19.09.2019, 16:22
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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:52
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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:56
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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, 16:59
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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:05
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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:38
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39864130
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Кобанчег,

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

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

....
stax
...
Рейтинг: 0 / 0
19.09.2019, 17:51
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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, 17:54
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39864146
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Staxпоетому такое поведение считаю багом (а не изощренностью)Одни считают, а другие не ходят по тонкому льду.
...
Рейтинг: 0 / 0
19.09.2019, 18:11
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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
19.09.2019, 18:14
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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, 02:26
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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, 03:56
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #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:47
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39864377
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
 
Staxдля такой ф-ции (без dbms_random) можете добится "оптимизации"
Саян, зная косноязычность Станислава, легко можно было понять, что он имел в виду детерминированную функцию.
...
Рейтинг: 0 / 0
20.09.2019, 08:52
| Ответить | Цитировать | Написать  
Немного магии оптимизатора Оракла
    #39864380
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
xtender,

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

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

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

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

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




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

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


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