Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 двойной select  [new]
petrovichvanya
Member

Откуда:
Сообщений: 85
select * from(
select * from brsactions a
where a.contractid = '3280915'
and a.actioncodeid in (17025310, 17025339, 17050320)
order by a.operdate desc )
where rownum = 1


Здравствуйте, есть возможность вывести так же записи, только без использования второго select ?
7 дек 18, 11:41    [21756794]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
viking_dooh
Member

Откуда:
Сообщений: 10
petrovichvanya,

Для 12с:
select * from brsactions a
where a.contractid = '3280915'
and a.actioncodeid in (17025310, 17025339, 17050320)
order by a.operdate desc
fetch first row only
7 дек 18, 11:47    [21756807]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1419
petrovichvanya
Здравствуйте, есть возможность вывести так же записи, только без использования второго select ?


Для семерки
если есть индекс по operdate и operdate is not null

select /*+ index_desc(a i$brsactions$operdate) */ * from brsactions a
where a.contractid = '3280915'
and a.actioncodeid in (17025310, 17025339, 17050320)
where rownum = 1


.....
stax
7 дек 18, 12:16    [21756854]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
petrovichvanya
Member

Откуда:
Сообщений: 85
оба варианта не подошли, жаль(
7 дек 18, 12:19    [21756865]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
-2-
Member

Откуда:
Сообщений: 14107
viking_dooh
select * from brsactions a
where a.contractid = '3280915'
and a.actioncodeid in (17025310, 17025339, 17050320)
order by a.operdate desc
fetch first row only
Это без первого select, нужно без второго.
7 дек 18, 12:35    [21756901]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1419
petrovichvanya,

CONTRACTID уникальное

  1  with t as(
  2  select 1 contractid ,300 actioncodeid, date '2018-11-01' operdate from dual union all
  3  select 2 contractid ,202 actioncodeid, date '2018-11-11' operdate from dual union all
  4  select 3 contractid ,203 actioncodeid, date '2018-11-11' operdate from dual union all
  5  select 4 contractid ,200 actioncodeid, date '2018-11-11' operdate from dual union all
  6  select 5 contractid ,111 actioncodeid, date '2018-11-10' operdate from dual
  7  )
  8  select
  9   max(contractid) KEEP (DENSE_RANK FIRST ORDER BY operdate desc,contractid) contractid
 10  ,max(a.actioncodeid) KEEP (DENSE_RANK FIRST ORDER BY operdate desc,contractid) actioncodeid
 11  ,max(a.operdate) oper_date
 12* from t a
SQL> /

CONTRACTID ACTIONCODEID OPER_DAT
---------- ------------ --------
         2          202 11.11.18
7 дек 18, 12:55    [21756929]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9230
viking_dooh
Для 12с:
fetch first row only


Из серии о сусликах:

SQL> variable c clob
SQL> exec dbms_utility.expand_sql_text('select ename,sal from emp order by sal desc fetch first row only',:c)

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> print c

C
--------------------------------------------------------------------------------
SELECT  "A1"."ENAME" "ENAME",
        "A1"."SAL" "SAL"
  FROM  (
         SELECT  "A2"."ENAME" "ENAME",
                 "A2"."SAL" "SAL",
                 "A2"."SAL" "rowlimit_$_0",
                 ROW_NUMBER() OVER (ORDER BY "A2"."SAL" DESC ) "rowlimit_$$_rownumber"
           FROM  "SCOTT"."EMP" "A2"
        ) "A1"
  WHERE "A1"."rowlimit_$$_rownumber"<=1
  ORDER BY "A1"."rowlimit_$_0" DESC


SY.
7 дек 18, 15:15    [21757139]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2075
SY
Из серии о сусликах (...)
Опаньки..
Получается, FETCH FIRST .. - это даже хуже чем старое-доброе top-N?
7 дек 18, 15:31    [21757157]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9230
--Eugene--
Опаньки..
Получается, FETCH FIRST .. - это даже хуже чем старое-доброе top-N?


Да нет:

SQL> EXPLAIN PLAN FOR
  2  SELECT *
  3    FROM (SELECT * FROM employees ORDER BY employee_id)
  4    WHERE ROWNUM < 11;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 460450477

-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |    10 |  1330 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |           |       |       |            |          |
|   2 |   VIEW                  |           |   107 | 14231 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|           |   107 |  7704 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | EMPLOYEES |   107 |  7704 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<11)
   3 - filter(ROWNUM<11)

