Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
Заранее извиняюсь если в текстах/коде будут опечатки - из-за политики компании набираю не с рабочего компа. Oracle 12c.

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

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

Пример на котором может стать понятно чего я хочу.
Создадим табличку test1 и функцию slow_function которая будет имитировать обращение за информацией к тормозной вьюшке:
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 раз:

select * from test1
where slow_function(val) > 0
order by text
offset 0 rows
fetch next 5 rows only;
29 мар 19, 21:20    [21847596]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
-2-
Member

Откуда:
Сообщений: 14730
Valergrad
На pl/sql это легко сделать вызвав slow_function всего лишь 5 раз.
давай код.
29 мар 19, 22:23    [21847617]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
-2-
Valergrad
На pl/sql это легко сделать вызвав slow_function всего лишь 5 раз.
давай код.


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

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;
/
29 мар 19, 23:44    [21847647]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
кит северных морей
Member

Откуда: Красноярск
Сообщений: 623
Valergrad
-2-
пропущено...
давай код.


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

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;
/
вы считаете, что здесь вы вызываете функцию только пять раз?
29 мар 19, 23:49    [21847648]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
-2-
Member

Откуда:
Сообщений: 14730
Valergrad
что-то вроде
Поздравляю с изобретением stop key.
30 мар 19, 00:59    [21847657]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
xtender
Member

Откуда: Мск
Сообщений: 5146
Valergrad
мне не получается написать запрос, который бы работал с таким планом.
покажи как пробовал? я ничего сложного в этом не вижу
30 мар 19, 01:35    [21847663]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
xtender
Member

Откуда: Мск
Сообщений: 5146
достаточно rownum или opt_param('_optimizer_filter_pushdown' 'false') чтобы не пропихивало предикат:
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;

+
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 и кучу разных выполнений лучше просто фетчить из одного курсора по сколько нужно
30 мар 19, 01:58    [21847667]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9491
xtender,

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

SY.
30 мар 19, 14:40    [21847815]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
Как обычно - пришел xtender и решил проблему :) Спасибо, я думаю это именно то, что нужно!


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


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

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

1) В 9 случаях из 10 пользователь не пойдет дальше первой страницы
2) Даже если пойдет, при втором и последующих выполнениях запроса, эти блоки уже будут в буффер кэше, так что проскакивать мимо них будет быстро.
30 мар 19, 16:36    [21847872]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
SY
xtender,

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

SY.


Думаете это меняет ситуацию по сравнению с исходной задачей? Ну, я проверю в понедельник - попробую подход с rownum использовать, мне ведь как раз это и нужно: чтобы предикаты не пушились вниз, а считались потихоньку, строчка за строчкой.
30 мар 19, 16:41    [21847874]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9491
Valergrad
Думаете это меняет ситуацию по сравнению с исходной задачей? Ну, я проверю в понедельник - попробую подход с rownum использовать, мне ведь как раз это и нужно: чтобы предикаты не пушились вниз, а считались потихоньку, строчка за строчкой.


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

SY.
30 мар 19, 20:25    [21847973]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
SY
Valergrad
Думаете это меняет ситуацию по сравнению с исходной задачей? Ну, я проверю в понедельник - попробую подход с rownum использовать, мне ведь как раз это и нужно: чтобы предикаты не пушились вниз, а считались потихоньку, строчка за строчкой.


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

SY.


Ну это вы уже придираетесь слегка. Нужно вызвать "slow_function минимально возможное количество раз ( т.к. тормозит именно она)" вам нравится больше?
30 мар 19, 20:33    [21847977]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9491
Valergrad
Ну это вы уже придираетесь слегка. Нужно вызвать "slow_function минимально возможное количество раз ( т.к. тормозит именно она)" вам нравится больше?


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

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

SY.
30 мар 19, 21:42    [21848003]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
SY
Valergrad
Ну это вы уже придираетесь слегка. Нужно вызвать "slow_function минимально возможное количество раз ( т.к. тормозит именно она)" вам нравится больше?


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

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

SY.


Извините, ну судя по тому что вы пишете - вы не прочитали исходный пост. Поэтому предлагаю не продолжать этот разговор, тем более что xtender уже ответил что нужно.
30 мар 19, 22:06    [21848021]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9491
Valergrad
Извините, ну судя по тому что вы пишете - вы не прочитали исходный пост. Поэтому предлагаю не продолжать этот разговор, тем более что xtender уже ответил что нужно.


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,
31 мар 19, 13:55    [21848241]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

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

Вы же почему-то уперлись в несущественную деталь и продолжаете почему-то на нее напирать как будто она что-то меняет. Я знаю что вы очень умный парень, неоднократно здесь это демонстрировали. Но в данном случае я ваш поинт никак не могу понять. Либо у вас есть действительно какой-то поинт, существенно меняющий ситуацию, который вы хотите объяснить - но пока что весьма неудачно, либо вы просто пытаетесь поднять себе самооценку за чужой счет придираясь к несущественным деталям.
31 мар 19, 20:58    [21848414]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
эх, добрался до компа чтобы убедиться что на исходном примере - с кучей таблиц и вьюшек не сработало.
В плане вместо WINDOW NOSORT STOPKEY используется WINDOW SORT PUSHED RANK.
Судя по всему, дело в том, что сортировать мне надо на самом деле не по столбцу, а по некой функции coalesce от нескольких столбцов. Я не понимаю почему, но легко убедиться что добавление функции ломает всю оптимизацию и функция снова вызывается 100 раз:

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;
1 апр 19, 15:40    [21849066]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 7697
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).
1 апр 19, 15:59    [21849085]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 7697
P.P.S.
Не очень понятно, насколько плохо решение через pipelined
Оно конечно выглядит крайне грубо и "прибито гвоздями". Но лучше прибить гвоздями, чем при upgrade на следующую версию Oracle, где оптимизатор с каждой версией все "умнее и умнее", хинты и планы уедут и ваши коллеги из будущего ))) будут вспоминать вас кучей добрых и ласковых слов.
Т.ч. мне кажется, нужно еще оценить риски решения с точки зрения надежности/будушего сопровождения.

IMHO & AFAIK
1 апр 19, 16:05    [21849093]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9491
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.
1 апр 19, 16:16    [21849106]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Valergrad
Member

Откуда:
Сообщений: 648
Leonid Kudryavtsev
P.P.S.
Не очень понятно, насколько плохо решение через pipelined
Оно конечно выглядит крайне грубо и "прибито гвоздями". Но лучше прибить гвоздями, чем при upgrade на следующую версию Oracle, где оптимизатор с каждой версией все "умнее и умнее", хинты и планы уедут и ваши коллеги из будущего ))) будут вспоминать вас кучей добрых и ласковых слов.
Т.ч. мне кажется, нужно еще оценить риски решения с точки зрения надежности/будушего сопровождения.

IMHO & AFAIK


В бэкенд к сожалению нужно передать курсор, а не коллекцию - фреймворк работает именно так.
Так что через pl/sql это выглядит как создание специального типа для результатов этого запроса, создание коллекции для него, сохранение результата в некую переменную коллекции и считывание потом этой переменной курсором. Ужасно уродливо и большой оверхед. Хотя если решение не найдется, возможно придется так и сделать.
1 апр 19, 16:23    [21849115]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 7697
Valergrad
В бэкенд к сожалению нужно передать курсор, а не коллекцию....


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

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

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

IMHO & AFAIK
1 апр 19, 16:30    [21849127]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 7697
P....s.
В общем-то, pipelined ф-ция это просто способ "прибить гвозьдями" метод доступа Nested Loops. Если Вы добъетесь в плане банального Nested Loops, то и SELECT должен выдать FIRST_ROWS не хуже, чем самопал через pipelined ф-цию.
Но hint'овать "новые" Oracle IMHO & AFAIK - все больше и больше "жизнь это боль" ( C ) Eve-online
С каждой новой версией Oracle оптимизатор все "умнее и умнее" и hint'ами его так просто не обманешь ))). "Oracle умный, он сам знает как лучше" ( С ) админ
/тут должен быть смайлик "смех сквозь слезы"/
1 апр 19, 16:38    [21849135]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 7697
Valergrad
....Это я туплю или оракл действительно так не может?

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

+

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 ? Я надеялся, что он ограничится только индексом (там все необходимые поля).
1 апр 19, 18:23    [21849273]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 7697
+

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

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;
/


with extra_func and pagination=44


1 апр 19, 18:49    [21849308]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить