Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Условие в Left Join VS Where / 20 сообщений из 20, страница 1 из 1
22.12.2016, 10:09
    #39372595
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Имеем запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Select count(*),
                    NVL(sum(case when NVL(s.serno, 0) > 0 then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('S2NT') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('SUCC', 'FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') = 'SUCC' and s.authserno > 0 then 1 else 0 end),0)
               from ctransactions t
               left outer join interfacerecordstatus s on t.serno = s.rowserno and s.partitionkey = t.partitionkey and s.recordtype = 'TRXN' and s.systemname = 'online' and s.systemtype = 'O'
              where t.batchserno = 321356
                and t.stgeneral = 'POST'



Он аццки тормозит, независимо от того хинтую я индексы или нет. Но вот если перенести часть условия в Where то он начинает летать хотьв пане запроса идет Full Scan по interfacerecordstatus
Что за ерунда? Надо статистику индексов пересобрать? Таблицы партицированные.

Этот летает:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Select count(*),
                    NVL(sum(case when NVL(s.serno, 0) > 0 then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('S2NT') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('SUCC', 'FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') = 'SUCC' and s.authserno > 0 then 1 else 0 end),0)
               from ctransactions t
               left outer join interfacerecordstatus s on t.serno = s.rowserno and s.partitionkey = t.partitionkey
              where t.batchserno = 321356
                and t.stgeneral = 'POST'
                and s.recordtype = 'TRXN' and s.systemname = 'online' and s.systemtype = 'O'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 10:16
    #39372602
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Индексы:

Код: plsql
1.
2.
3.
4.
create index CTRANSACTIONSI03 on CTRANSACTIONS (BATCHSERNO)
  local;
create index INTERFACERECORDSTATUSI01 on INTERFACERECORDSTATUS (SYSTEMNAME, SYSTEMTYPE, STATUS, ROWSERNO, PARTITIONKEY)
  local;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 10:17
    #39372604
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров Андрей,

а план посмотреть слабо ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 10:27
    #39372608
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Могу только через PL/SQL Dev:
Знаю что он кривой но все что есть...

http://s020.radikal.ru/i721/1612/e5/c70776ebe38e.jpg
http://s017.radikal.ru/i420/1612/e3/c009ed0606c2.jpg
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 10:28
    #39372609
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров Андрей,

за 10 лет не научился нормально показывать план запроса ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 10:42
    #39372622
Andrey.L
Andrey.L
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров Андрей,

Запросы не равнозначные, поэтому и планы и результаты (в общем случае) будут разные.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 11:28
    #39372671
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров Андрей,

stff dbms_xplan
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 11:51
    #39372707
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Жду админов, чтобы смотреть план обоих запросов.

Код: plsql
1.
select * from table(dbms_xplan.display_cursor())


авторSQL_ID 9m7787camwh4m, child number 0

begin :id := sys.dbms_transaction.local_transaction_id; end;

NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 13:07
    #39372827
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров Андрей,
Код: plsql
1.
select * from table(dbms_xplan.display_cursor())


так и пишешь ?
facepalm
но ответ тебе уже дали
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 14:00
    #39372940
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
:-)

Быстрый запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain plan for 
Select count(*),
                    NVL(sum(case when NVL(s.serno, 0) > 0 then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('S2NT') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('SUCC', 'FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') = 'SUCC' and s.authserno > 0 then 1 else 0 end),0)
               from ctransactions t
               left outer join interfacerecordstatus s on t.serno = s.rowserno and s.partitionkey = t.partitionkey
              where t.batchserno = 321356
                and t.stgeneral = 'POST'
                and s.recordtype = 'TRXN' and s.systemname = 'online' and s.systemtype = 'O';
select * from table(dbms_xplan.display);



dbms_xplan.displayPlan hash value: 2112472494

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 309K (1)| 01:01:57 | | |
| 1 | SORT AGGREGATE | | 1 | 63 | | | | |
|* 2 | HASH JOIN | | 1981 | 121K| 309K (1)| 01:01:57 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1612 | 35464 | 93 (0)| 00:00:02 | | |
| 4 | PARTITION LIST ALL | | 1612 | 35464 | 93 (0)| 00:00:02 | 1 | 13 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| CTRANSACTIONS | 1612 | 35464 | 93 (0)| 00:00:02 | 1 | 13 |
|* 6 | INDEX RANGE SCAN | CTRANSACTIONSI03 | 1857 | | 33 (0)| 00:00:01 | 1 | 13 |
| 7 | PARTITION LIST JOIN-FILTER | | 7206K| 281M| 309K (1)| 01:01:56 |:BF0000|:BF0000|
|* 8 | TABLE ACCESS FULL | INTERFACERECORDSTATUS | 7206K| 281M| 309K (1)| 01:01:56 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------------------------

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

2 - access("PARTITIONKEY"="PARTITIONKEY" AND "SERNO"="ROWSERNO")
5 - filter("STGENERAL"='POST')
6 - access("BATCHSERNO"=321356)
8 - filter("RECORDTYPE"='TRXN' AND "SYSTEMNAME"='online' AND "SYSTEMTYPE"='O')