17 rows selected.

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM employees ORDER BY employee_id FETCH FIRST 10 ROWS ONLY;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |    10 |  1590 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |           |    10 |  1590 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  7704 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  7704 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPLOYEES"."EMPLOYEE_ID")<=10)

16 rows selected.

SQL> 


SY.
7 дек 18, 15:40    [21757169]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17931
И что ты показал?
Ну покажи на миллионе записей
Там обычный COUNT STOPKEY уделает ROW_NUMBER() OVER (ORDER BY "A2"."SAL" DESC )

Это в общем-то давно известно
7 дек 18, 15:47    [21757189]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9230
Вячеслав Любомудров
Это в общем-то давно известно


Воможно. Я думал и WINDOW SORT PUSHED RANK и SORT ORDER BY STOPKEY остановят сортировку после первых N.

SY.
7 дек 18, 15:54    [21757204]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17931
Насколько помню, там речь не про остановку сортировки
COUNT STOPKEY хранит (ROWNUM<11) всего 10 последних максимальных/минимальных значений -- вся сортировка делается не более чем с 10 максимальными/минимальными значениями
WINDOW SORT PUSHED RANK вроде должен делать тоже самое, но в большинстве случаев это не работает и идет полная сортировка по окну

Да вроде, обсуждалось неоднократно

Или это у меня "волшебные пузырьки"?
7 дек 18, 16:03    [21757226]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1419
Вячеслав Любомудров
И что ты показал?
Ну покажи на миллионе записей
Там обычный COUNT STOPKEY уделает ROW_NUMBER() OVER (ORDER BY "A2"."SAL" DESC )

Это в общем-то давно известно


600тысч, для rownun<10 разница незаметна

....
stax
7 дек 18, 16:23    [21757266]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
xtender
Member

Откуда: Мск
Сообщений: 4959
Вячеслав Любомудров
Насколько помню, там речь не про остановку сортировки
COUNT STOPKEY хранит (ROWNUM<11) всего 10 последних максимальных/минимальных значений -- вся сортировка делается не более чем с 10 максимальными/минимальными значениями
WINDOW SORT PUSHED RANK вроде должен делать тоже самое, но в большинстве случаев это не работает и идет полная сортировка по окну

Да вроде, обсуждалось неоднократно

Или это у меня "волшебные пузырьки"?
WINDOW SORT PUSHED RANK тоже так же работает, другое дело, что она сама по себе сложнее, поэтому и работает чуть дольше. Естественно, я не говорю о "with ties" или всяких RANK
7 дек 18, 16:40    [21757308]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Человек и Кошка
Member

Откуда: настоящему индейцу завсегда везде ништяк (с)
Сообщений: 830
Вячеслав Любомудров,

Какой запрос правильней и быстрее?
7 дек 18, 16:44    [21757317]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17931
Спасибо,
Но у меня, как правило, получались другие результаты
Может версии были не те, но сложилось стойкое впечатление, что в простых случаях COUNT STOPKEY проще и быстрее WINDOW SORT PUSHED RANK
В более сложных (в запросе кроме аналитики есть еще и общая сортировка) я даже не сомневался в этом
7 дек 18, 16:55    [21757342]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16909
Stax
Вячеслав Любомудров
И что ты показал?
Ну покажи на миллионе записей
Там обычный COUNT STOPKEY уделает ROW_NUMBER() OVER (ORDER BY "A2"."SAL" DESC )
Это в общем-то давно известно

