Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Прокинуть stopkey внутрь unnion all / 14 сообщений из 14, страница 1 из 1
22.05.2018, 19:24
    #39648297
Takurava
Takurava
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
Народ, возможно ли?
План запроса при одной таблице хороший - берётся индекс по CONNECTOR_CODE и CREATED, из него вычитывается первая тысяча о возвращается (общее кол-во строк удовлетворяющее условию - 7М)
Код: 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.
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 строк отсортировать из и отдать первую тысячу?
Возможно ли заставить оптимизатор это сделать?

Код: 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.
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))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 02:50
    #39648362
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
Takurava,

была такая задача лет 5 назад: как тогда CBO с этим не справлялся, так и сейчас не умеет. Единственное, с чем стало получше - это параллельный union all, поэтому при небольшой модификации (добавление order by) можно добиться такого:
 
тестовые таблицы
Код: plsql
1.
2.
3.
4.
5.
6.
--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
Код: 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.
61.
62.
63.
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 строк отсортировать из и отдать первую тысячу?да, это наиболее простой вариант
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 08:32
    #39648401
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
Takurava
Но нужно сделать две таблицы через unon all, и stopkey не прокидывается внутрь.
Хотя в принципе разумно было бы выбрать из каждой таблицы по 1000 строк отсортировать из и отдать первую тысячу?
Возможно ли заставить оптимизатор это сделать?



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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 08:38
    #39648402
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
Stax
а если в таблице меньше 1000записей?


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

......
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 09:26
    #39648429
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
TakuravaНо нужно сделать две таблицы через unon all, и stopkey не прокидывается внутрь.stopkey проталкивается внутрь - у тебя в плане rows=1000 по каждой таблице. Не проталкивается сортировка, так как она по объединенному набору записей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 09:55
    #39648444
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
-2-TakuravaНо нужно сделать две таблицы через unon all, и stopkey не прокидывается внутрь.stopkey проталкивается внутрь - у тебя в плане rows=1000 по каждой таблице. Не проталкивается сортировка, так как она по объединенному набору записей.
імхо
без сортировки нет смысла проталкивать 1000 (сортировка есть с использованием индекса)

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 10:01
    #39648451
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
Takurava,

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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 и выглядеть это будет как-то так (ну и тоже не могу сейчас проверить )

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 10:15
    #39648474
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
MaximaXXL,

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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
         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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 10:26
    #39648486
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
MaximaXXL,

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 15:58
    #39648844
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
http://www.sql.ru/forum/297664/optimizator-pobezhdaet
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 17:39
    #39648909
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
andrey_anonymous http://www.sql.ru/forum/297664/optimizator-pobezhdaet

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



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

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

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 18:20
    #39648929
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
andrey_anonymous,

ну твоя проблема в 12.2 уже решена:
 
Код: 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.
  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.

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.05.2018, 19:15
    #39648957
Takurava
Takurava
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
В общем печально, но будем ждать:
Staxзавтра на livesql поробую, там уже 18-я версия
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.05.2018, 10:42
    #39649232
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Прокинуть stopkey внутрь unnion all
Takurava,

на тесте
Код: plsql
1.
2.
3.
4.
5.
6.
7.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Прокинуть stopkey внутрь unnion all / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 565ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1883953]:
 0ms
get settings:
 8ms
get forum list:
 19ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 221ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 245ms
total:  565ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]