двойной select / Oracle / Sql.ru

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 * from brsactions a
where a.contractid = '3280915'
and a.actioncodeid in (17025310, 17025339, 17050320)
order by a.operdate desc
fetch first row only

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

select * from brsactions a
where a.contractid = '3280915'
and a.actioncodeid in (17025310, 17025339, 17050320)
order by a.operdate desc
fetch first row only

  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

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

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> 

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>

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> 

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

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

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)          |
=====================================================================================================================================================================================

двойной select [new]
petrovichvanya Member Откуда: Сообщений: 122	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 Откуда: Сообщений: 12	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]
petrovichvanya Member Откуда: Сообщений: 122	оба варианта не подошли, жаль(
7 дек 18, 12:19 [21756865] Ответить \| Цитировать Сообщить модератору

Re: двойной select [new]
Вячеслав Любомудров Member Откуда: Владивосток Сообщений: 18323	И что ты показал? Ну покажи на миллионе записей Там обычный COUNT STOPKEY уделает ROW_NUMBER() OVER (ORDER BY "A2"."SAL" DESC ) Это в общем-то давно известно
7 дек 18, 15:47 [21757189] Ответить \| Цитировать Сообщить модератору

Re: двойной select [new]
Вячеслав Любомудров Member Откуда: Владивосток Сообщений: 18323	Насколько помню, там речь не про остановку сортировки COUNT STOPKEY хранит (ROWNUM<11) всего 10 последних максимальных/минимальных значений -- вся сортировка делается не более чем с 10 максимальными/минимальными значениями WINDOW SORT PUSHED RANK вроде должен делать тоже самое, но в большинстве случаев это не работает и идет полная сортировка по окну Да вроде, обсуждалось неоднократно Или это у меня "волшебные пузырьки"?
7 дек 18, 16:03 [21757226] Ответить \| Цитировать Сообщить модератору

Добро пожаловать в форум, Guest >> Войти \| Регистрация \| Поиск \| Правила \|			В избранное \| Подписаться
Все форумы / Oracle

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

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

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