600тысч, для rownun<10 разница незаметна


Не заметна.
+
SQL> create table dropme_t(orderkey not null, payload) as
  2    select decode(mod(rownum,2),0,rownum, -rownum) , rpad('A',1000,'B')
  3      from dual
  4   connect by level <= 1e6
  5  ;

Таблица создана.

--  сбор статистики нужен, чтобы оптимизатор не собирал рекурсивно в динамике - это портит статистику autotrace
SQL> exec dbms_stats.gather_table_stats(user, 'DROPME_T');

Процедура PL/SQL успешно завершена.

SQL> set echo on
SQL> set autotrace traceonly statistics
SQL> select * from (select * from dropme_t order by orderkey) where rownum <= 10;

10 строк выбрано.


Статистика
----------------------------------------------------------
          5  recursive calls
         21  db block gets
      71494  consistent gets
...
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> select * from dropme_t order by orderkey fetch first 10 rows only;

10 строк выбрано.


Статистика
----------------------------------------------------------
          2  recursive calls
         22  db block gets
      71497  consistent gets
...
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

-- теперь с индексом
SQL> create index dropme_t$ord on dropme_t(orderkey);

Индекс создан.

SQL> exec dbms_stats.gather_table_stats(user, 'DROPME_T', cascade=>true);

Процедура PL/SQL успешно завершена.

SQL> select * from (select * from dropme_t order by orderkey) where rownum <= 10;

10 строк выбрано.


Статистика
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          8  consistent gets
...
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> select * from dropme_t order by orderkey fetch first 10 rows only;

10 строк выбрано.


Статистика
----------------------------------------------------------
          2  recursive calls
          0  db block gets
          8  consistent gets
...
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> set autotrace off
SQL> drop table dropme_t purge;

Таблица удалена.

SQL>
7 дек 18, 17:04    [21757355]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 17931
Заклевали
Пойду дальше "пузырики" лопать
7 дек 18, 17:07    [21757365]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9230
xtender
WINDOW SORT PUSHED RANK тоже так же работает, другое дело, что она сама по себе сложнее, поэтому и работает чуть дольше. Естественно, я не говорю о "with ties" или всяких RANK


Не поленился проверить. Версия 12.1.0.2.0, 2 node RAC на EXADATA:

SQL> SELECT  COUNT(*)
  2    FROM  MNY_PROVISION_PART
  3  /

  COUNT(*)
----------
 418423940

SQL> SET TIMING ON
SQL> SELECT CD
  2    FROM  (SELECT CD FROM MNY_PROVISION_PART ORDER BY CD DESC)
  3    WHERE ROWNUM < 11
  4  /

        CD
----------
 442961538
 442961538
 442961538
 442961538
 442961308
 442961308
 442961308
 442961308
 442961308
 442961308

10 rows selected.

Elapsed: 00:04:50.19
SQL> SELECT  CD
  2    FROM  (SELECT CD FROM MNY_PROVISION_PART ORDER BY CD DESC)
  3    WHERE ROWNUM < 11
  4  /

        CD
----------
 442961538
 442961538
 442961538
 442961538
 442961308
 442961308
 442961308
 442961308
 442961308
 442961308

10 rows selected.

Elapsed: 00:04:42.91
SQL> SELECT  CD
  2    FROM  MNY_PROVISION_PART
  3    ORDER BY CD DESC
  4    FETCH FIRST 10 ROWS ONLY
  5  /

        CD
----------
 442961538
 442961538
 442961538
 442961538
 442961308
 442961308
 442961308
 442961308
 442961308
 442961308

10 rows selected.

Elapsed: 00:01:18.55
SQL> 


SY.
7 дек 18, 17:10    [21757368]     Ответить | Цитировать Сообщить модератору
 Re: двойной select  [new]
xtender
Member

Откуда: Мск
Сообщений: 4959
SY,

прогнал сейчас тесты на 12.1.0.2(не экзадата) и 12.2.0.1(экзадата): по умолчанию "fetch first" был быстрее в 2 раза. Глянул статистики: fetch first на обычном сервере использовал serial direct path reads, а rownum - нет.
На экзадате I/O
fetch first: через cell smart table scan
rownum: через cell multiblock physical read и cell multiblock read request

Очевидно, что rownum своим включением режима оптимизации first rows (_optimizer_rownum_pred_based_fkr - enable the use of first K rows due to rownum predicate), отключает директридсы и смартскан.


Пример с обычным сервером:
Сделал
alter session set "_serial_direct_read"=always;
и стало примерно одинаково:
--12.1.0.2
SQL> select * from (select * from TEST_TAB2 order by f1) where rownum<=10;

10 rows selected.

Elapsed: 00:00:05.30

SQL> select * from TEST_TAB2 order by f1 fetch first 10 rows only;

10 rows selected.

Elapsed: 00:00:05.22


Пример с экзадатой:
+ fetch first: 00:07:57.51
SQL> set autot trace stat feed only timing on
SQL> select * from TTT order by CCC fetch first 10 rows only;

10 rows selected.

Elapsed: 00:07:57.51

Statistics
----------------------------------------------------------
         17  recursive calls
         11  db block gets
    8649233  consistent gets
    8637603  physical reads
       1144  redo size
       7460  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

+ rownum c "_optimizer_rownum_pred_based_fkr"=false: 00:06:41.46
SQL> alter session set "_optimizer_rownum_pred_based_fkr"=false;

Session altered.

SQL> set feed only timing on autot trace stat
SQL> select * from (select * from TTT order by CCC) where rownum<=10;

10 rows selected.

Elapsed: 00:06:41.46

Statistics
----------------------------------------------------------
         11  recursive calls
          3  db block gets
    8641469  consistent gets
    8637300  physical reads
       3960  redo size
       7497  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

Global Stats
==============================================================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Fetch | Buffer | Read | Read  | Uncompressed |  Offload   |    Offload     |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes |    Bytes     | Elig Bytes | Returned Bytes | Offload |
==============================================================================================================================================================
|     401 |     388 |       10 |        0.01 |        0.00 |     2.44 |     2 |     9M | 599K |  66GB |         66GB |       66GB |           63GB |   4.87% |
==============================================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=306186833)
=====================================================================================================================================================================================
| Id |           Operation            |    Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity |           Activity Detail           |
|    |                                |            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |             (# samples)             |
=====================================================================================================================================================================================
|  0 | SELECT STATEMENT               |            |         |      |         1 |   +401 |     1 |       10 |      |       |     . |          |                                     |
|  1 |   COUNT STOPKEY                |            |         |      |         1 |   +401 |     1 |       10 |      |       |     . |          |                                     |
|  2 |    VIEW                        |            |    265M |  17M |         1 |   +401 |     1 |       10 |      |       |     . |          |                                     |
|  3 |     SORT ORDER BY STOPKEY      |            |    265M |  17M |       399 |     +3 |     1 |       10 |      |       |  6144 |    71.00 | Cpu (284)                           |
|  4 |      TABLE ACCESS STORAGE FULL | TTT        |    265M |   2M |       401 |     +1 |     1 |     266M | 599K |  66GB |   5MB |    29.00 | Cpu (99)                            |
|    |                                |            |         |      |           |        |       |          |      |       |       |          | cell single block physical read (2) |
|    |                                |            |         |      |           |        |       |          |      |       |       |          | cell smart table scan (15)          |
=====================================================================================================================================================================================


Так что по большому счету разницы нет и в случае со сложным запросом где реально работает ALL_ROWS разницы особой не будет. А для простых запросов, но по большим таблицам, там где реально нужна оптимизация ввода/вывода, там FKR портит всю картину, т.к. отключает эти оптимизации.
8 дек 18, 00:59    [21757716]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить