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

Откуда:
Сообщений: 623
SY
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, 23:26    [21849550]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
xtender
Member

Откуда: Мск
Сообщений: 5067
Valergrad
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
Так просто?
ну или так еще:
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
2 апр 19, 01:33    [21849599]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
SY
Member

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


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

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

Откуда:
Сообщений: 623
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 закрыт - пишу не с нее, поэтому не прикрепляю планы. Думаю, что можно вылететь сразу как выложишь актуальный план с актуальными именами таблиц , не говоря уже о коде.
2 апр 19, 14:21    [21850081]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка: эффективная сортировка с фильтрацией строк  [new]
xtender
Member

Откуда: Мск
Сообщений: 5067
Valergrad
Так я сравнил.
со своим upper(text) as text ты, видимо, сам себя запутал... CBO не такой умный, чтобы делать замены загодя.
Помедитируй над этим:
+
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]


ну и для полного прояснения попробуй сначала построить план с проекциями и предикатами сам для такого запроса:
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.

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

Откуда:
Сообщений: 623
Ну в итоге - все вроде работает.
Так что всем большое спасибо за помощь!
4 апр 19, 15:14    [21852768]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Oracle Ответить