Медленный:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
explain plan for 
Select count(*),
                    NVL(sum(case when NVL(s.serno, 0) > 0 then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('S2NT') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('SUCC', 'FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') = 'SUCC' and s.authserno > 0 then 1 else 0 end),0)
               from ctransactions t
               left outer join interfacerecordstatus s on t.serno = s.rowserno and s.partitionkey = t.partitionkey and s.recordtype = 'TRXN' and s.systemname = 'online' and s.systemtype = 'O'
              where t.batchserno = 321356
                and t.stgeneral = 'POST';                
select * from table(dbms_xplan.display);



dbms_xplan.displayPlan hash value: 4099375

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 63 | 309K (1)| 01:01:57 | | |
| 1 | SORT AGGREGATE | | 1 | 63 | | | | |
|* 2 | HASH JOIN OUTER | | 1981 | 121K| 309K (1)| 01:01:57 | | |
| 3 | PARTITION LIST ALL | | 1612 | 35464 | 93 (0)| 00:00:02 | 1 | 13 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| CTRANSACTIONS | 1612 | 35464 | 93 (0)| 00:00:02 | 1 | 13 |
|* 5 | INDEX RANGE SCAN | CTRANSACTIONSI03 | 1857 | | 33 (0)| 00:00:01 | 1 | 13 |
| 6 | PARTITION LIST ALL | | 7206K| 281M| 309K (1)| 01:01:56 | 1 | 27 |
|* 7 | TABLE ACCESS FULL | INTERFACERECORDSTATUS | 7206K| 281M| 309K (1)| 01:01:56 | 1 | 27 |
------------------------------------------------------------------------------------------------------------------------------

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

2 - access("PARTITIONKEY"(+)="PARTITIONKEY" AND "SERNO"="ROWSERNO"(+))
4 - filter("STGENERAL"='POST')
5 - access("BATCHSERNO"=321356)
7 - filter("RECORDTYPE"(+)='TRXN' AND "SYSTEMTYPE"(+)='O' AND "SYSTEMNAME"(+)='online')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 14:53
    #39373004
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров Андрей:-)

Быстрый запрос:

Медленный:


Ты сравниваешь часы с трусами.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SQL> select  *
  2    from  dept d left outer join emp e
  3          on d.deptno = e.deptno and e.sal < 1000
  4    where d.deptno > 20
  5  /

DEPTNO DNAME          LOC                EMPNO ENAME   JOB         MGR HIREDATE    SAL       COMM DEPTNO
------ -------------- ------------- ---------- ------- --------- ----- --------- ----- ---------- ------
    30 SALES          CHICAGO             7900 JAMES   CLERK      7698 03-DEC-81   950                30
    40 OPERATIONS     BOSTON

SQL> select  *
  2    from  dept d left outer join emp e
  3          on d.deptno = e.deptno
  4    where d.deptno > 20
  5       and e.sal < 1000
  6  /

DEPTNO DNAME          LOC                EMPNO ENAME   JOB         MGR HIREDATE    SAL       COMM DEPTNO
------ -------------- ------------- ---------- ------- --------- ----- --------- ----- ---------- ------
    30 SALES          CHICAGO             7900 JAMES   CLERK      7698 03-DEC-81   950                30

SQL>



Как только ты в LEFT JOIN переносишь условие пo правой таблице из фрaзы ON в WHERE ты получаешь, в общем случае, другой результат. Поcему сравнивать планы бессмысленно. Посмотри на плaны - как только оптимизатор находит условие пo правой таблице в WHERE он х...рит OUTER ( HASH JOIN OUTER в "медленном" и HASH JOIN в "быстpом") что дает возможность использовать bloom filter (:BF0000) через PART JOIN FILTER в "быстpом" по сравнению с PARTITION LIST ALL в "медленном". Но все-это по-барабану ибо имеем два неэквивалентных SELECT.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 14:59
    #39373011
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
SYПоcему сравнивать планы бессмысленно
сам план - да, но Predicate Information дает информацию для размышления.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 15:06
    #39373017
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Ну да, все логично.
Ну так а как мне написать именно с Left Join чтобы оно делало: PART JOIN FILTER?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 15:41
    #39373063
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров АндрейНу да, все логично.
Ну так а как мне написать именно с Left Join чтобы оно делало: PART JOIN FILTER?

Попробовать OUTER через UNION ALL:

Код: 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.
with x as (
            select  s.serno,
                    s.status,
                    s.authserno
              from  ctransactions t,
                    interfacerecordstatus s
              where t.serno = s.rowserno 
                and s.partitionkey = t.partitionkey
                and s.recordtype = 'TRXN'
                and s.systemname = 'online'
                and s.systemtype = 'O'
                and t.batchserno = 321356
                and t.stgeneral = 'POST'
           union all
            select  null serno,
                    null status,
                    null authserno
              from  ctransactions t
              where t.batchserno = 321356
                and t.stgeneral = 'POST'
                and not exists(
                               select  1
                                 from  interfacerecordstatus s
                                 where t.serno = s.rowserno 
                                   and s.partitionkey = t.partitionkey
                                   and s.recordtype = 'TRXN'
                                   and s.systemname = 'online'
                                   and s.systemtype = 'O'
                              )
          )
select  count(*),
        nvl(sum(case when NVL(s.serno, 0) > 0 then 1 else 0 end),0),
        nvl(sum(case when NVL(s.status, 'XXXX') in ('S2NT') then 1 else 0 end),0),
        nvl(sum(case when NVL(s.status, 'XXXX') in ('FAIL') then 1 else 0 end),0),
        nvl(sum(case when NVL(s.status, 'XXXX') in ('SUCC', 'FAIL') then 1 else 0 end),0),
        nvl(sum(case when NVL(s.status, 'XXXX') = 'SUCC' and s.authserno > 0 then 1 else 0 end),0)
  from  x
/



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 16:09
    #39373092
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Понятно.
Ну так по сути весь смысл партицирования таблиц убивается в Left Join чтоли?
Как то это очень странно.

>> HASH JOIN в "быстpом") что дает возможность использовать bloom filter (:BF0000) через PART JOIN FILTER в "быстpом" по сравнению с PARTITION LIST ALL в "медленном".

Как то у меня не вяжется что скрещивание PARTITION LIST JOIN-FILTER и HASH JOIN OUTER не работает. Т.е. оптимизатор считает что если в partitionkey будет Null то придется сканить все партишены (даже если они в разных файлах или на разных дисках)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 16:15
    #39373102
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров Андрей,

Inner join берет из левой только если есть совпадение с правой. Посeму можно отфильтровать partitions. Left join берет из левой даже если нет в правой. Посeму и фильтровать partitions через left join врядли получится.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 16:16
    #39373105
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Получится если поставить NVL :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 16:22
    #39373114
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Петров АндрейПолучится если поставить NVL :-)

В oбщем случае это не эквивалентно:

Код: 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.
SQL> select  *
  2    from  dept d left outer join emp e
  3          on d.deptno = e.deptno and e.comm < 1000
  4    where d.deptno > 20
  5  /

DEPTNO DNAME          LOC                EMPNO ENAME   JOB         MGR HIREDATE    SAL       COMM DEPTNO
------ -------------- ------------- ---------- ------- --------- ----- --------- ----- ---------- ------
    30 SALES          CHICAGO             7499 ALLEN   SALESMAN   7698 20-FEB-81  1600        300     30
    30 SALES          CHICAGO             7521 WARD    SALESMAN   7698 22-FEB-81  1250        500     30
    30 SALES          CHICAGO             7844 TURNER  SALESMAN   7698 08-SEP-81  1500          0     30
    40 OPERATIONS     BOSTON

SQL> select  *
  2    from  dept d left outer join emp e
  3          on d.deptno = e.deptno
  4    where d.deptno > 20
  5      and nvl(e.comm,0) < 1000
  6  /

DEPTNO DNAME          LOC                EMPNO ENAME   JOB         MGR HIREDATE    SAL       COMM DEPTNO
------ -------------- ------------- ---------- ------- --------- ----- --------- ----- ---------- ------
    30 SALES          CHICAGO             7499 ALLEN   SALESMAN   7698 20-FEB-81  1600        300     30
    30 SALES          CHICAGO             7521 WARD    SALESMAN   7698 22-FEB-81  1250        500     30
    30 SALES          CHICAGO             7698 BLAKE   MANAGER    7839 01-MAY-81  2850                30
    30 SALES          CHICAGO             7844 TURNER  SALESMAN   7698 08-SEP-81  1500          0     30
    30 SALES          CHICAGO             7900 JAMES   CLERK      7698 03-DEC-81   950                30
    40 OPERATIONS     BOSTON

6 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 16:35
    #39373125
Петров Андрей
Петров Андрей
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
Я про

Код: plsql
1.
2.
3.
4.
select  *
    from  dept d left outer join emp e
          on d.deptno = e.deptno and nvl(e.comm,0) < 1000
    where d.deptno > 20
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.12.2016, 16:38
    #39373130
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Условие в Left Join VS Where
SY
В oбщем случае это не эквивалентно:


Ты можешь использовать NVL если поля s.recordtype, s.systemname и s.systemtype определены как NOT NULL:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Select count(*),
                    NVL(sum(case when NVL(s.serno, 0) > 0 then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('S2NT') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') in ('SUCC', 'FAIL') then 1 else 0 end),0),
                    NVL(sum(case when NVL(s.status, 'XXXX') = 'SUCC' and s.authserno > 0 then 1 else 0 end),0)
               from ctransactions t
               left outer join interfacerecordstatus s on t.serno = s.rowserno and s.partitionkey = t.partitionkey
              where t.batchserno = 321356
                and t.stgeneral = 'POST'
                and nvl(s.recordtype,'TRXN') = 'TRXN' and nvl(s.systemname,'online') = 'online' and nvl(s.systemtype,'O') = 'O'



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Условие в Left Join VS Where / 20 сообщений из 20, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 551ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1886765]:
 0ms
get settings:
 10ms
get forum list:
 20ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 192ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 64ms
get tp. blocked users:
 1ms
others: 242ms
total:  551ms
x
x
Закрыть


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