ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная задачка: эффективная сортировка с фильтрацией строк
25 сообщений из 31, страница 1 из 2
  1  все
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39793941
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
Заранее извиняюсь если в текстах/коде будут опечатки - из-за политики компании набираю не с рабочего компа. Oracle 12c.

Задача: представим что у нас есть основная, достаточно небольшая таблица ( например 100 тысяч аккаунтов ) и достаточно тормозная вьюшка которая выдает детальную инфу по аккаунту ( предположим, она может выдавать инфу всего лишь по 100 аккаунтам в секунду). Также этот джойн может отфильтровать "плохие" аккаунты у которых что-то не то с деталями ( ну, скажем, в 5% случаев).
Мы хотим их заджойнить, отсортировать по имени аккаунта и взять , скажем, 100 первых строк чтобы показать на сайте - для того чтобы пользователь долго не ждал. Т.к. вьюшка с деталями тормозная, с точки зрения человека напрашивается следующий план работ:

- отсортируем наши аккаунты по имени
- пойдем подряд по отсортированному списку и будем джойнить каждый элемент с тормозной вьюшкой пока не наберем 100 строк.
- так как отфильтровывается всего лишь около 5% строк, то нам нужно будет рассмотреть около 105 строк чтобы осталось 100 нам нужных.
Итого к тормозной вьюшке будет примерно 105 обращений и время работы запроса - чуть больше секунды. Это очень легко написать на pl/sql, но к своему удивлению мне не получается написать запрос, который бы работал с таким планом. Это я туплю или оракл действительно так не может?

Пример на котором может стать понятно чего я хочу.
Создадим табличку test1 и функцию slow_function которая будет имитировать обращение за информацией к тормозной вьюшке:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
create table test1( val number, text varchar2(100) );

create or replace package test_package
is
 g_counter number := 0;
end;
/


create or replace slow_function ( x number ) return number as
begin
    test_package.g_counter := test_package.g_counter + 1; -- чтобы было видно сколько раз функция вызывалась
    return dbms_random.value;
end;
/


insert into test1 
select level lev, rpad(mod(level,20), 100, 'a') from dual connect by level <= 100;

commit;


Теперь мы хотим получить первые 5 строк отсортированные по text, но при этом удовлетворяющие фильтру slow_function(val) > 0. На pl/sql это легко сделать вызвав slow_function всего лишь 5 раз. Как это сделать на SQL я пока не придумал - может придумает кто-то из вас. Вот такой вариант вызывает эту функцию 100 раз:

Код: plsql
1.
2.
3.
4.
5.
select * from test1
where slow_function(val) > 0
order by text
offset 0 rows
fetch next 5 rows only;
...
Рейтинг: 0 / 0
29.03.2019, 21:20
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39793952
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
ValergradНа pl/sql это легко сделать вызвав slow_function всего лишь 5 раз.давай код.
...
Рейтинг: 0 / 0
29.03.2019, 22:23
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39793970
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
-2-ValergradНа pl/sql это легко сделать вызвав slow_function всего лишь 5 раз.давай код.

Под рукой сейчас нет компилятора, но что-то вроде ( придется создать тип и коллекцию ):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create or replace function get_records return t_test1_type_arr pipelined as
declare
    k number := 0;
begin
    for rec in ( select * from test1 order by text )
    loop
         if ( slow_function(rec.val) > 0 ) then
              pipe row( t_test1_type(rec.val, rec.text) );
              k := k + 1;
              if ( k > 5) then
                    exit;
              end if;
         end if;
    end loop;
end;
/
...
Рейтинг: 0 / 0
29.03.2019, 23:44
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39793971
Фотография кит северных морей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
кит северных морей
Участник
Valergrad-2-пропущено...
давай код.

Под рукой сейчас нет компилятора, но что-то вроде ( придется создать тип и коллекцию ):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create or replace function get_records return t_test1_type_arr pipelined as
declare
    k number := 0;
begin
    for rec in ( select * from test1 order by text )
    loop
         if ( slow_function(rec.val) > 0 ) then
              pipe row( t_test1_type(rec.val, rec.text) );
              k := k + 1;
              if ( k > 5) then
                    exit;
              end if;
         end if;
    end loop;
end;
/

вы считаете, что здесь вы вызываете функцию только пять раз?
...
Рейтинг: 0 / 0
29.03.2019, 23:49
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39793977
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Valergradчто-то вроде Поздравляю с изобретением stop key.
...
Рейтинг: 0 / 0
30.03.2019, 00:59
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39793982
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Valergradмне не получается написать запрос, который бы работал с таким планом.покажи как пробовал? я ничего сложного в этом не вижу
...
Рейтинг: 0 / 0
30.03.2019, 01:35
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39793984
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
достаточно rownum или opt_param('_optimizer_filter_pushdown' 'false') чтобы не пропихивало предикат:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ * 
from (
   select/*+ no_merge */ t.*, rownum
   from test1 t
   order by text
   ) v
where slow_function(val) > 0
order by text
offset 0 rows
fetch next 5 rows only;


 
Код: 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.
Plan hash value: 2929658712

--------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |   100 | 44300 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                  |       |   100 | 44300 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|       |   100 | 22800 |     4  (25)| 00:00:01 |
|*  3 |    VIEW                |       |   100 | 22800 |     4  (25)| 00:00:01 |
|   4 |     SORT ORDER BY      |       |   100 | 21500 |     4  (25)| 00:00:01 |
|   5 |      COUNT             |       |       |       |            |          |
|   6 |       TABLE ACCESS FULL| TEST1 |   100 | 21500 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / from$_subquery$_003@SEL$3
   2 - SEL$1
   3 - SEL$2 / V@SEL$1
   4 - SEL$2
   6 - SEL$2 / T@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$2" "T"@"SEL$2")
      NO_ACCESS(@"SEL$1" "V"@"SEL$1")
      NO_ACCESS(@"SEL$3" "from$_subquery$_003"@"SEL$3")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      DB_VERSION('18.1.0')
      OPTIMIZER_FEATURES_ENABLE('18.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE  WHEN
              (0>=0) THEN 0 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownu
              mber">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "V"."TEXT")<=CASE  WHEN
              (0>=0) THEN 0 ELSE 0 END +5)
   3 - filter("SLOW_FUNCTION"("VAL")>0)


вообще вместо fetch first+offset и кучу разных выполнений лучше просто фетчить из одного курсора по сколько нужно
...
Рейтинг: 0 / 0
30.03.2019, 01:58
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794056
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
xtender,

Ну тут скорее всего недомыслие примера ибо шанс dbms_random.value вернет 0 мало. Ну и функция в WHERE абсолютно независимая
от выбираeмых данных тоже не real life...

SY.
...
Рейтинг: 0 / 0
30.03.2019, 14:40
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794076
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
Как обычно - пришел xtender и решил проблему :) Спасибо, я думаю это именно то, что нужно!


вообще вместо fetch first+offset и кучу разных выполнений лучше просто фетчить из одного курсора по сколько нужно

Это верно, но у этого способа свои проблемы в бекэнде возникают ( который hibernate и тому подобная ерунда) . Я им предложил поначалу, но бэкендщики привели свои возражения. Вот считали, мы, скажем, 50 строк из курсора - и что теперь? Держать курсор открытым? Этак у нас весь коннекшен пул переполнится. Непонятно когда его закрывать - пользователь может очень долго изучать первую страницу выдачи.

Я им тогда предложил быстро выдать пользователю 100 строк, остальные дочитывать асинхронно на случай если понадобятся, и как дочитали все - закрыть курсор. Но это не только сильно усложняет код ( нужно писать асинхронность, кэширование и прочее ), но и ведет к непредсказуемому потреблению памяти.
Подход же с использованием offset - fetch next выглядит ужасно, но два соображения показывают что, возможно, все не так плохо

1) В 9 случаях из 10 пользователь не пойдет дальше первой страницы
2) Даже если пойдет, при втором и последующих выполнениях запроса, эти блоки уже будут в буффер кэше, так что проскакивать мимо них будет быстро.
...
Рейтинг: 0 / 0
30.03.2019, 16:36
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794077
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
SYxtender,

Ну тут скорее всего недомыслие примера ибо шанс dbms_random.value вернет 0 мало. Ну и функция в WHERE абсолютно независимая
от выбираeмых данных тоже не real life...

SY.

Думаете это меняет ситуацию по сравнению с исходной задачей? Ну, я проверю в понедельник - попробую подход с rownum использовать, мне ведь как раз это и нужно: чтобы предикаты не пушились вниз, а считались потихоньку, строчка за строчкой.
...
Рейтинг: 0 / 0
30.03.2019, 16:41
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794136
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
ValergradДумаете это меняет ситуацию по сравнению с исходной задачей? Ну, я проверю в понедельник - попробую подход с rownum использовать, мне ведь как раз это и нужно: чтобы предикаты не пушились вниз, а считались потихоньку, строчка за строчкой.

Есть N строк K из которых удолетворяют какому-то условию. Из них нужны первые M. Невозможно оптимизировать сколько раз будет проверяться условие eсли это все что извeстно. Оптимизировать можно только stop-key, т.е. выход по достижении M a не сколько раз будет проверяться условие. Так что постановка задачи "вызвав slow_function всего лишь 5 раз" неправильна.

SY.
...
Рейтинг: 0 / 0
30.03.2019, 20:25
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794137
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
SYValergradДумаете это меняет ситуацию по сравнению с исходной задачей? Ну, я проверю в понедельник - попробую подход с rownum использовать, мне ведь как раз это и нужно: чтобы предикаты не пушились вниз, а считались потихоньку, строчка за строчкой.

Есть N строк K из которых удолетворяют какому-то условию. Из них нужны первые M. Невозможно оптимизировать сколько раз будет проверяться условие eсли это все что извeстно. Оптимизировать можно только stop-key, т.е. выход по достижении M a не сколько раз будет проверяться условие. Так что постановка задачи "вызвав slow_function всего лишь 5 раз" неправильна.

SY.

Ну это вы уже придираетесь слегка. Нужно вызвать "slow_function минимально возможное количество раз ( т.к. тормозит именно она)" вам нравится больше?
...
Рейтинг: 0 / 0
30.03.2019, 20:33
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794146
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
ValergradНу это вы уже придираетесь слегка. Нужно вызвать "slow_function минимально возможное количество раз ( т.к. тормозит именно она)" вам нравится больше?

Bсе что мы можем оптимизировать это выход пoсле 5 TRUE результатов условия а не общее число вызовов функции.

Чтобы вызвать "slow_function минимально возможное количество раз надо создать FBI. Но только если функция determinustic (твоя -нет).

SY.
...
Рейтинг: 0 / 0
30.03.2019, 21:42
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794148
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
SYValergradНу это вы уже придираетесь слегка. Нужно вызвать "slow_function минимально возможное количество раз ( т.к. тормозит именно она)" вам нравится больше?

Bсе что мы можем оптимизировать это выход пoсле 5 TRUE результатов условия а не общее число вызовов функции.

Чтобы вызвать "slow_function минимально возможное количество раз надо создать FBI. Но только если функция determinustic (твоя -нет).

SY.

Извините, ну судя по тому что вы пишете - вы не прочитали исходный пост. Поэтому предлагаю не продолжать этот разговор, тем более что xtender уже ответил что нужно.
...
Рейтинг: 0 / 0
30.03.2019, 22:06
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794238
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
ValergradИзвините, ну судя по тому что вы пишете - вы не прочитали исходный пост. Поэтому предлагаю не продолжать этот разговор, тем более что xtender уже ответил что нужно.

Код: 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.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
SQL> set serveroutput on
SQL> create or replace function slow_function ( x number ) return number as
  2  begin
  3      test_package.g_counter := test_package.g_counter + 1;
  4      dbms_output.put_line(test_package.g_counter);
  5      return dbms_random.value;
  6  end;
  7  /

Function created.

SQL> exec test_package.g_counter := 0;

PL/SQL procedure successfully completed.

SQL> select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ rn
  2  from (
  3     select/*+ no_merge */ t.*, rownum rn
  4     from test1 t
  5     order by text
  6     ) v
  7  where slow_function(val) > 0
  8  order by text
  9  offset 0 rows
 10  fetch next 5 rows only
 11  /

        RN
----------
        40
        80
        20
        60
       100

1
2
3
4
5
6
SQL> create or replace function slow_function ( x number ) return number as
  2  begin
  3      test_package.g_counter := test_package.g_counter + 1;
  4      dbms_output.put_line(test_package.g_counter);
  5      return dbms_random.value(-1,1);
  6  end;
  7  /

Function created.

SQL> exec test_package.g_counter := 0;

PL/SQL procedure successfully completed.

SQL> select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ rn 
  2  from (
  3     select/*+ no_merge */ t.*, rownum rn
  4     from test1 t
  5     order by text
  6     ) v
  7  where slow_function(val) > 0
  8  order by text
  9  offset 0 rows
 10  fetch next 5 rows only
 11  /

        RN
----------
        40
        20
        60
       100
        90

1
2
3
4
5
6
7
8
9
SQL> create or replace function slow_function ( x number ) return number as
  2  begin
  3      test_package.g_counter := test_package.g_counter + 1;
  4      dbms_output.put_line(test_package.g_counter);
  5      return dbms_random.value(-10,1);
  6  end;
  7  /

Function created.

SQL> exec test_package.g_counter := 0;

PL/SQL procedure successfully completed.

SQL> select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ rn 
  2  from (
  3     select/*+ no_merge */ t.*, rownum rn
  4     from test1 t
  5     order by text
  6     ) v
  7  where slow_function(val) > 0
  8  order by text
  9  offset 0 rows
 10  fetch next 5 rows only
 11  /

        RN
----------
        10
        92
        93
        76
        99

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> create or replace function slow_function ( x number ) return number as
  2  begin
  3      test_package.g_counter := test_package.g_counter + 1;
  4      dbms_output.put_line(test_package.g_counter);
  5      return dbms_random.value(-10,-1);
  6  end;
  7  /

Function created.

SQL> exec test_package.g_counter := 0;

PL/SQL procedure successfully completed.

SQL> select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ rn 
  2  from (
  3     select/*+ no_merge */ t.*, rownum rn
  4     from test1 t
  5     order by text
  6     ) v
  7  where slow_function(val) > 0
  8  order by text
  9  offset 0 rows
 10  fetch next 5 rows only
 11  /

no rows selected

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
SQL>



Так-что вот тебе воскресная задача: в таблице "плохие" аккаунты у которых что-то не то с деталями ( ну, скажем, в 5% случаев). Какова вероятность прочтения "хороших" аккаунтов первыми?
Вторая задача: RTFM диапазон значений DBMS_RANDOM.VALUE и посчитай % "плохих" (DBMS_RANDOM.VALUE = 0) аккаунтов и "хороших" (DBMS_RANDOM.VALUE > 0) аккаунтов а затем сравни с "ну, скажем, в 5% случаев".

SY,
...
Рейтинг: 0 / 0
31.03.2019, 13:55
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794319
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
SY, я прекрасно знаю какой диапазон у dbms_random.value. Я написал там dbms_random.value не потому что имел ввиду какую-то конкретную функцию - с тем же успехом я мог написать там bla-bla-bla. Я не стал подгонять функцию под ту, которая дает true в 95% случаев - потому что это даже не особо важно. Если функция slow_function действительно slow, то это будет оптимальный способ доступа в любом случае, независимо от того какой процент true выдает наш фильтр - 1%, 10%, 95% или 100%.
Так что смысл придираться к этому? Вопрос был в том, как вызвать некую функцию/операцию минимальное число раз. Даже более того - вопрос был в том, как сделать чтобы оракл не протаскивал предикат, хоть я и не стал формулировать его в таком виде, чтобы не сужать поле поиска.

Вы же почему-то уперлись в несущественную деталь и продолжаете почему-то на нее напирать как будто она что-то меняет. Я знаю что вы очень умный парень, неоднократно здесь это демонстрировали. Но в данном случае я ваш поинт никак не могу понять. Либо у вас есть действительно какой-то поинт, существенно меняющий ситуацию, который вы хотите объяснить - но пока что весьма неудачно, либо вы просто пытаетесь поднять себе самооценку за чужой счет придираясь к несущественным деталям.
...
Рейтинг: 0 / 0
31.03.2019, 20:58
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794652
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
эх, добрался до компа чтобы убедиться что на исходном примере - с кучей таблиц и вьюшек не сработало.
В плане вместо WINDOW NOSORT STOPKEY используется WINDOW SORT PUSHED RANK.
Судя по всему, дело в том, что сортировать мне надо на самом деле не по столбцу, а по некой функции coalesce от нескольких столбцов. Я не понимаю почему, но легко убедиться что добавление функции ломает всю оптимизацию и функция снова вызывается 100 раз:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ * 
from (
   select/*+ no_merge */ t.val, upper(t.text) as text, rownum
   from test1 t
   order by text
   ) v
where slow_function(val) > 0
order by text
offset 0 rows
fetch next 5 rows only;
...
Рейтинг: 0 / 0
01.04.2019, 15:40
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794665
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
IMHO & AFAIK

При Nested Loop и прекрашении фетча в нужном мести, никакие "новомодные" stop key даже и не нужну )))

Не знаю как современный Oracle, а Oracle 8.1.5 вполне мог сортировку заменять доступом по индексу (при указании хинта INDEX_ASC, INDEX_DESC)

Если можно прибить гвоздями LEADING "небольшую" таблицу + все остальное через NL и колонки/функцию по которым сортируется в индехс, то IMHO проблемы быть не должно

Если же сортируется по данным из нескольких таблиц, то разумеется, невозможно отсортировать данные которых нет, т.ч. никакая оптимизация FIRST_ROWS становится не возможна в принципе. Сначала нужно получить данные, только потом мы можем их отсортировать. Ну или данные изначально нужны отсортированными (индекс)

p.s.
возможно написал чущь, просьба сильно не ругаться.
p.p.s.
если индекс сделать не возможно, "новые" Oracle по нему отказываются сортировать (не binary правила сортировки), то наверное можно попытаться подзапрос "небольшая таблица" материализовать (или with или rownum AFAIK).
...
Рейтинг: 0 / 0
01.04.2019, 15:59
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794671
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
P.P.S.
Не очень понятно, насколько плохо решение через pipelined
Оно конечно выглядит крайне грубо и "прибито гвоздями". Но лучше прибить гвоздями, чем при upgrade на следующую версию Oracle, где оптимизатор с каждой версией все "умнее и умнее", хинты и планы уедут и ваши коллеги из будущего ))) будут вспоминать вас кучей добрых и ласковых слов.
Т.ч. мне кажется, нужно еще оценить риски решения с точки зрения надежности/будушего сопровождения.

IMHO & AFAIK
...
Рейтинг: 0 / 0
01.04.2019, 16:05
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794682
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ val,upper(text) upper_text,rn
from (
   select/*+ no_merge */ t.val, text, rownum rn
   from test1 t
   order by upper(text)
   ) v
where slow_function(val) > 0
order by upper(text)
offset 0 rows
fetch next 5 rows only
/



SY.
...
Рейтинг: 0 / 0
01.04.2019, 16:16
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794687
Valergrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Valergrad
Участник
Leonid KudryavtsevP.P.S.
Не очень понятно, насколько плохо решение через pipelined
Оно конечно выглядит крайне грубо и "прибито гвоздями". Но лучше прибить гвоздями, чем при upgrade на следующую версию Oracle, где оптимизатор с каждой версией все "умнее и умнее", хинты и планы уедут и ваши коллеги из будущего ))) будут вспоминать вас кучей добрых и ласковых слов.
Т.ч. мне кажется, нужно еще оценить риски решения с точки зрения надежности/будушего сопровождения.

IMHO & AFAIK

В бэкенд к сожалению нужно передать курсор, а не коллекцию - фреймворк работает именно так.
Так что через pl/sql это выглядит как создание специального типа для результатов этого запроса, создание коллекции для него, сохранение результата в некую переменную коллекции и считывание потом этой переменной курсором. Ужасно уродливо и большой оверхед. Хотя если решение не найдется, возможно придется так и сделать.
...
Рейтинг: 0 / 0
01.04.2019, 16:23
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794695
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
ValergradВ бэкенд к сожалению нужно передать курсор, а не коллекцию....

Проблемы не понял, особенно если мы говорим о pipelined ф-циях

SELECT * FROM TABLE( pipelined_function(param1, param2, param3...) )

Тут скорее проблема, что если бэк/фронт сам SELECT'ы конструирует и без WHERE жить не может ((( А тут параметры нужно в FROM clause запихать, а не в WHERE clause

IMHO & AFAIK
...
Рейтинг: 0 / 0
01.04.2019, 16:30
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794700
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
P....s.
В общем-то, pipelined ф-ция это просто способ "прибить гвозьдями" метод доступа Nested Loops. Если Вы добъетесь в плане банального Nested Loops, то и SELECT должен выдать FIRST_ROWS не хуже, чем самопал через pipelined ф-цию.
Но hint'овать "новые" Oracle IMHO & AFAIK - все больше и больше "жизнь это боль" ( C ) Eve-online
С каждой новой версией Oracle оптимизатор все "умнее и умнее" и hint'ами его так просто не обманешь ))). "Oracle умный, он сам знает как лучше" ( С ) админ
/тут должен быть смайлик "смех сквозь слезы"/
...
Рейтинг: 0 / 0
01.04.2019, 16:38
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794769
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
Valergrad....Это я туплю или оракл действительно так не может?

может, лично у меня Oracle EE 11.2.0.3.0 так и сделал

 

Код: sql
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.
drop table test1;
drop table test2;

create table test1( val number primary key, text varchar2(100) );
create table test2( val number, extra varchar2(4000) );

create index test1_sort on test1( NLSSORT(text), val ); 

create index test2_val on test2( val );

create or replace package test_package
is
 g_counter number := 0;
end;
/

create or replace function slow_function ( x number ) return number as
begin
    test_package.g_counter := test_package.g_counter + 1; -- чтобы было видно сколько раз функция вызывалась
    return sqrt(x);  -- You should NOT use dbms_random.value !!!
end;
/

insert into test1 
select level lev, rpad(mod(level,20), 100, 'a') from dual connect by level <= 10000;

insert into test2
select level lev, 'extra '||rpad(mod(level,20), 100, 'a') from dual connect by level <= 10000;

commit; 

create or replace view slow_view as
select
  val as val,
  extra as extra,
  slow_function( val ) as extra_func 
  from test2 t2;

begin
  test_package.g_counter := 0;
end;
/

select 
test1.val, test1.text, slow_view.extra as extra, slow_view.extra_func from test1, slow_view 
where slow_view.val=test1.val 
order by NLSSORT(test1.text)  -- just fetch as many rows as you need
;

begin
  dbms_output.put_line( 'w/out hints='||test_package.g_counter );
  test_package.g_counter := 0;
end;
/

select 
/*+ LEADING(test1) INDEX_ASC( test1 test1_sort) USE_NL( T2@SEL$2 ) INDEX( T2@SEL$2 test2_val ) */
test1.val, test1.text, slow_view.extra as extra, slow_view.extra_func from test1, slow_view 
where slow_view.val=test1.val 
order by NLSSORT(test1.text)  -- just fetch as many rows as you need
;

begin
  dbms_output.put_line( 'with hints='||test_package.g_counter );
  test_package.g_counter := 0;
end;
/



вывод у меня на компьютере, запускал через PL/SQL Developer, фетчилось только 100 записей

 

[fix]
w/out hints=100
with hints=100
[/fix]


To SY и прочие знатоки:
а почему в плане запроса Oracle зачем-то полез в таблицу table1 ? Я надеялся, что он ограничится только индексом (там все необходимые поля).
...
Рейтинг: 0 / 0
01.04.2019, 18:23
| Ответить | Цитировать | Написать  
Пятничная задачка: эффективная сортировка с фильтрацией строк
    #39794784
Leonid Kudryavtsev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Leonid Kudryavtsev
Участник
 

Автор еще хотел условие на slow_function + пагинацию, но вроде то же, без проблем. Надеюсь не ошибся. Сложно делать пример "сферический конь в вакууме"

Код: 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.
begin
  test_package.g_counter := 0;
end;
/

-- There may be some errors in table names ((( I can not test hints.
select
/*+ LEADING(test1@sel$3) INDEX_ASC( test1@sel$3 test1_sort) USE_NL( T2@SEL$4 ) INDEX( T2@SEL$4 test2_val ) */
*
from (
select 
  row_number() over (order by NLSSORT(t1.text)) rn,
  t1.val, t1.text, slow_view.extra as extra, slow_view.extra_func 
  from
    (select 
    val, text from test1) t1,
    slow_view 
  where
    slow_view.val=t1.val
    and slow_view.extra_func > 10
)  tt
  where rn between 10 and 19
  order by
    NLSSORT(text)
;

begin
  dbms_output.put_line( 'with extra_func and pagination='||test_package.g_counter );
  test_package.g_counter := 0;
end;
/



Код: sql
1.
with extra_func and pagination=44



...
Рейтинг: 0 / 0
01.04.2019, 18:49
| Ответить | Цитировать | Написать  
25 сообщений из 31, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная задачка: эффективная сортировка с фильтрацией строк
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&startmsg=39793941&tid=1882623]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 31ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 18ms
total:  144ms
созд. / загр.: 144ms
x
x
Закрыть


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