Пятничная задачка: эффективная сортировка с фильтрацией строк / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная задачка: эффективная сортировка с фильтрацией строк

6 сообщений из 31, страница 2 из 2

все

Пятничная задачка: эффективная сортировка с фильтрацией строк

#39794888

Valergrad

Участник

Сообщения: 601

Рейтинг: 0 / 0

Код: 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, 23:26

| Ответить | Цитировать | Написать

Пятничная задачка: эффективная сортировка с фильтрацией строк

#39794901

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

Valergrad

Код: 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;

сравни проекции

ValergradТак просто?ну или так еще:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ * 
from (
   select * 
   from (select/*+ no_merge */ t.val, upper(t.text) as text
         from test1 t
        )
   order by text
   ) v
where slow_function(val) > 0
order by text
offset 0 rows
fetch next 5 rows only

а вообще, лучше бы сразу на реальном коде и с реальными планами показывал, а то насколько я понимаю, у тебя там вьюха джойнится и там свои нюансы могут быть с учетом JPPD и ELIMINATE_OBY/NO_ELIMINATE_OBY

...

Рейтинг:

0 / 0

02.04.2019, 01:33

| Ответить | Цитировать | Написать

Пятничная задачка: эффективная сортировка с фильтрацией строк

#39794905

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

xtenderну или так еще:

Да, так лучше. Но если есть возможность создать FBI (может быть проблема при частых insert/update):

Код: 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.

create or replace function slow_function ( x number ) return number deterministic as
begin
    test_package.g_counter := test_package.g_counter + 1;
    dbms_output.put_line(test_package.g_counter);
    return dbms_random.value;
end;
/
create index test1_fbi
  on test1(
           case when slow_function(val) > 0 then text end,
           case when slow_function(val) > 0 then 1 end
          )
/
explain plan for
select * from test1
where case when slow_function(val) > 0 then 1 end = 1
order by case when slow_function(val) > 0 then text end
offset 0 rows
fetch next 5 rows only
/
select * from table(dbms_xplan.display)
/

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2326002217

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |   130 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                         |           |     1 |   130 |     4   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |           |     1 |   104 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST1     |     1 |   104 |     4   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN           | TEST1_FBI |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=0+5 AND
              "from$_subquery$_002"."rowlimit_$$_rownumber">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY CASE  WHEN
              "SCOTT"."SLOW_FUNCTION"("VAL")>0 THEN "TEXT" END )<=0+5)
   4 - access(CASE  WHEN "SCOTT"."SLOW_FUNCTION"("VAL")>0 THEN 1 END =1)
       filter(CASE  WHEN "SCOTT"."SLOW_FUNCTION"("VAL")>0 THEN 1 END =1)

21 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

02.04.2019, 02:58

| Ответить | Цитировать | Написать

Пятничная задачка: эффективная сортировка с фильтрацией строк

#39795183

Valergrad

Участник

Сообщения: 601

Рейтинг: 0 / 0

xtenderсравни проекции

Так я сравнил. Насколько я понимаю, для того чтобы там не было второй сортировки, ключи сортировки должны совпадать в строчках от SORT и в строчке от WINDOW.
Смотрим версию от SY. В SORT ORDER BY сортировка идет по UPPER("T"."TEXT"), а в WINDOW NOSORT STOPKEY по UPPER("V"."TEXT"). Окей, Oracle не дурак и сообразил что V.TEXT и T.TEXT суть одно и то же, поэтому функции от них равны и можно не делать вторую сортировку.
Теперь смотрим исходный пример. Там сортировка идет сначала по UPPER("T"."TEXT") а потом по "V"."TEXT". Но ведь Оракл легко может подставить значение "V"."TEXT" которое как раз равно UPPER("T"."TEXT") и понять что сортируется в обоих случаях по одному и тому же. Каким образом он правильно подставил в одном случае, и не может в другом? Он подставляет только колонки, но не функции?

xtenderа вообще, лучше бы сразу на реальном коде и с реальными планами показывал, а то насколько я понимаю, у тебя там вьюха джойнится и там свои нюансы могут быть с учетом JPPD и ELIMINATE_OBY/NO_ELIMINATE_OBY

Да я бы с удовольствием, думаешь мне самому нравится конструировать эти гипотетические примеры, надеясь что они не упускают ничего важного? Но NDA , security и т.п... На рабочей машине даже sql.ru закрыт - пишу не с нее, поэтому не прикрепляю планы. Думаю, что можно вылететь сразу как выложишь актуальный план с актуальными именами таблиц , не говоря уже о коде.

...

Рейтинг:

0 / 0

02.04.2019, 14:21

| Ответить | Цитировать | Написать

Пятничная задачка: эффективная сортировка с фильтрацией строк

#39795463

Sayan Malakshinov

Модератор форума

Откуда: Мск

Сообщения: 5 731

Рейтинг: 0 / 0

ValergradТак я сравнил.со своим upper(text) as text ты, видимо, сам себя запутал... CBO не такой умный, чтобы делать замены загодя.
Помедитируй над этим:

Код: 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.

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

Explained.

SQL> @xplan +projection

P_FORMAT
----------------------------------------------------------------------------------
typical +projection


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 403762756

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

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_$$_rownumb
              er">0)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "V"."UP")<=CASE  WHEN (0>=0)
              THEN 0 ELSE 0 END +5)
   3 - filter("SLOW_FUNCTION"("VAL")>0)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (rowset=147) "from$_subquery$_003"."VAL"[NUMBER,22],
       "from$_subquery$_003"."UP"[VARCHAR2,400],
       "from$_subquery$_003"."ROWNUM"[NUMBER,22],
       "from$_subquery$_003"."rowlimit_$$_rownumber"[NUMBER,22]
   2 - (#keys=1; rowset=147) "V"."UP"[VARCHAR2,400], "VAL"[NUMBER,22],
       "V"."ROWNUM"[NUMBER,22], ROW_NUMBER() OVER ( ORDER BY "V"."UP")[22]
   3 - "VAL"[NUMBER,22], "V"."UP"[VARCHAR2,400], "V"."ROWNUM"[NUMBER,22]
   4 - (#keys=1) UPPER("T"."TEXT")[400], "T"."VAL"[NUMBER,22], ROWNUM[22]
   5 - (rowset=147) "T"."VAL"[NUMBER,22], "T"."TEXT"[VARCHAR2,400], ROWNUM[8]
   6 - (rowset=147) "T"."VAL"[NUMBER,22], "T"."TEXT"[VARCHAR2,400]

ну и для полного прояснения попробуй сначала построить план с проекциями и предикатами сам для такого запроса:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

SQL> ;
  1  explain plan for
  2  select/*+ opt_param('_optimizer_filter_pushdown' 'false') */ *
  3  from (
  4     select/*+ no_merge */ t.val, upper(t.text) as up, rownum
  5     from test1 t
  6     where rownum>0 and upper(t.text)>'a'
  7     order by up
  8     ) v
  9  where slow_function(val) > 0
 10  order by upper(up)
 11  offset 0 rows
 12* fetch next 5 rows only
SQL> /

Explained.

отгадка

Код: 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.

SQL> @xplan +projection

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 2865627389

-----------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |     5 |  2215 |     6  (50)| 00:00:01 |
|   1 |  SORT ORDER BY            |       |     5 |  2215 |     6  (50)| 00:00:01 |
|*  2 |   VIEW                    |       |     5 |  2215 |     5  (40)| 00:00:01 |
|*  3 |    WINDOW SORT PUSHED RANK|       |     5 |  1140 |     5  (40)| 00:00:01 |
|*  4 |     VIEW                  |       |     5 |  1140 |     4  (25)| 00:00:01 |
|   5 |      SORT ORDER BY        |       |     5 |   520 |     4  (25)| 00:00:01 |
|   6 |       COUNT               |       |       |       |            |          |
|*  7 |        FILTER             |       |       |       |            |          |
|*  8 |         TABLE ACCESS FULL | TEST1 |     5 |   520 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE  WHEN
              (0>=0) THEN 0 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumbe
              r">0)
   3 - filter(ROW_NUMBER() OVER ( ORDER BY UPPER("V"."UP"))<=CASE  WHEN
              (0>=0) THEN 0 ELSE 0 END +5)
   4 - filter("SLOW_FUNCTION"("VAL")>0)
   7 - filter(ROWNUM>0)
   8 - filter(UPPER("T"."TEXT")>'a')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1; rowset=147) "from$_subquery$_003"."rowlimit_$_0"[VARCHAR2,
       400], "from$_subquery$_003"."VAL"[NUMBER,22],
       "from$_subquery$_003"."UP"[VARCHAR2,400],
       "from$_subquery$_003"."ROWNUM"[NUMBER,22]
   2 - (rowset=147) "from$_subquery$_003"."VAL"[NUMBER,22],
       "from$_subquery$_003"."UP"[VARCHAR2,400],
       "from$_subquery$_003"."ROWNUM"[NUMBER,22],
       "from$_subquery$_003"."rowlimit_$_0"[VARCHAR2,400],
       "from$_subquery$_003"."rowlimit_$$_rownumber"[NUMBER,22]
   3 - (#keys=1; rowset=147) UPPER("V"."UP")[400], "VAL"[NUMBER,22],
       "V"."UP"[VARCHAR2,400], "V"."ROWNUM"[NUMBER,22], ROW_NUMBER() OVER ( ORDER
       BY UPPER("V"."UP"))[22]
   4 - "VAL"[NUMBER,22], "V"."UP"[VARCHAR2,400], "V"."ROWNUM"[NUMBER,22]
   5 - (#keys=1) UPPER("T"."TEXT")[400], "T"."VAL"[NUMBER,22], ROWNUM[22]
   6 - "T"."VAL"[NUMBER,22], "T"."TEXT"[VARCHAR2,400], ROWNUM[8]
   7 - "T"."VAL"[NUMBER,22], "T"."TEXT"[VARCHAR2,400]
   8 - "T"."VAL"[NUMBER,22], "T"."TEXT"[VARCHAR2,400]

...

Рейтинг:

0 / 0

03.04.2019, 01:41

| Ответить | Цитировать | Написать

Пятничная задачка: эффективная сортировка с фильтрацией строк

#39796544

Valergrad

Участник

Сообщения: 601

Рейтинг: 0 / 0

Ну в итоге - все вроде работает.
Так что всем большое спасибо за помощь!

...

Рейтинг:

0 / 0

04.04.2019, 15:14

| Ответить | Цитировать | Написать

6 сообщений из 31, страница 2 из 2

все

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&gotonew=1&tid=1882623]:	0ms
get settings:	8ms
get forum list:	12ms
check forum access:	4ms
check topic access:	4ms
track hit:	64ms
get topic data:	10ms
get first new msg:	7ms
get forum data:	2ms
get page messages:	45ms
get tp. blocked users:	1ms
others:	13ms

total:	170ms