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

Откуда:
Сообщений: 1776
Народ, возможно ли?
План запроса при одной таблице хороший - берётся индекс по CONNECTOR_CODE и CREATED, из него вычитывается первая тысяча о возвращается (общее кол-во строк удовлетворяющее условию - 7М)
select *
  from (select ID, CREATED
          from CONNECTOR_DATA
         where CONNECTOR_CODE = :1
         order by CREATED desc)
 where rownum <= 1000

Plan hash value: 1776959645
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |  1000 | 26000 |   989   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                        |       |       |            |          |
|   2 |   VIEW                        |                        |  1000 | 26000 |   989   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CONNECTOR_DATA         |  6914K|   131M|   989   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_CONN_DATA_CODE_CRTD |  1000 |       |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=1000)
   4 - access("CONNECTOR_CODE"=TO_NUMBER(:1))


Но нужно сделать две таблицы через unon all, и stopkey не прокидывается внутрь.
Хотя в принципе разумно было бы выбрать из каждой таблицы по 1000 строк отсортировать из и отдать первую тысячу?
Возможно ли заставить оптимизатор это сделать?

select *
  from (select ID, CREATED
          from tutdf.CONNECTOR_DATA a
         where CONNECTOR_CODE = :1
        union all
         select ID, CREATED
           from tutdf.CONNECTOR_DATA_new b
          where CONNECTOR_CODE = :1
        order by CREATED desc)
 where rownum <= 1000

Plan hash value: 3754634835
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                       |  1000 | 26000 |  1684   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY                          |                       |       |       |            |          |
|   2 |   VIEW                                  |                       |  2000 | 52000 |  1684   (1)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY                |                       |  2000 |   263M|  1683   (1)| 00:00:01 |
|   4 |     UNION-ALL                           |                       |       |       |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| CONNECTOR_DATA        |  1000 | 20000 |   841   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | IX_CONN_DATA_CODE     |  6914K|       |     8   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED| CONNECTOR_DATA_NEW    |  1000 | 20000 |   841   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN                  | IX_CONN_DATA_CODE_NEW |  6914K|       |     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=1000)
   3 - filter(ROWNUM<=1000)
   6 - access("CONNECTOR_CODE"=TO_NUMBER(:1))
   8 - access("CONNECTOR_CODE"=TO_NUMBER(:1))
22 май 18, 19:24    [21430331]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
xtender
Member

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

была такая задача лет 5 назад: как тогда CBO с этим не справлялся, так и сейчас не умеет. Единственное, с чем стало получше - это параллельный union all, поэтому при небольшой модификации (добавление order by) можно добиться такого:
+ тестовые таблицы
--drop table t1 purge;
--drop table t2 purge;
create table t1 as select mod(level,1000) a, level b, level c from dual connect by level<=1e4;
create table t2 as select 1000+mod(level,1000) a, 1000+level b, 1000+level c from dual connect by level<=1e4;
create index ix1 on t1(a,b);
create index ix2 on t2(a,b);
+ PQ_CONCURRENT_UNION
SQL Monitoring Report

SQL Text
------------------------------
select /*+ parallel(2) PQ_CONCURRENT_UNION */ *
  from (select *
          from (select a, b from t1 where a = :a order by b desc)
        union all
        select *
          from (select a, b from t2 where a = :a order by b desc)
         order by 1 desc) v
 where rownum < 5


Binds
========================================================================================================================
| Name | Position |  Type  |                                           Value                                           |
========================================================================================================================
| :A   |        1 | NUMBER | 3                                                                                         |
========================================================================================================================

Global Stats
===============================================================
| Elapsed |   Cpu   | Concurrency |  Other   | Fetch | Buffer |
| Time(s) | Time(s) |  Waits(s)   | Waits(s) | Calls |  Gets  |
===============================================================
|    0.39 |    0.02 |        0.08 |     0.30 |     2 |     10 |
===============================================================

Parallel Execution Details (DOP=2 , Servers Allocated=4)
========================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   | Concurrency |  Other   | Buffer | Wait Events |
|                |       |         | Time(s) | Time(s) |  Waits(s)   | Waits(s) |  Gets  | (sample #)  |
========================================================================================================
| PX Coordinator | QC    |         |    0.22 |    0.02 |             |     0.20 |        |             |
| p000           | Set 1 |       1 |    0.03 |         |        0.02 |     0.01 |        |             |
| p001           | Set 1 |       2 |    0.04 |         |        0.03 |     0.01 |        |             |
| p002           | Set 2 |       1 |    0.05 |         |             |     0.05 |      8 |             |
| p003           | Set 2 |       2 |    0.06 |         |        0.03 |     0.03 |      2 |             |
========================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3824323222)
==========================================================================================================================================================
| Id |                Operation                 |   Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                          |          | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
==========================================================================================================================================================
|  0 | SELECT STATEMENT                         |          |         |      |         1 |     +1 |     1 |        4 |     . |          |                 |
|  1 |   COUNT STOPKEY                          |          |         |      |         1 |     +1 |     1 |        4 |     . |          |                 |
|  2 |    PX COORDINATOR                        |          |         |      |         1 |     +1 |     5 |        4 |     . |   100.00 | Cpu (2)         |
|  3 |     PX SEND QC (ORDER)                   | :TQ10001 |      11 |    5 |         1 |     +1 |     2 |        5 |     . |          |                 |
|  4 |      VIEW                                |          |      11 |    5 |         1 |     +1 |     2 |        5 |     . |          |                 |
|  5 |       SORT ORDER BY STOPKEY              |          |      11 |    4 |         1 |     +1 |     2 |        5 |  2048 |          |                 |
|  6 |        PX RECEIVE                        |          |       4 |      |         1 |     +1 |     2 |        5 |     . |          |                 |
|  7 |         PX SEND RANGE                    | :TQ10000 |       4 |      |         1 |     +1 |     2 |        5 |     . |          |                 |
|  8 |          SORT ORDER BY STOPKEY           |          |       4 |      |         1 |     +1 |     2 |        5 |  2048 |          |                 |
|  9 |           UNION-ALL                      |          |         |      |         1 |     +1 |     2 |       10 |     . |          |                 |
| 10 |            PX SELECTOR                   |          |         |      |         1 |     +1 |     2 |       10 |     . |          |                 |
| 11 |             VIEW                         |          |      10 |    2 |         1 |     +1 |     1 |       10 |     . |          |                 |
| 12 |              INDEX RANGE SCAN DESCENDING | IX1      |      10 |    2 |         1 |     +1 |     1 |       10 |     . |          |                 |
| 13 |            PX SELECTOR                   |          |         |      |           |        |     2 |          |     . |          |                 |
| 14 |             VIEW                         |          |       1 |    2 |           |        |     1 |          |     . |          |                 |
| 15 |              INDEX RANGE SCAN DESCENDING | IX2      |       1 |    2 |           |        |     1 |          |     . |          |                 |
==========================================================================================================================================================

Takurava
Хотя в принципе разумно было бы выбрать из каждой таблицы по 1000 строк отсортировать из и отдать первую тысячу?
да, это наиболее простой вариант
23 май 18, 02:50    [21430875]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
Stax
Member

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

Но нужно сделать две таблицы через unon all, и stopkey не прокидывается внутрь.
Хотя в принципе разумно было бы выбрать из каждой таблицы по 1000 строк отсортировать из и отдать первую тысячу?
Возможно ли заставить оптимизатор это сделать?



а если в таблице меньше 1000записей?

.....
stax
23 май 18, 08:32    [21430989]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
Stax
Member

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

а если в таблице меньше 1000записей?


сморозил, без разницы

......
stax
23 май 18, 08:38    [21430997]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
-2-
Member

Откуда:
Сообщений: 15330
Takurava
Но нужно сделать две таблицы через unon all, и stopkey не прокидывается внутрь.
stopkey проталкивается внутрь - у тебя в плане rows=1000 по каждой таблице. Не проталкивается сортировка, так как она по объединенному набору записей.
23 май 18, 09:26    [21431080]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2795
-2-
Takurava
Но нужно сделать две таблицы через unon all, и stopkey не прокидывается внутрь.
stopkey проталкивается внутрь - у тебя в плане rows=1000 по каждой таблице. Не проталкивается сортировка, так как она по объединенному набору записей.

імхо
без сортировки нет смысла проталкивать 1000 (сортировка есть с использованием индекса)

.....
stax
23 май 18, 09:55    [21431135]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
Takurava,

А если так заморочиться?

select * from (
select *  from (select ID, CREATED
                       from CONNECTOR_DATA
                     where CONNECTOR_CODE = :1
                     order by CREATED desc)
 where rownum <= 1000
union all
select *  from (select ID, CREATED
                       from CONNECTOR_DATA_new
                     where CONNECTOR_CODE = :1
                     order by CREATED desc)
 where rownum <= 1000
order by CREATED desc)
 where rownum <= 1000


сейчас нет под рукой базы, проверить план, но чем черт не шутит
ну и в 12-м появился FETCH FIRST и выглядеть это будет как-то так (ну и тоже не могу сейчас проверить )

select * from (
                     (select ID, CREATED
                       from CONNECTOR_DATA
                     where CONNECTOR_CODE = :1
                     order by CREATED desc
                     FETCH FIRST 1000 ROWS ONLY)
union all
                    (select ID, CREATED
                       from CONNECTOR_DATA_new
                     where CONNECTOR_CODE = :1
                     order by CREATED desc
                     FETCH FIRST 1000 ROWS ONLY))
order by CREATED desc
FETCH FIRST 1000 ROWS ONLY
23 май 18, 10:01    [21431155]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 652
MaximaXXL,

в 12 можно так еще попробовать, может и не будет вычитывать все записи

         select ID, CREATED
          from tutdf.CONNECTOR_DATA a
         where CONNECTOR_CODE = :1
        union all
         select ID, CREATED
           from tutdf.CONNECTOR_DATA_new b
          where CONNECTOR_CODE = :1
        order by CREATED desc
        FETCH FIRST 1000 ROWS ONLY
23 май 18, 10:15    [21431209]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
Stax
Member

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

Takurava хочет чтоб ето сделал оптимизатор, а он етого пока не делает, хотя в плане намеки (Rows) есть

.....
stax
23 май 18, 10:26    [21431241]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18363
https://www.sql.ru/forum/297664/optimizator-pobezhdaet
23 май 18, 15:58    [21432990]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2795
andrey_anonymous
https://www.sql.ru/forum/297664/optimizator-pobezhdaet


автор
-- Умный оптимизатор отбирает в каждой просматриваемой секции
-- индекса не более N записей. Очень логично и замечательно работает.



для себя я так понял

для union all оптимизатор 11-ки еще не совсем умный и не проталкивает rownum <= 1000,
хотя в плане Rows=1000

завтра на livesql поробую, там уже 18-я версия

.....
stax
23 май 18, 17:39    [21433443]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
xtender
Member

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

ну твоя проблема в 12.2 уже решена:
+
  1  explain plan for
  2  SELECT MAX(TRIM(VAL))
  3  FROM
  4  (SELECT VAL FROM ( SELECT /*+ index_desc(t ane_test$p1$p2$p3) */ * FROM
  5*   ANE_TEST T WHERE PKEY > :B2 AND P1 = :B1 ORDER BY P2 DESC ) WHERE ROWNUM < :B3 )
SQL> /

Explained.

SQL> @xplan

P_FORMAT
----------------
typical


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3724497250

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                   |     1 |   102 |   358   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                                  |                   |     1 |   102 |            |          |       |       |
|   2 |   VIEW                                           |                   |   225 | 22950 |   358   (1)| 00:00:01 |       |       |
|*  3 |    COUNT STOPKEY                                 |                   |       |       |            |          |       |       |
|   4 |     VIEW                                         |                   |   225 | 22950 |   358   (1)| 00:00:01 |       |       |
|*  5 |      SORT ORDER BY STOPKEY                       |                   |   225 | 28125 |   358   (1)| 00:00:01 |       |       |
|   6 |       PARTITION RANGE ITERATOR                   |                   |   225 | 28125 |   357   (0)| 00:00:01 |     4 |   KEY |
|*  7 |        COUNT STOPKEY                             |                   |       |       |            |          |       |       |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ANE_TEST          |   225 | 28125 |   357   (0)| 00:00:01 |     4 |   KEY |
|*  9 |          INDEX RANGE SCAN DESCENDING             | ANE_TEST$P1$P2$P3 |  4500 |       |    29   (0)| 00:00:01 |     4 |   KEY |
--------------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter(ROWNUM<TO_NUMBER(:B3))
   5 - filter(ROWNUM<TO_NUMBER(:B3))
   7 - filter(ROWNUM<TO_NUMBER(:B3))
   8 - filter("PKEY">:B2)
   9 - access("P1"=TO_NUMBER(:B1))

25 rows selected.
23 май 18, 18:20    [21433594]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
Takurava
Member

Откуда:
Сообщений: 1776
В общем печально, но будем ждать:
Stax
завтра на livesql поробую, там уже 18-я версия
23 май 18, 19:15    [21433703]     Ответить | Цитировать Сообщить модератору
 Re: Прокинуть stopkey внутрь unnion all  [new]
Stax
Member

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

на тесте
select * from (
select empno,stax_log(rownum,empno) l from emp where empno>0
union all
select deptno,stax_log(2,deptno) l from dept where deptno>0 order by 1
)
where rownum<3
/


pезультат 18-й аналогично 11-ке

STOPKEY для каждой таблицы не проталкивается

.....
stax
24 май 18, 10:42    [21435272]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить