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

Откуда: Asus Winter Cup
Сообщений: 478
Всем привет.

При использовании коррелированного подзапроса не получается протолкнуть stopkey. Ниже тест кейсы.
BANNER                                                                               
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

+ ddl
/**************** TMP_DROPME ****************/
drop table tmp_dropme purge;

create table tmp_dropme pctfree 0 as
select 'string' || mod(rownum,10) v,
    sysdate + rownum d,
    round(dbms_random.value(1,10), 2) n
from dual
connect by level <= 1e3;

create unique index idx_tmp_dropme on tmp_dropme(v, d);

exec dbms_stats.gather_table_stats(user,'TMP_DROPME');

/**************** TMP_DROPME2 ****************/ 
drop table tmp_dropme2 purge;

create table tmp_dropme2 pctfree 0 as select * from tmp_dropme order by dbms_random.value fetch first 1 row only;

exec dbms_stats.gather_table_stats(user,'TMP_DROPME2');
+ test1 - TopN query
select count(1)  cnt from tmp_dropme tmp where tmp.v = 'string7' and tmp.d between sysdate AND sysdate+500;

CNT
---------------
50


select /*+gather_plan_statistics qry1*/ *
from(
    select d, n from tmp_dropme tmp 
    where tmp.v = 'string7' and tmp.d between sysdate AND sysdate+500
    order by d desc)
where rownum < 2;

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |      1 |        |      1 |00:00:00.01 |       3 |      3 |
|*  1 |  COUNT STOPKEY                  |                |      1 |        |      1 |00:00:00.01 |       3 |      3 |
|   2 |   VIEW                          |                |      1 |      2 |      1 |00:00:00.01 |       3 |      3 |
|*  3 |    FILTER                       |                |      1 |        |      1 |00:00:00.01 |       3 |      3 |
|   4 |     TABLE ACCESS BY INDEX ROWID | TMP_DROPME     |      1 |     50 |      1 |00:00:00.01 |       3 |      3 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IDX_TMP_DROPME |      1 |      2 |      1 |00:00:00.01 |       2 |      2 |
---------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<2)
   3 - filter(SYSDATE@!+500>=SYSDATE@!)
   5 - access("TMP"."V"='string7' AND "TMP"."D"<=SYSDATE@!+500 AND "TMP"."D">=SYSDATE@!)
 
+ test2 TopN query + correlated subquery
select * from tmp_dropme2;

V                                              D                            N
---------------------------------------------- ------------------- ----------
string1                                        17.12.2020 23:32:13       2,55


select count(1) cnt from tmp_dropme tmp where tmp.v = 'string1' and tmp.d between sysdate AND sysdate+500;

CNT
---------------
50


select /*+gather_plan_statistics qry2*/ *
from tmp_dropme2 t
    ,lateral 
        (select * from 
            (select d, n from tmp_dropme tmp 
            where tmp.v = t.v and tmp.d between sysdate AND sysdate+500
            order by d desc)
        where rownum < 2) z;

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |      1 |        |      1 |00:00:00.01 |       8 |      7 |       |       |          |
|   1 |  NESTED LOOPS                             |                 |      1 |      1 |      1 |00:00:00.01 |       8 |      7 |       |       |          |
|   2 |   TABLE ACCESS FULL                       | TMP_DROPME2     |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |       |       |          |
|   3 |   VIEW                                    | VW_LAT_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |      5 |       |       |          |
|*  4 |    COUNT STOPKEY                          |                 |      1 |        |      1 |00:00:00.01 |       5 |      5 |       |       |          |
|   5 |     VIEW                                  |                 |      1 |     50 |      1 |00:00:00.01 |       5 |      5 |       |       |          |
|*  6 |      SORT ORDER BY STOPKEY                |                 |      1 |     50 |      1 |00:00:00.01 |       5 |      5 |  2048 |  2048 | 2048  (0)|
|*  7 |       FILTER                              |                 |      1 |        |     50 |00:00:00.01 |       5 |      5 |       |       |          |
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| TMP_DROPME      |      1 |     50 |     50 |00:00:00.01 |       5 |      5 |       |       |          |
|*  9 |         INDEX RANGE SCAN                  | IDX_TMP_DROPME  |      1 |     50 |     50 |00:00:00.01 |       3 |      3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(ROWNUM<2)
   6 - filter(ROWNUM<2)
   7 - filter(SYSDATE@!+500>=SYSDATE@!)
   9 - access("TMP"."V"="T"."V" AND "TMP"."D">=SYSDATE@! AND "TMP"."D"<=SYSDATE@!+500)

В тесткейсе 1 - попытка выбрать строку из таблицы с максимальной датой в диапазоне, используя обратный порядок значений в индексе. Тут оптимизатор понимает, что от него хотят. A-Rows равно единице.
В тетскейсе 2 - попытка сделать тоже самое, но добавив немного корреляции во FROM. Тут оптимизатор добавляет сортировку, в которую попадает весь пул строк. Где stopkey? Пропал IRS DESCENDING.

Кто знает, как починить и протолкнуть stopkey в test2?

P.S. Хинты index_desc и index_rs_desc не помогают. Они хоть и проставляют в план IRS DESCENDING, но A-Rows остается равным 50. SORT ORDER BY STOPKEY также не пропадает.

Сообщение было отредактировано: 30 июн 20, 00:03
30 июн 20, 00:04    [22159329]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
AlexFF__|
Member

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

"TMP"."V"='string7' - one value
"TMP"."V"="T"."V" - how many?
30 июн 20, 00:44    [22159341]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
xtender
Member

Откуда: Мск
Сообщений: 5478
mlc
select /*+gather_plan_statistics qry2*/ *
from tmp_dropme2 t
    ,lateral 
        (select * from 
            (select d, n from tmp_dropme tmp 
            where tmp.v = t.v and tmp.d between sysdate AND sysdate+500
            order by tmp.v desc, d desc)
        where rownum < 2) z;

Но вообще надо быть осторожным с ROWNUM в LATERAL() - иногда это может привести к неправильным результатам из-за DCL (lateral view decorrelation):
http://orasql.org/2019/02/16/lateral-view-decorrelationvw_dcl-causes-wrong-results-with-rownum/
30 июн 20, 04:33    [22159365]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
AlexFF__|, и там и там по 100 значений, но в кейсах участвуют выборки по 50 значений.

select * from tmp_dropme2;

V          D                            N
---------- ------------------- ----------
string1    17.12.2020 23:32:13       2,55

select tmp.v, count(1) cnt_all, count(case when tmp.d between sysdate AND sysdate+500 then 1 end) cnt 
from tmp_dropme tmp where tmp.v in ('string1', 'string7') group by v;

V             CNT_ALL        CNT
---------- ---------- ----------
string1           100         50
string7           100         50


Сообщение было отредактировано: 30 июн 20, 09:08
30 июн 20, 09:10    [22159438]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
xtender,

спасибо за ссылку, сейчас почитаю.
30 июн 20, 09:12    [22159439]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
AmKad
Member

Откуда:
Сообщений: 5166
xtender

Но вообще надо быть осторожным с ROWNUM в LATERAL()
fetch first 1 row only?
30 июн 20, 09:12    [22159440]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
AmKad,

В моем случае fetch first 1 row only работает, как и rownum.

select /*+gather_plan_statistics qry3*/ *
from tmp_dropme2 t
    ,lateral 
        (select d, n from tmp_dropme tmp 
        where tmp.v = t.v and tmp.d between sysdate AND sysdate+500
        order by d desc fetch first 1 row only) z;
+ dbms_xplan
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                 |      1 |        |      1 |00:00:00.03 |       7 |      7 |       |       |          |
|   1 |  NESTED LOOPS                             |                 |      1 |      1 |      1 |00:00:00.03 |       7 |      7 |       |       |          |
|   2 |   TABLE ACCESS FULL                       | TMP_DROPME2     |      1 |      1 |      1 |00:00:00.02 |       2 |      2 |       |       |          |
|   3 |   VIEW                                    | VW_LAT_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |      5 |       |       |          |
|*  4 |    VIEW                                   |                 |      1 |      1 |      1 |00:00:00.01 |       5 |      5 |       |       |          |
|*  5 |     WINDOW SORT PUSHED RANK               |                 |      1 |     50 |      1 |00:00:00.01 |       5 |      5 |  2048 |  2048 | 2048  (0)|
|*  6 |      FILTER                               |                 |      1 |        |     50 |00:00:00.01 |       5 |      5 |       |       |          |
|   7 |        TABLE ACCESS BY INDEX ROWID BATCHED| TMP_DROPME      |      1 |     50 |     50 |00:00:00.01 |       5 |      5 |       |       |          |
|*  8 |         INDEX RANGE SCAN                  | IDX_TMP_DROPME  |      1 |     50 |     50 |00:00:00.01 |       3 |      3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=1)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("TMP"."D") DESC )<=1)
   6 - filter(SYSDATE@!+500>=SYSDATE@!)
   8 - access("TMP"."V"="T"."V" AND "TMP"."D">=SYSDATE@! AND "TMP"."D"<=SYSDATE@!+500)
30 июн 20, 09:44    [22159456]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
AlexFF__|
Member

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

Ты меня не понял, что бы сработал COUNT STOPKEY на INDEX RANGE SCAN DESCENDING, нужно определить диапазон в индексе, перейти на его конец и сделать шаг назад => получили то самое одно значение.

Когда потенциальных значений несколько (а тебя именно такой случай, не смотря на одно реальное), oracle должен сделать выборки из нескольких потенциальных диапазонов, отсортировать их, и только потом применить на COUNT STOPKEY на первом/последнем.
30 июн 20, 11:45    [22159546]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
xtender
Member

Откуда: Мск
Сообщений: 5478
AmKad
xtender

Но вообще надо быть осторожным с ROWNUM в LATERAL()
fetch first 1 row only?
нет, это хуже - нет stopkey в случаях с order by desc более чем одного поля, т.е. было бы order by a desc или order by a,b - было бы ок, но не в случае order by a desc, b desc.


mlc
В моем случае fetch first 1 row only работает, как и rownum.
нет не так:
+ rownum
select /*+gather_plan_statistics qry2*/ *
from tmp_dropme2 t
    ,lateral 
        (select * from 
            (select d, n from tmp_dropme tmp 
            where tmp.v = t.v and tmp.d between sysdate AND sysdate+500
            order by tmp.v desc, d desc)
        where rownum < 2) z;

Plan hash value: 486177393

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |      1 |        |       |     6 (100)|          |      1 |00:00:00.01 |       6 |
|   1 |  NESTED LOOPS                     |                 |      1 |      1 |    42 |     6   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS FULL               | TMP_DROPME2     |      1 |      1 |    20 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|   3 |   VIEW                            | VW_LAT_A18161FF |      1 |      1 |    22 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  4 |    COUNT STOPKEY                  |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |
|   5 |     VIEW                          |                 |      1 |      2 |    44 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  6 |      FILTER                       |                 |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |
|   7 |       TABLE ACCESS BY INDEX ROWID | TMP_DROPME      |      1 |     50 |  1000 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  8 |        INDEX RANGE SCAN DESCENDING| IDX_TMP_DROPME  |      1 |      2 |       |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------------------
+ fetch first
select /*+gather_plan_statistics qry2*/ *
from tmp_dropme2 t
    ,lateral(
     select d, n from tmp_dropme tmp 
     where tmp.v = t.v and tmp.d between sysdate AND sysdate+500
     order by tmp.v desc, d desc
     fetch first 1 rows only
     ) z;

Plan hash value: 3111541902

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |      1 |        |       |     8 (100)|          |      1 |00:00:00.01 |       6 |       |       |          |
|   1 |  NESTED LOOPS                     |                 |      1 |      1 |    42 |     8   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |       |       |          |
|   2 |   TABLE ACCESS FULL               | TMP_DROPME2     |      1 |      1 |    20 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |       |       |          |
|   3 |   VIEW                            | VW_LAT_A18161FF |      1 |      1 |    22 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|*  4 |    VIEW                           |                 |      1 |      1 |    69 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|*  5 |     WINDOW BUFFER PUSHED RANK     |                 |      1 |     50 |  1000 |     5   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
|*  6 |      FILTER                       |                 |      1 |        |       |            |          |     50 |00:00:00.01 |       4 |       |       |          |
|   7 |       TABLE ACCESS BY INDEX ROWID | TMP_DROPME      |      1 |     50 |  1000 |     5   (0)| 00:00:01 |     50 |00:00:00.01 |       4 |       |       |          |
|*  8 |        INDEX RANGE SCAN DESCENDING| IDX_TMP_DROPME  |      1 |     50 |       |     2   (0)| 00:00:01 |     50 |00:00:00.01 |       2 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$3 / Z@SEL$1
   4 - SEL$2 / from$_subquery$_004@SEL$3
   5 - SEL$2
   7 - SEL$2 / TMP@SEL$2
   8 - SEL$2 / TMP@SEL$2

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

   4 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=1)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("TMP"."V") DESC ,INTERNAL_FUNCTION("TMP"."D") DESC )<=1)
   6 - filter(SYSDATE@!+500>=SYSDATE@!)
   8 - access("TMP"."V"="T"."V" AND "TMP"."D"<=SYSDATE@!+500 AND "TMP"."D">=SYSDATE@!)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."V"[VARCHAR2,46], "T"."D"[DATE,7], "T"."N"[NUMBER,22], "Z"."D"[DATE,7], "Z"."N"[NUMBER,22]
   2 - "T"."V"[VARCHAR2,46], "T"."D"[DATE,7], "T"."N"[NUMBER,22]
   3 - "Z"."D"[DATE,7], "Z"."N"[NUMBER,22]
   4 - "from$_subquery$_004"."D"[DATE,7], "from$_subquery$_004"."N"[NUMBER,22], "from$_subquery$_004"."rowlimit_$$_rownumber"[NUMBER,22]
   5 - (#keys=2) "TMP"."V"[VARCHAR2,46], "TMP"."D"[DATE,7], "TMP".ROWID[ROWID,10], "N"[NUMBER,22], ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("TMP"."V") DESC
       ,INTERNAL_FUNCTION("TMP"."D") DESC )[22]
   6 - "TMP".ROWID[ROWID,10], "TMP"."V"[VARCHAR2,46], "TMP"."D"[DATE,7], "N"[NUMBER,22]
   7 - "TMP".ROWID[ROWID,10], "TMP"."V"[VARCHAR2,46], "TMP"."D"[DATE,7], "N"[NUMBER,22]
   8 - "TMP".ROWID[ROWID,10], "TMP"."V"[VARCHAR2,46], "TMP"."D"[DATE,7]
30 июн 20, 14:24    [22159691]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
xtender
    order by tmp.v desc, d desc

Блин, я как-то сразу не обратил внимание на второе поле в сортировке. Спасибо, что ткнул еще разок.

Я так понял, что невозможно использовать STOPKPEY в IRS DESCENDING не сортируя оба поля desc, несмотря на то, что первое вроде как уже указано. Что в принципе вполне логично.

Спасибо.
30 июн 20, 17:46    [22159876]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
xtender
Member

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

Ну и как я выше сказал, лучше добавить сразу хинт no_decorrelate, чтобы потом не приплыть к wrong results
30 июн 20, 18:33    [22159912]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
xtender,

Че т вот не понял, как так вышло. В моем розовом мире IRS DESCCENDING + STOPKEY должен был помочь, ибо я ожидал от него, что будет прочитан максимум 1 листовой блок на один starts, но вставив lateral в рабочий запрос вышло еще хуже, чем было (а было inline view с max keep dense_rank):

Было:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
...
|  28 |    VIEW PUSHED PREDICATE             | VW_DCL_A18161FF         |  11973 |      1 |  11973 |00:02:32.27 |   48899 |  13361 |       |       |          |
|  29 |     SORT GROUP BY                    |                         |  11973 |      1 |  11973 |00:02:32.24 |   48899 |  13361 |  2048 |  2048 | 2048  (0)|
|* 30 |      FILTER                          |                         |  11973 |        |    182K|00:02:26.40 |   48899 |  13361 |       |       |          |
|* 31 |       INDEX RANGE SCAN               | PK_AL_STOCK_REM         |  11973 |     15 |    182K|00:02:26.32 |   48899 |  13361 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Стало:
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
....
|  28 |    VIEW                              | VW_LAT_A18161FF         |  11973 |      1 |  11973 |00:03:45.69 |   63748 |  14171 |       |       |          |
|* 29 |     COUNT STOPKEY                    |                         |  11973 |        |  11973 |00:03:45.67 |   63748 |  14171 |       |       |          |
|  30 |      VIEW                            |                         |  11973 |      2 |  11973 |00:03:45.59 |   63748 |  14171 |       |       |          |
|* 31 |       FILTER                         |                         |  11973 |        |  11973 |00:03:45.57 |   63748 |  14171 |       |       |          |
|* 32 |        INDEX RANGE SCAN DESCENDING   | PK_AL_STOCK_REM         |  11973 |      2 |  11973 |00:03:45.54 |   63748 |  14171 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Не могу понять почему при одинаковых starts = 11973 - количество прочитанных блоков с диска со stopkey больше, чем без него. Хотя я ожидал обратного эффекта.
2 июл 20, 11:45    [22160720]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
xtender
Member

Откуда: Мск
Сообщений: 5478
mlc
VW_DCL_A18161FF         
ты вообще изначально неправильно поставил задачу. У тебя тут декоррелированный латерал.
Показывай нормально полный запрос и планы со статистиками
2 июл 20, 12:24    [22160761]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
Stax
Member

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

У тебя тут декоррелированный латерал.


Если не тяжело, то в двух словах что это такое? трансформация lateral в join?

можно просто ссылку

.....
stax
2 июл 20, 12:33    [22160771]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
xtender
Member

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

https://hourim.wordpress.com/2017/03/25/de-correlated-lateral-view-vw_dcl_mmm/
2 июл 20, 14:01    [22160893]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
Stax
Member

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

Спасибо, примерно так себе и представлял

SELECT
  t1.id1 id1,
  t1.flag1 flag1,
  t1.flag2 flag2,
  t1.n1 n1,
  t1.v1 v1,
  vw_dcl_1b0973d4.item_1 id1,
  vw_dcl_1b0973d4.item_2 product_t1,
  vw_dcl_1b0973d4.item_3 start_date,
  vw_dcl_1b0973d4.item_4 end_date,
  vw_dcl_1b0973d4.item_5 padding
FROM c##mhouri.t1 t1,
  (SELECT
    t2.id1 item_1_0,
    t2.product_t1 item_2_1,
    t2.start_date item_3_2,
    t2.end_date item_4_3,
    t2.padding item_5_4
  FROM c##mhouri.t2 t2
  WHERE t2.start_date<=:b1 AND t2.end_date >=:b2
  OR t2.id1           >100
  ) VW_DCL_1B0973D4
WHERE t1.id1=VW_DCL_1B0973D4.item_2(+) 


не понял (туплю) откуда взялся VW_DCL_1B0973D4.item_2

.....
stax
2 июл 20, 14:28    [22160927]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
xtender,

al_daily_rem_d - IOT c PK.

+ lateral + order by
select /*+gather_plan_statistics */
    t.no,
    t.key,
    c.remain,
    c.system_date
from 
   (select 
        t.* 
    from al_stock_no d, al_stock_item t
    where t.status = 1 
        and t.no = d.no 
        and 1=1
    union all
    select
        t.* 
    from al_stock_no d, al_stock_item t
    where t.status = 0 
        and t.no = d.no 
        and d.type in (1253, 1313) 
        and 1=0)t,
    lateral(select * from
        (select --+no_decorrelate
            asr.remain,
            asr.system_date
          FROM al_daily_rem_d asr
          WHERE asr.no = t.key 
            AND asr.system_date >= TO_DATE('01.01.2017', 'dd.mm.yyyy')
            AND asr.system_date <= sysdate
          order by asr.no desc, asr.system_date desc)
        where rownum = 1) c;
        
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                   |      1 |        |  11973 |00:06:16.77 |   75831 |  20291 |
|   1 |  NESTED LOOPS                            |                   |      1 |   1879 |  11973 |00:06:16.77 |   75831 |  20291 |
|   2 |   VIEW                                   |                   |      1 |   1879 |  11973 |00:02:14.45 |   12230 |   6089 |
|   3 |    UNION-ALL                             |                   |      1 |        |  11973 |00:02:14.44 |   12230 |   6089 |
|*  4 |     HASH JOIN                            |                   |      1 |   1878 |  11973 |00:02:14.41 |   12230 |   6089 |
|   5 |      NESTED LOOPS                        |                   |      1 |   1878 |  11973 |00:02:14.40 |   12230 |   6089 |
|   6 |       NESTED LOOPS                       |                   |      1 |   1986 |  11973 |00:00:13.44 |    1370 |    713 |
|   7 |        STATISTICS COLLECTOR              |                   |      1 |        |    662 |00:00:00.08 |       4 |      3 |
|   8 |         TABLE ACCESS FULL                | AL_STOCK_NO       |      1 |    662 |    662 |00:00:00.08 |       4 |      3 |
|*  9 |        INDEX RANGE SCAN                  | AL_STOCK_ITEM_NO  |    662 |      3 |  11973 |00:00:06.77 |    1366 |    710 |
|  10 |       TABLE ACCESS BY INDEX ROWID        | AL_STOCK_ITEM     |  11973 |      3 |  11973 |00:01:53.08 |   10860 |   5376 |
|* 11 |      TABLE ACCESS FULL                   | AL_STOCK_ITEM     |      0 |      3 |      0 |00:00:00.01 |       0 |      0 |
|* 12 |     FILTER                               |                   |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|* 13 |      HASH JOIN                           |                   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|  14 |       TABLE ACCESS BY INDEX ROWID BATCHED| AL_STOCK_ITEM     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|* 15 |        INDEX RANGE SCAN                  | AL_STOCK_ITEM_NO  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|* 16 |       TABLE ACCESS FULL                  | AL_STOCK_NO       |      0 |    233 |      0 |00:00:00.01 |       0 |      0 |
|  17 |   VIEW                                   | VW_LAT_A18161FF   |  11973 |      1 |  11973 |00:04:02.32 |   63601 |  14202 |
|* 18 |    COUNT STOPKEY                         |                   |  11973 |        |  11973 |00:04:02.29 |   63601 |  14202 |
|  19 |     VIEW                                 |                   |  11973 |      2 |  11973 |00:04:02.20 |   63601 |  14202 |
|* 20 |      FILTER                              |                   |  11973 |        |  11973 |00:04:02.18 |   63601 |  14202 |
|* 21 |       INDEX RANGE SCAN DESCENDING        | AL_DAILY_REM_D_PK |  11973 |     15 |  11973 |00:04:02.14 |   63601 |  14202 |
---------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("T"."NO"="D"."NO")
   9 - access("T"."STATUS"=1 AND "T"."NO"="D"."NO")
  11 - filter("T"."STATUS"=1)
  12 - filter(NULL IS NOT NULL)
  13 - access("T"."NO"="D"."NO")
  15 - access("T"."STATUS"=0)
  16 - filter(("D"."TYPE"=1253 OR "D"."TYPE"=1313))
  18 - filter(ROWNUM=1)
  20 - filter(SYSDATE@!>=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  21 - access("ASR"."NO"="T"."KEY" AND "ASR"."SYSTEM_DATE"<=SYSDATE@! AND "ASR"."SYSTEM_DATE">=TO_DATE(' 2017-01-01 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
+ inline view + push predicate
select /*+gather_plan_statistics */
    t.no,
    t.key,
    c.remain,
    c.system_date
from 
   (select 
        t.* 
    from al_stock_no d, al_stock_item t
    where t.status = 1 
        and t.no = d.no 
        and 1=1
    union all
    select
        t.* 
    from al_stock_no d, al_stock_item t
    where t.status = 0 
        and t.no = d.no 
        and d.type in (1253, 1313) 
        and 1=0)t,
    (select acc.no
            ,max(acc.remain) keep(dense_rank last order by acc.system_date) as remain
            ,max(acc.system_date) as system_date
        from al_daily_rem_d acc
       where acc.system_date >= to_date('01.01.2017', 'dd.mm.yyyy')
         and acc.system_date <= sysdate
       group by acc.no) c
       where c.no = t.key;
       
       
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                   |      1 |        |  11973 |00:03:06.08 |   62475 |  20551 |
|   1 |  NESTED LOOPS                            |                   |      1 |   1879 |  11973 |00:03:06.08 |   62475 |  20551 |
|   2 |   VIEW                                   |                   |      1 |   1879 |  11973 |00:00:59.57 |   12235 |   5970 |
|   3 |    UNION-ALL                             |                   |      1 |        |  11973 |00:00:59.57 |   12235 |   5970 |
|*  4 |     HASH JOIN                            |                   |      1 |   1878 |  11973 |00:00:59.54 |   12235 |   5970 |
|   5 |      NESTED LOOPS                        |                   |      1 |   1878 |  11973 |00:00:59.52 |   12235 |   5970 |
|   6 |       NESTED LOOPS                       |                   |      1 |   1986 |  11973 |00:00:00.41 |    1375 |    703 |
|   7 |        STATISTICS COLLECTOR              |                   |      1 |        |    662 |00:00:00.03 |       3 |      3 |
|   8 |         TABLE ACCESS FULL                | AL_STOCK_NO       |      1 |    662 |    662 |00:00:00.02 |       3 |      3 |
|*  9 |        INDEX RANGE SCAN                  | AL_STOCK_ITEM_NO  |    662 |      3 |  11973 |00:00:00.40 |    1372 |    700 |
|  10 |       TABLE ACCESS BY INDEX ROWID        | AL_STOCK_ITEM     |  11973 |      3 |  11973 |00:01:06.70 |   10860 |   5267 |
|* 11 |      TABLE ACCESS FULL                   | AL_STOCK_ITEM     |      0 |      3 |      0 |00:00:00.01 |       0 |      0 |
|* 12 |     FILTER                               |                   |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|* 13 |      HASH JOIN                           |                   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|  14 |       TABLE ACCESS BY INDEX ROWID BATCHED| AL_STOCK_ITEM     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|* 15 |        INDEX RANGE SCAN                  | AL_STOCK_ITEM_NO  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|* 16 |       TABLE ACCESS FULL                  | AL_STOCK_NO       |      0 |    233 |      0 |00:00:00.01 |       0 |      0 |
|  17 |   VIEW PUSHED PREDICATE                  |                   |  11973 |      1 |  11973 |00:02:13.28 |   50240 |  14581 |
|* 18 |    FILTER                                |                   |  11973 |        |  11973 |00:02:13.25 |   50240 |  14581 |
|  19 |     SORT AGGREGATE                       |                   |  11973 |      1 |  11973 |00:02:13.22 |   50240 |  14581 |
|* 20 |      FILTER                              |                   |  11973 |        |    193K|00:01:58.24 |   50240 |  14581 |
|* 21 |       INDEX RANGE SCAN                   | AL_DAILY_REM_D_PK |  11973 |     15 |    193K|00:01:58.17 |   50240 |  14581 |
---------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("T"."NO"="D"."NO")
   9 - access("T"."STATUS"=1 AND "T"."NO"="D"."NO")
  11 - filter("T"."STATUS"=1)
  12 - filter(NULL IS NOT NULL)
  13 - access("T"."NO"="D"."NO")
  15 - access("T"."STATUS"=0)
  16 - filter(("D"."TYPE"=1253 OR "D"."TYPE"=1313))
  18 - filter(COUNT(*)>0)
  20 - filter(SYSDATE@!>=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  21 - access("ACC"."NO"="T"."KEY" AND "ACC"."SYSTEM_DATE">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
              AND "ACC"."SYSTEM_DATE"<=SYSDATE@!)
+ lateral view + unique index scan
select /*+gather_plan_statistics */
    t.no,
    t.key,
    c.remain,
    c.system_date
from 
   (select 
        t.* 
    from al_stock_no d, al_stock_item t
    where t.status = 1 
        and t.no = d.no 
        and 1=1
    union all
    select
        t.* 
    from al_stock_no d, al_stock_item t
    where t.status = 0 
        and t.no = d.no 
        and d.type in (1253, 1313) 
        and 1=0)t,
    lateral(select 
                system_date, remain 
            from al_daily_rem_d where (no, system_date) = (
                    select  
                        t.key, max(system_date)
                    from al_daily_rem_d acc 
                    where no = t.key
                    and acc.system_date between TO_DATE('01.01.2017', 'dd.mm.yyyy') AND sysdate)) c;
                    
                    
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                   |      1 |        |  11973 |00:04:20.85 |     108K|  18682 |
|   1 |  NESTED LOOPS                            |                   |      1 |     37 |  11973 |00:04:20.85 |     108K|  18682 |
|   2 |   VIEW                                   |                   |      1 |   1879 |  11973 |00:01:28.14 |   12229 |   6173 |
|   3 |    UNION-ALL                             |                   |      1 |        |  11973 |00:01:28.13 |   12229 |   6173 |
|*  4 |     HASH JOIN                            |                   |      1 |   1878 |  11973 |00:01:28.11 |   12229 |   6173 |
|   5 |      NESTED LOOPS                        |                   |      1 |   1878 |  11973 |00:01:28.09 |   12229 |   6173 |
|   6 |       NESTED LOOPS                       |                   |      1 |   1986 |  11973 |00:00:10.68 |    1369 |    714 |
|   7 |        STATISTICS COLLECTOR              |                   |      1 |        |    662 |00:00:00.02 |       3 |      3 |
|   8 |         TABLE ACCESS FULL                | AL_STOCK_NO       |      1 |    662 |    662 |00:00:00.02 |       3 |      3 |
|*  9 |        INDEX RANGE SCAN                  | AL_STOCK_ITEM_NO  |    662 |      3 |  11973 |00:00:07.33 |    1366 |    711 |
|  10 |       TABLE ACCESS BY INDEX ROWID        | AL_STOCK_ITEM     |  11973 |      3 |  11973 |00:01:20.67 |   10860 |   5459 |
|* 11 |      TABLE ACCESS FULL                   | AL_STOCK_ITEM     |      0 |      3 |      0 |00:00:00.01 |       0 |      0 |
|* 12 |     FILTER                               |                   |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|* 13 |      HASH JOIN                           |                   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|  14 |       TABLE ACCESS BY INDEX ROWID BATCHED| AL_STOCK_ITEM     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|* 15 |        INDEX RANGE SCAN                  | AL_STOCK_ITEM_NO  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |
|* 16 |       TABLE ACCESS FULL                  | AL_STOCK_NO       |      0 |    233 |      0 |00:00:00.01 |       0 |      0 |
|* 17 |   INDEX UNIQUE SCAN                      | AL_DAILY_REM_D_PK |  11973 |      1 |  11973 |00:02:41.93 |   95788 |  12509 |
|  18 |    SORT AGGREGATE                        |                   |  11973 |      1 |  11973 |00:02:41.60 |   47894 |  12509 |
|* 19 |     FILTER                               |                   |  11973 |        |  11973 |00:02:41.51 |   47894 |  12509 |
|  20 |      FIRST ROW                           |                   |  11973 |      1 |  11973 |00:02:41.46 |   47894 |  12509 |
|* 21 |       INDEX RANGE SCAN (MIN/MAX)         | AL_DAILY_REM_D_PK |  11973 |      1 |  11973 |00:02:41.44 |   47894 |  12509 |
---------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("T"."NO"="D"."NO")
   9 - access("T"."STATUS"=1 AND "T"."NO"="D"."NO")
  11 - filter("T"."STATUS"=1)
  12 - filter(NULL IS NOT NULL)
  13 - access("T"."NO"="D"."NO")
  15 - access("T"."STATUS"=0)
  16 - filter(("D"."TYPE"=1253 OR "D"."TYPE"=1313))
  17 - access("NO"= AND "SYSTEM_DATE"=)
  19 - filter(SYSDATE@!>=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  21 - access("NO"=:B1 AND "ACC"."SYSTEM_DATE"<=SYSDATE@! AND "ACC"."SYSTEM_DATE">=TO_DATE(' 2017-01-01 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
+ статистика таблицы al_daily_rem_d
col column_name for a15
col density for a20
select column_name, num_distinct, density, num_buckets, num_nulls, histogram, global_stats, user_stats 
from user_tab_col_statistics where table_name = 'AL_DAILY_REM_D' and column_name in ('NO','SYSTEM_DATE','REMAIN');

COLUMN_NAME     NUM_DISTINCT              DENSITY NUM_BUCKETS  NUM_NULLS HISTOGRAM       GLO USE
--------------- ------------ -------------------- ----------- ---------- --------------- --- ---
NO                  17393664 ,000000918267743             254          0 HEIGHT BALANCED YES NO 
SYSTEM_DATE             3712 ,000269396552                  1          0 NONE            YES NO 
REMAIN              38076416 ,0000789515238               254          0 HEIGHT BALANCED YES NO
3 июл 20, 13:23    [22161565]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
Может ли кто-нибудь прояснить почему IRS DESCENDING + STOPKEY и LATERAL + INDEX UNIQUE SCAN при одинаковых starts поднимают разное количество блоков с диска? IRS DESCENDING + STOPKEY затратнее INDEX RANGE SCAN (MIN/MAX)? Чисто логически что первая, что вторая операция должны брать данные из крайнего листового блока и все.
6 июл 20, 09:22    [22162524]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
xtender
Member

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

Сейчас некогда смотреть. Попробуйте пока в обратном порядке скрипты прогнать. Может у вас эффект сброса кэша.
6 июл 20, 10:06    [22162548]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 478
xtender,

не думаю. Я перед каждым тестом flush buffer cache выполняю.
7 июл 20, 10:47    [22163219]     Ответить | Цитировать Сообщить модератору
 Re: протолкнуть stopkey в коррелированный запрос  [new]
xtender
Member

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

Не надо! Наоборот, лучше по нескольку раз запрос гонять. И порядок попробовать сменить и посмотреть. Явно статы не релевантные
7 июл 20, 11:06    [22163239]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить