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

Откуда:
Сообщений: 2539
Доброго дня господа !

Возникла проблема

select  A.*
  from <Основная таблица> A
    left join <[b]Таблица 2= 50 записей[/b]> B
     on  (B.F0='B' and B.F1=A.F1)
    .......................................... 
    .......................................... 
    .......................................... 
    left join <[b]Таблица 2= 50 записей[/b]> С
     on  (C.F0='C' and C.F1=A.F1)

Запрос работает нормально

На седьмом использовании Left-а крокозябримо план оптимизации
зависло выполнение.
Left join неоднократно присоединял одну и туже таблицу (но с разными ключами)


select  A.*
  from <Основная таблица> A
    left join <[b]Таблица 2= 50 записей[/b]> B
     on  (B.F0='B' and B.F1=A.F1)
    .......................................... 
    .......................................... 
    .......................................... 
    left join <[b]Таблица 2= 50 записей[/b]> С
     on  (C.F0='C' and C.F1=A.F1)

    left join <[b]Таблица 2= 50 записей[/b]> D
     on  (D.F0='D' and D.F1=A.F1)


Кто-то сталкивался с таким, как побороть ...

Заранее благодарен !
1 дек 15, 12:45    [18496797]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
пывот
Guest
HOME_X
Запрос работает нормально
ну не дается sql, не смог научиться задавать воопросы, кривые руки - не мучайся выдумыванием поводов, пойди в грузчики или продавцы.
1 дек 15, 12:53    [18496850]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
HOME_X
Member

Откуда:
Сообщений: 2539
пывот,

По сути прошу Вас.
Какие доп. информация Вам необходима ?
На N-ом присоединении одной и той же таблицы наблюдаеться зависание ?
Почему ? План приложить ?
1 дек 15, 13:14    [18496961]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
ы!
Guest
HOME_X
Доброго дня господа !
....
Запрос работает нормально
На седьмом использовании Left-а крокозябримо план оптимизации
зависло выполнение.
....

Из вашего вопроса - очевидно, оракл больше 7 left join-ов не делает.
Вы хотя бы номер ошибки сказали... ну или что значит "крокозябримо план оптимизации"
Что оптимизируете? Что за план?...
1 дек 15, 13:25    [18497025]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
HOME_X
пывот,

По сути прошу Вас.
Какие доп. информация Вам необходима ?
На N-ом присоединении одной и той же таблицы наблюдаеться зависание ?
Почему ? План приложить ?

для начала:
версия сервера и (работающий) тесткейс
1 дек 15, 13:33    [18497085]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
HOME_X
Member

Откуда:
Сообщений: 2539
ы!
оракл больше 7 left join-ов не делает.


Я этого не утверждаю !
У меня возник этот факт, я пытаюсь его осознать
И немного озадачен, при обьеме в 50 записей - возникла проблема

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production


Хороший план - время выполнения порядка 40-50 сек
Plan hash value: 2836280742
 
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT                                       |                              |  9981 |    66M|   828K  (1)| 00:01:05 |
|   1 |  TEMP TABLE TRANSFORMATION                                   |                              |       |       |            |          |
|   2 |   LOAD AS SELECT                                             | SYS_TEMP_0FD9E4062_F269332B  |       |       |            |          |
|*  3 |    FILTER                                                    |                              |       |       |            |          |
|*  4 |     FILTER                                                   |                              |       |       |            |          |
|*  5 |      HASH JOIN                                               |                              | 16865 |   807K| 56742   (1)| 00:00:05 |
|   6 |       NESTED LOOPS                                           |                              | 18858 |   478K|  1037   (1)| 00:00:01 |
|   7 |        FAST DUAL                                             |                              |     1 |       |     2   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS BY INDEX ROWID BATCHED                   | T_CFA_STATUSOFCONTR_FACT     | 18858 |   478K|  1035   (1)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN                                     | I02_CFA_STATUS_FACT_DATE_FIN | 18428 |       |    35   (0)| 00:00:01 |
|* 10 |       TABLE ACCESS FULL                                      | T_CFA_CONTRACT_FULL_ALL      |  2612K|    57M| 55697   (1)| 00:00:05 |
|  11 |   LOAD AS SELECT                                             | AA20                         |       |       |            |          |
|  12 |    NESTED LOOPS OUTER                                        |                              |  9981 |    66M|   770K  (1)| 00:01:01 |
|  13 |     VIEW                                                     |                              |  9981 |   604K|    19   (0)| 00:00:01 |
|  14 |      TABLE ACCESS FULL                                       | SYS_TEMP_0FD9E4062_F269332B  |  9981 | 99810 |    15   (0)| 00:00:01 |
|  15 |     VIEW                                                     | VW_LAT_2C52851A              |     1 |  6901 |    77   (0)| 00:00:01 |
|  16 |      NESTED LOOPS OUTER                                      |                              |     1 |   778 |    76   (0)| 00:00:01 |
|  17 |       NESTED LOOPS OUTER                                     |                              |     1 |   754 |    75   (0)| 00:00:01 |
|  18 |        NESTED LOOPS OUTER                                    |                              |     1 |   730 |    74   (0)| 00:00:01 |
|  19 |         NESTED LOOPS OUTER                                   |                              |     1 |   706 |    73   (0)| 00:00:01 |
|  20 |          NESTED LOOPS OUTER                                  |                              |     1 |   682 |    72   (0)| 00:00:01 |
|  21 |           NESTED LOOPS OUTER                                 |                              |     1 |   636 |    71   (0)| 00:00:01 |
|  22 |            NESTED LOOPS OUTER                                |                              |     1 |   592 |    70   (0)| 00:00:01 |
|  23 |             NESTED LOOPS OUTER                               |                              |     1 |   568 |    69   (0)| 00:00:01 |
|  24 |              NESTED LOOPS OUTER                              |                              |     1 |   544 |    68   (0)| 00:00:01 |
|* 25 |               HASH JOIN SEMI                                 |                              |     1 |   522 |    67   (0)| 00:00:01 |
|  26 |                NESTED LOOPS OUTER                            |                              |     1 |   460 |    34   (0)| 00:00:01 |
|  27 |                 NESTED LOOPS OUTER                           |                              |     1 |   420 |    32   (0)| 00:00:01 |
|  28 |                  NESTED LOOPS OUTER                          |                              |     1 |   409 |    30   (0)| 00:00:01 |
|  29 |                   NESTED LOOPS OUTER                         |                              |     1 |   384 |    28   (0)| 00:00:01 |
|  30 |                    NESTED LOOPS OUTER                        |                              |     1 |   358 |    27   (0)| 00:00:01 |
|  31 |                     NESTED LOOPS OUTER                       |                              |     1 |   313 |    24   (0)| 00:00:01 |
|  32 |                      NESTED LOOPS OUTER                      |                              |     1 |   279 |    20   (0)| 00:00:01 |
|* 33 |                       HASH JOIN OUTER                        |                              |     1 |   273 |    19   (0)| 00:00:01 |
|  34 |                        NESTED LOOPS OUTER                    |                              |     1 |   265 |    13   (0)| 00:00:01 |
|* 35 |                         TABLE ACCESS BY INDEX ROWID BATCHED  | T_CFA_CONTRACT_FULL_ALL      |     1 |    73 |     2   (0)| 00:00:01 |
|* 36 |                          INDEX RANGE SCAN                    | I01_CFA_CONTR_FULL_ALL_DEAL  |     1 |       |     2   (0)| 00:00:01 |
|  37 |                         VIEW PUSHED PREDICATE                |                              |     1 |   192 |    11   (0)| 00:00:01 |
|* 38 |                          FILTER                              |                              |       |       |            |          |
|  39 |                           SORT GROUP BY                      |                              |     1 |    47 |    10   (0)| 00:00:01 |
|* 40 |                            FILTER                            |                              |       |       |            |          |
|* 41 |                             HASH JOIN                        |                              |     1 |    47 |    10   (0)| 00:00:01 |
|  42 |                              NESTED LOOPS                    |                              |     1 |    41 |     6   (0)| 00:00:01 |
|  43 |                               TABLE ACCESS BY INDEX ROWID    | T_CFA_CONTRACT_FULL_ALL      |     1 |    12 |     3   (0)| 00:00:01 |
|* 44 |                                INDEX UNIQUE SCAN             | PK_CFA_CONTRACT_FULL_ALL     |     1 |       |     2   (0)| 00:00:01 |
|  45 | TCHED                         TABLE ACCESS BY INDEX ROWID BA | T_CFA_INSURANCE_FULL         |     1 |    29 |     3   (0)| 00:00:01 |
|* 46 |                                INDEX RANGE SCAN              | I01_INSURANCE_FULL           |     1 |       |     2   (0)| 00:00:01 |
|  47 |                              TABLE ACCESS FULL               | T_CFA_DICINSURANCETYPE_FULL  |    89 |   534 |     4   (0)| 00:00:01 |
|  48 |                        VIEW                                  |                              |     2 |    16 |     6   (0)| 00:00:01 |
|  49 |                         UNION-ALL                            |                              |       |       |            |          |
|  50 |                          FAST DUAL                           |                              |     1 |       |     2   (0)| 00:00:01 |
|  51 |                          FAST DUAL                           |                              |     1 |       |     2   (0)| 00:00:01 |
|  52 |                       TABLE ACCESS BY INDEX ROWID            | T_CFA_DIC_BAREME_FULL        |    16 |    96 |     1   (0)| 00:00:01 |
|* 53 |                        INDEX UNIQUE SCAN                     | I02_CFA_DIC_BAREME_FULL      |     1 |       |     0   (0)| 00:00:01 |
|  54 |                      TABLE ACCESS BY INDEX ROWID BATCHED     | T_CFA_STATUSOFCONTR_FACT     |     1 |    34 |     4   (0)| 00:00:01 |
|* 55 |                       INDEX RANGE SCAN                       | I03_CFA_STATUS_FACT_ID_CONTR |     1 |       |     2   (0)| 00:00:01 |
|* 56 |                     TABLE ACCESS BY INDEX ROWID BATCHED      | T_CFA_EXT_DISTRIB_DIC        |     1 |    45 |     3   (0)| 00:00:01 |
|* 57 |                      INDEX RANGE SCAN                        | I01_CFA_EXT_DISTRIB_ID_ED    |     2 |       |     1   (0)| 00:00:01 |
|  58 |                    TABLE ACCESS BY INDEX ROWID               | PM_FTP                       |     1 |    26 |     1   (0)| 00:00:01 |
|* 59 |                     INDEX UNIQUE SCAN                        | SYS_C0076229                 |     1 |       |     0   (0)| 00:00:01 |
|  60 |                   TABLE ACCESS BY INDEX ROWID                | T_KPS_DA_BY_COL              |     1 |    25 |     2   (0)| 00:00:01 |
|* 61 |                    INDEX UNIQUE SCAN                         | SYS_C0098285                 |     1 |       |     1   (0)| 00:00:01 |
|  62 |                  TABLE ACCESS BY INDEX ROWID                 | V_CFA_CONTRACT_DELTA         |     1 |    11 |     2   (0)| 00:00:01 |
|* 63 |                   INDEX UNIQUE SCAN                          | KLO                          |     1 |       |     1   (0)| 00:00:01 |
|  64 |                 TABLE ACCESS BY INDEX ROWID                  | PM_PRICING                   |     1 |    40 |     2   (0)| 00:00:01 |
|* 65 |                  INDEX UNIQUE SCAN                           | SYS_C00311212                |     1 |       |     1   (0)| 00:00:01 |
|  66 |                VIEW                                          | VW_NSO_1                     |  9981 |   604K|    33   (0)| 00:00:01 |
|* 67 |                 FILTER                                       |                              |       |       |            |          |
|* 68 |                  FILTER                                      |                              |       |       |            |          |
|  69 |                   VIEW                                       |                              |  9981 |   604K|    19   (0)| 00:00:01 |
|  70 |                    TABLE ACCESS FULL                         | SYS_TEMP_0FD9E4062_F269332B  |  9981 | 99810 |    15   (0)| 00:00:01 |
|  71 |               INLIST ITERATOR                                |                              |       |       |            |          |
|* 72 |                INDEX UNIQUE SCAN                             | SYS_C00312314                |     1 |    22 |     1   (0)| 00:00:01 |
|  73 |              TABLE ACCESS BY INDEX ROWID                     | PM_MARK                      |     1 |    24 |     1   (0)| 00:00:01 |
|* 74 |               INDEX UNIQUE SCAN                              | SYS_C00312314                |     1 |       |     0   (0)| 00:00:01 |
|  75 |             TABLE ACCESS BY INDEX ROWID                      | PM_MARK                      |     1 |    24 |     1   (0)| 00:00:01 |
|* 76 |              INDEX UNIQUE SCAN                               | SYS_C00312314                |     1 |       |     0   (0)| 00:00:01 |
|* 77 |            TABLE ACCESS BY INDEX ROWID BATCHED               | PM_EDIT                      |     1 |    44 |     1   (0)| 00:00:01 |
|* 78 |             INDEX RANGE SCAN                                 | PM_EDIT_TYPE                 |     4 |       |     0   (0)| 00:00:01 |
|* 79 |           TABLE ACCESS BY INDEX ROWID BATCHED                | PM_EDIT                      |     1 |    46 |     1   (0)| 00:00:01 |
|* 80 |            INDEX RANGE SCAN                                  | PM_EDIT_TYPE                 |    27 |       |     0   (0)| 00:00:01 |
|  81 |          TABLE ACCESS BY INDEX ROWID                         | PM_MARK                      |     1 |    24 |     1   (0)| 00:00:01 |
|* 82 |           INDEX UNIQUE SCAN                                  | SYS_C00312314                |     1 |       |     0   (0)| 00:00:01 |
|  83 |         TABLE ACCESS BY INDEX ROWID                          | PM_MARK                      |     1 |    24 |     1   (0)| 00:00:01 |
|* 84 |          INDEX UNIQUE SCAN                                   | SYS_C00312314                |     1 |       |     0   (0)| 00:00:01 |
|  85 |        TABLE ACCESS BY INDEX ROWID                           | PM_MARK                      |     1 |    24 |     1   (0)| 00:00:01 |
|* 86 |         INDEX UNIQUE SCAN                                    | SYS_C00312314                |     1 |       |     0   (0)| 00:00:01 |
|  87 |       TABLE ACCESS BY INDEX ROWID                            | PM_MARK                      |     1 |    24 |     1   (0)| 00:00:01 |
|* 88 |        INDEX UNIQUE SCAN                                     | SYS_C00312314                |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL)
   4 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CONTRACT_FULL_ALL','S_CFA_CONTRACT_FULL_ALL',3) IS NULL)
   5 - access("ID_CONTRACT_SAP"="ID_CONTRACT_SAP")
   8 - filter("ID_CONTRACT_SAP" IS NOT NULL)
   9 - access(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))>=TO_DATE(' 2015-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))<=TO_DATE(' 2015-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  10 - filter("ID_CONTRACT_SAP" IS NOT NULL AND "STATUS_OF_DOSSIER"<>1 AND "STATUS_OF_DOSSIER"<>2 AND 
              ("CREDIT_PROGRAM_NAME_DIC"='CAR' OR "CREDIT_PROGRAM_NAME_DIC"='CASH' OR "CREDIT_PROGRAM_NAME_DIC"='POS') AND "ID_BAREME"<>3692 AND 
              "ID_BAREME"<>3693 AND "ID_BAREME"<>3694 AND "STATUS_OF_DOSSIER"<>100 AND "STATUS_OF_DOSSIER"<>65 AND "STATUS_OF_DOSSIER"<>103)
  25 - access("ID_CONTRACT_SAP"="ID_CONTRACT_SAP")
  33 - access("M"."CREDIT_PROGRAM_NAME_DIC"(+)="CREDIT_PROGRAM_NAME_DIC")
  35 - filter("STATUS_OF_DOSSIER"<>1 AND "STATUS_OF_DOSSIER"<>2 AND ("CREDIT_PROGRAM_NAME_DIC"='CAR' OR 
              "CREDIT_PROGRAM_NAME_DIC"='CASH' OR "CREDIT_PROGRAM_NAME_DIC"='POS') AND "ID_BAREME"<>3692 AND "ID_BAREME"<>3693 AND 
              "ID_BAREME"<>3694 AND "STATUS_OF_DOSSIER"<>100 AND "STATUS_OF_DOSSIER"<>65 AND "STATUS_OF_DOSSIER"<>103)
  36 - access("ID_CONTRACT_SAP"="A"."ID_CONTRACT_SAP")
       filter("ID_CONTRACT_SAP" IS NOT NULL)
  38 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_INSURANCE_FULL','S_CFA_INSURANCE_FULL',3) IS NULL)
  40 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CONTRACT_FULL_ALL','S_CFA_CONTRACT_FULL_ALL',3) IS NULL AND 
              SYS_AUDIT(1,'ADBUS','V_CFA_DICINSURANCETYPE_FULL','S_CFA_DICINSURANCETYPE_FULL',3) IS NULL)
  41 - access("ID"="INSURANCE_TYPE_ID")
       filter("CREDIT_PROGRAM_NAME_DIC"<>'CAR' OR "ID_INSUR_PRODUCT"=1 OR "ID_INSUR_PRODUCT"=4)
  44 - access("ID_CONTRACT_RATANET"="ID_CONTRACT_RATANET")
  46 - access("ID_CONTRACT_RATANET"="ID_CONTRACT_RATANET")
       filter("ID_CONTRACT_RATANET"="ID_CONTRACT_RATANET")
  53 - access("BAREME_CODE"(+)="ID_BAREME")
       filter("BAREME_CODE"(+)<>3692 AND "BAREME_CODE"(+)<>3693 AND "BAREME_CODE"(+)<>3694)
  55 - access("ID_CONTRACT_SAP"(+)="ID_CONTRACT_SAP")
       filter("ID_CONTRACT_SAP"(+) IS NOT NULL)
  56 - filter("DATE_REQUEST">="DATE_BEG"(+) AND "DATE_REQUEST"<="DATE_END"(+))
  57 - access("ID_ED_RATANET"(+)="ID_ED_RATANET")
  59 - access("D"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "D"."KIND"(+)=CASE  WHEN ("RATE">=9) THEN 
              'annuity' ELSE 'linear' END  AND "D"."RATE"(+)=CASE  WHEN ("RATE">=9) THEN TRUNC("RATE")/100+CASE  WHEN ("RATE"=TRUNC("RATE")) THEN 
              0 WHEN ("RATE">TRUNC("RATE")+0.5) THEN 0.01 ELSE 0.005 END  ELSE 0 END  AND "D"."SPAN"(+)="DURATION_MONTH")
  61 - access("R"."DATE_REP"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND 
              "R"."DEAL_CONTRACT_NUMB"(+)="ID_CONTRACT_SAP")
  63 - access("Q"."ID_CONTRACT_RATANET"(+)="ID_CONTRACT_RATANET")
  65 - access("G"."DAY"(+)="DATE_REQUEST" AND "G"."TYPE"(+)="ID_CREDIT_TYPE" AND "G"."BAREME"(+)="ID_BAREME")
       filter("G"."BAREME"(+)<>3692 AND "G"."BAREME"(+)<>3693 AND "G"."BAREME"(+)<>3694)
  67 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CONTRACT_FULL_ALL','S_CFA_CONTRACT_FULL_ALL',3) IS NULL)
  68 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL AND 
              SYS_AUDIT(1,'ADBUS','V_CFA_EXT_DISTRIB_DIC','S_CFA_EXT_DISTRIB_DIC',3) IS NULL AND 
              SYS_AUDIT(1,'ADBUS','V_CFA_DIC_BAREME_FULL','S_CFA_DIC_BAREME_FULL',3) IS NULL)
  72 - access("F"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND ("F"."TYPE"(+)='VES_ALLO' OR 
              "F"."TYPE"(+)='VES_AUCHAN') AND "F"."KIND"(+)="NETWORK_NAMED")
  74 - access("W"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "W"."TYPE"(+)='NBI' AND 
              "W"."KIND"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND 
              (("RSEGMENT"=1) OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END )
  76 - access("S"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "S"."TYPE"(+)='AMOUNT' AND 
              "S"."KIND"(+)="NETWORK_NAMED")
  77 - filter("NETWORK_NAMED"="I"."NETWORK"(+) AND INSTR(','||"I"."LIST"(+)||',',','||TO_CHAR("ID_BAREME")||',')=0 AND 
              "I"."AMOUNT_END"(+)>=NVL("S"."VAL",0) AND "I"."AMOUNT_BEG"(+)<=NVL("S"."VAL",0) AND 
              "I"."DAY_BEG"(+)<=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND "I"."DAY_END"(+)>=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")))
  78 - access("I"."KIND"(+)='F2')
  79 - filter("NETWORK_NAMED"="E"."NETWORK"(+) AND "ID_BAREME"="E"."BAREME"(+) AND "E"."AMOUNT_END"(+)>=NVL("S"."VAL",0) AND 
              "E"."AMOUNT_BEG"(+)<=NVL("S"."VAL",0) AND "E"."DAY_BEG"(+)<=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND 
              "E"."DAY_END"(+)>=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND "E"."BAREME"(+)<>3692 AND "E"."BAREME"(+)<>3693 AND 
              "E"."BAREME"(+)<>3694)
  80 - access("E"."KIND"(+)='F1')
  82 - access("L"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "L"."TYPE"(+)='DISCOUNT' AND 
              "L"."KIND"(+)=CASE "CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND 
              (("RSEGMENT"=1) OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  WHEN 
              'POS' THEN 'POS'||CASE  WHEN (((NVL("TECHNOLOGY_OF_SALES",'')='POS') OR (NVL("TECHNOLOGY_OF_SALES",'')='Broker')) AND 
              ((NVL("NETWORK_NAMED",'')='Comfy') OR (NVL("NETWORK_NAMED",'')='Foxtrot') OR (NVL("NETWORK_NAMED",'')='Epicentr'))) THEN 
              ('_Broker_'||"NETWORK_NAMED") WHEN (((NVL("TECHNOLOGY_OF_SALES",'')='POS') OR (NVL("TECHNOLOGY_OF_SALES",'')='Broker')) AND 
              ((NVL("NETWORK_NAMED",'')='Eldorado') OR (NVL("NETWORK_NAMED",'')='Technopolis'))) THEN '_Broker_Eldorado' WHEN 
              ((NVL("TECHNOLOGY_OF_SALES",'')='POS') OR (NVL("TECHNOLOGY_OF_SALES",'')='Broker')) THEN '_Broker_Other' WHEN 
              (((NVL("TECHNOLOGY_OF_SALES",'')='NST') OR (NVL("TECHNOLOGY_OF_SALES",'')='Internet')) AND (NVL("NETWORK_NAMED",'')='ALLO')) THEN 
              '_All_ALLO' ELSE ('_'||NVL("TECHNOLOGY_OF_SALES",'')||'_All') END  ELSE 
              "CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND (("RSEGMENT"=1) OR 
              ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END ||'_All_All' END )
  84 - access("Z"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND 
              "Z"."TYPE"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND 
              (("RSEGMENT"=1) OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  AND 
              "Z"."KIND"(+)='NOT')
  86 - access("T"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND 
              "T"."TYPE"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND 
              (("RSEGMENT"=1) OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  AND 
              "T"."KIND"(+)="TECHNOLOGY_OF_SALES")
  88 - access("S"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND 
              "S"."TYPE"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND 
              (("RSEGMENT"=1) OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  AND 
              "S"."KIND"(+)="NETWORK_NAMED")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 5 Sql Plan Directives used for this statement


добавил еще одну связь PM_EDIT(50 записей) = J
              left join PM_EDIT I
                on (     L.DBEG between I.DAY_BEG and I.DAY_END
                     and 'F2'      = I.KIND
                     and L.NETWORK = I.NETWORK
                     and Instr(','||I.LIST||',',','||L.BAREME||',')=0
                     and L.AMOUNT_KEY between I.AMOUNT_BEG and I.AMOUNT_END
                   )
             left join PM_EDIT J
              on (    L.DBEG between J.DAY_BEG and J.DAY_END
                    and 'F3'      = J.KIND
                    and L.NETWORK = J.NETWORK
                    and L.AMOUNT_KEY between J.AMOUNT_BEG and J.AMOUNT_END
                  )


Крокозябренный план
Plan hash value: 3322924366
 
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                   | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT                                      |                              |  9981 |    66M|       |   908M  (1)| 19:42:38 |
|   1 |  TEMP TABLE TRANSFORMATION                                  |                              |       |       |       |            |          |
|   2 |   LOAD AS SELECT                                            | SYS_TEMP_0FD9E4065_F269332B  |       |       |       |            |          |
|*  3 |    FILTER                                                   |                              |       |       |       |            |          |
|*  4 |     FILTER                                                  |                              |       |       |       |            |          |
|*  5 |      HASH JOIN                                              |                              | 16865 |   807K|       | 56742   (1)| 00:00:05 |
|   6 |       NESTED LOOPS                                          |                              | 18858 |   478K|       |  1037   (1)| 00:00:01 |
|   7 |        FAST DUAL                                            |                              |     1 |       |       |     2   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS BY INDEX ROWID BATCHED                  | T_CFA_STATUSOFCONTR_FACT     | 18858 |   478K|       |  1035   (1)| 00:00:01 |
|*  9 |         INDEX RANGE SCAN                                    | I02_CFA_STATUS_FACT_DATE_FIN | 18428 |       |       |    35   (0)| 00:00:01 |
|* 10 |       TABLE ACCESS FULL                                     | T_CFA_CONTRACT_FULL_ALL      |  2612K|    57M|       | 55697   (1)| 00:00:05 |
|  11 |   LOAD AS SELECT                                            | AA20                         |       |       |       |            |          |
|  12 |    NESTED LOOPS OUTER                                       |                              |  9981 |    66M|       |   908M  (1)| 19:42:33 |
|  13 |     VIEW                                                    |                              |  9981 |   604K|       |    19   (0)| 00:00:01 |
|  14 |      TABLE ACCESS FULL                                      | SYS_TEMP_0FD9E4065_F269332B  |  9981 | 99810 |       |    15   (0)| 00:00:01 |
|  15 |     VIEW                                                    | VW_LAT_7BA066E9              |     1 |  6901 |       | 90993   (1)| 00:00:08 |
|* 16 |      HASH JOIN SEMI                                         |                              |     1 |   817 |       | 90992   (1)| 00:00:08 |
|* 17 |       HASH JOIN OUTER                                       |                              |     1 |   755 |       | 90958   (1)| 00:00:08 |
|  18 |        NESTED LOOPS OUTER                                   |                              |     1 |   563 |       |    33   (0)| 00:00:01 |
|  19 |         NESTED LOOPS OUTER                                  |                              |     1 |   539 |       |    32   (0)| 00:00:01 |
|  20 |          NESTED LOOPS OUTER                                 |                              |     1 |   515 |       |    31   (0)| 00:00:01 |
|  21 |           NESTED LOOPS OUTER                                |                              |     1 |   491 |       |    30   (0)| 00:00:01 |
|  22 |            NESTED LOOPS OUTER                               |                              |     1 |   467 |       |    29   (0)| 00:00:01 |
|* 23 |             HASH JOIN OUTER                                 |                              |     1 |   443 |       |    28   (0)| 00:00:01 |
|  24 |              NESTED LOOPS OUTER                             |                              |     1 |   435 |       |    22   (0)| 00:00:01 |
|  25 |               NESTED LOOPS OUTER                            |                              |     1 |   413 |       |    21   (0)| 00:00:01 |
|  26 |                NESTED LOOPS OUTER                           |                              |     1 |   367 |       |    20   (0)| 00:00:01 |
|  27 |                 NESTED LOOPS OUTER                          |                              |     1 |   323 |       |    19   (0)| 00:00:01 |
|  28 |                  NESTED LOOPS OUTER                         |                              |     1 |   284 |       |    18   (0)| 00:00:01 |
|  29 |                   NESTED LOOPS OUTER                        |                              |     1 |   260 |       |    17   (0)| 00:00:01 |
|  30 |                    NESTED LOOPS OUTER                       |                              |     1 |   234 |       |    16   (0)| 00:00:01 |
|  31 |                     NESTED LOOPS OUTER                      |                              |     1 |   194 |       |    14   (0)| 00:00:01 |
|  32 |                      NESTED LOOPS OUTER                     |                              |     1 |   169 |       |    12   (0)| 00:00:01 |
|  33 |                       NESTED LOOPS OUTER                    |                              |     1 |   163 |       |    11   (0)| 00:00:01 |
|  34 |                        NESTED LOOPS OUTER                   |                              |     1 |   152 |       |     9   (0)| 00:00:01 |
|  35 |                         NESTED LOOPS OUTER                  |                              |     1 |   107 |       |     6   (0)| 00:00:01 |
|* 36 |                          TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_CONTRACT_FULL_ALL      |     1 |    73 |       |     2   (0)| 00:00:01 |
|* 37 |                           INDEX RANGE SCAN                  | I01_CFA_CONTR_FULL_ALL_DEAL  |     1 |       |       |     2   (0)| 00:00:01 |
|  38 |                          TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_STATUSOFCONTR_FACT     |     1 |    34 |       |     4   (0)| 00:00:01 |
|* 39 |                           INDEX RANGE SCAN                  | I03_CFA_STATUS_FACT_ID_CONTR |     1 |       |       |     2   (0)| 00:00:01 |
|* 40 |                         TABLE ACCESS BY INDEX ROWID BATCHED | T_CFA_EXT_DISTRIB_DIC        |     1 |    45 |       |     3   (0)| 00:00:01 |
|* 41 |                          INDEX RANGE SCAN                   | I01_CFA_EXT_DISTRIB_ID_ED    |     2 |       |       |     1   (0)| 00:00:01 |
|  42 |                        TABLE ACCESS BY INDEX ROWID          | V_CFA_CONTRACT_DELTA         |     1 |    11 |       |     2   (0)| 00:00:01 |
|* 43 |                         INDEX UNIQUE SCAN                   | KLO                          |     1 |       |       |     1   (0)| 00:00:01 |
|  44 |                       TABLE ACCESS BY INDEX ROWID           | T_CFA_DIC_BAREME_FULL        |    16 |    96 |       |     1   (0)| 00:00:01 |
|* 45 |                        INDEX UNIQUE SCAN                    | I02_CFA_DIC_BAREME_FULL      |     1 |       |       |     0   (0)| 00:00:01 |
|  46 |                      TABLE ACCESS BY INDEX ROWID            | T_KPS_DA_BY_COL              |     1 |    25 |       |     2   (0)| 00:00:01 |
|* 47 |                       INDEX UNIQUE SCAN                     | SYS_C0098285                 |     1 |       |       |     1   (0)| 00:00:01 |
|  48 |                     TABLE ACCESS BY INDEX ROWID             | PM_PRICING                   |     1 |    40 |       |     2   (0)| 00:00:01 |
|* 49 |                      INDEX UNIQUE SCAN                      | SYS_C00311212                |     1 |       |       |     1   (0)| 00:00:01 |
|  50 |                    TABLE ACCESS BY INDEX ROWID              | PM_FTP                       |     1 |    26 |       |     1   (0)| 00:00:01 |
|* 51 |                     INDEX UNIQUE SCAN                       | SYS_C0076229                 |     1 |       |       |     0   (0)| 00:00:01 |
|  52 |                   TABLE ACCESS BY INDEX ROWID               | PM_MARK                      |     1 |    24 |       |     1   (0)| 00:00:01 |
|* 53 |                    INDEX UNIQUE SCAN                        | SYS_C00312314                |     1 |       |       |     0   (0)| 00:00:01 |
|* 54 |                  TABLE ACCESS BY INDEX ROWID BATCHED        | PM_EDIT                      |     1 |    39 |       |     1   (0)| 00:00:01 |
|* 55 |                   INDEX RANGE SCAN                          | PM_EDIT_TYPE                 |     2 |       |       |     0   (0)| 00:00:01 |
|* 56 |                 TABLE ACCESS BY INDEX ROWID BATCHED         | PM_EDIT                      |     1 |    44 |       |     1   (0)| 00:00:01 |
|* 57 |                  INDEX RANGE SCAN                           | PM_EDIT_TYPE                 |     4 |       |       |     0   (0)| 00:00:01 |
|* 58 |                TABLE ACCESS BY INDEX ROWID BATCHED          | PM_EDIT                      |     1 |    46 |       |     1   (0)| 00:00:01 |
|* 59 |                 INDEX RANGE SCAN                            | PM_EDIT_TYPE                 |    27 |       |       |     0   (0)| 00:00:01 |
|  60 |               INLIST ITERATOR                               |                              |       |       |       |            |          |
|* 61 |                INDEX UNIQUE SCAN                            | SYS_C00312314                |     1 |    22 |       |     1   (0)| 00:00:01 |
|  62 |              VIEW                                           |                              |     2 |    16 |       |     6   (0)| 00:00:01 |
|  63 |               UNION-ALL                                     |                              |       |       |       |            |          |
|  64 |                FAST DUAL                                    |                              |     1 |       |       |     2   (0)| 00:00:01 |
|  65 |                FAST DUAL                                    |                              |     1 |       |       |     2   (0)| 00:00:01 |
|  66 |             TABLE ACCESS BY INDEX ROWID                     | PM_MARK                      |     1 |    24 |       |     1   (0)| 00:00:01 |
|* 67 |              INDEX UNIQUE SCAN                              | SYS_C00312314                |     1 |       |       |     0   (0)| 00:00:01 |
|  68 |            TABLE ACCESS BY INDEX ROWID                      | PM_MARK                      |     1 |    24 |       |     1   (0)| 00:00:01 |
|* 69 |             INDEX UNIQUE SCAN                               | SYS_C00312314                |     1 |       |       |     0   (0)| 00:00:01 |
|  70 |           TABLE ACCESS BY INDEX ROWID                       | PM_MARK                      |     1 |    24 |       |     1   (0)| 00:00:01 |
|* 71 |            INDEX UNIQUE SCAN                                | SYS_C00312314                |     1 |       |       |     0   (0)| 00:00:01 |
|  72 |          TABLE ACCESS BY INDEX ROWID                        | PM_MARK                      |     1 |    24 |       |     1   (0)| 00:00:01 |
|* 73 |           INDEX UNIQUE SCAN                                 | SYS_C00312314                |     1 |       |       |     0   (0)| 00:00:01 |
|  74 |         TABLE ACCESS BY INDEX ROWID                         | PM_MARK                      |     1 |    24 |       |     1   (0)| 00:00:01 |
|* 75 |          INDEX UNIQUE SCAN                                  | SYS_C00312314                |     1 |       |       |     0   (0)| 00:00:01 |
|  76 |        VIEW                                                 |                              |  1981K|   362M|       | 90919   (1)| 00:00:08 |
|* 77 |         FILTER                                              |                              |       |       |       |            |          |
|  78 |          SORT GROUP BY                                      |                              |  1981K|    88M|   112M| 88790   (1)| 00:00:07 |
|* 79 |           FILTER                                            |                              |       |       |       |            |          |
|* 80 |            HASH JOIN                                        |                              |  2092K|    93M|       | 73845   (1)| 00:00:06 |
|  81 |             TABLE ACCESS FULL                               | T_CFA_DICINSURANCETYPE_FULL  |    89 |   534 |       |     4   (0)| 00:00:01 |
|* 82 |             HASH JOIN                                       |                              |  2097K|    82M|    77M| 73834   (1)| 00:00:06 |
|  83 |              TABLE ACCESS FULL                              | T_CFA_INSURANCE_FULL         |  1988K|    55M|       | 11092   (1)| 00:00:01 |
|  84 |              TABLE ACCESS FULL                              | T_CFA_CONTRACT_FULL_ALL      |  3805K|    43M|       | 55673   (1)| 00:00:05 |
|  85 |       VIEW                                                  | VW_NSO_1                     |  9981 |   604K|       |    33   (0)| 00:00:01 |
|* 86 |        FILTER                                               |                              |       |       |       |            |          |
|* 87 |         FILTER                                              |                              |       |       |       |            |          |
|  88 |          VIEW                                               |                              |  9981 |   604K|       |    19   (0)| 00:00:01 |
|  89 |           TABLE ACCESS FULL                                 | SYS_TEMP_0FD9E4065_F269332B  |  9981 | 99810 |       |    15   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL)
   4 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CONTRACT_FULL_ALL','S_CFA_CONTRACT_FULL_ALL',3) IS NULL)
   5 - access("ID_CONTRACT_SAP"="ID_CONTRACT_SAP")
   8 - filter("ID_CONTRACT_SAP" IS NOT NULL)
   9 - access(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))>=TO_DATE(' 2015-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))<=TO_DATE(' 2015-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  10 - filter("ID_CONTRACT_SAP" IS NOT NULL AND "STATUS_OF_DOSSIER"<>1 AND "STATUS_OF_DOSSIER"<>2 AND ("CREDIT_PROGRAM_NAME_DIC"='CAR' OR 
              "CREDIT_PROGRAM_NAME_DIC"='CASH' OR "CREDIT_PROGRAM_NAME_DIC"='POS') AND "ID_BAREME"<>3692 AND "ID_BAREME"<>3693 AND "ID_BAREME"<>3694 AND 
              "STATUS_OF_DOSSIER"<>100 AND "STATUS_OF_DOSSIER"<>65 AND "STATUS_OF_DOSSIER"<>103)
  16 - access("ID_CONTRACT_SAP"="ID_CONTRACT_SAP")
  17 - access("I"."ID_CONTRACT_RATANET"(+)="ID_CONTRACT_RATANET")
  23 - access("M"."CREDIT_PROGRAM_NAME_DIC"(+)="CREDIT_PROGRAM_NAME_DIC")
  36 - filter("STATUS_OF_DOSSIER"<>1 AND "STATUS_OF_DOSSIER"<>2 AND ("CREDIT_PROGRAM_NAME_DIC"='CAR' OR "CREDIT_PROGRAM_NAME_DIC"='CASH' OR 
              "CREDIT_PROGRAM_NAME_DIC"='POS') AND "ID_BAREME"<>3692 AND "ID_BAREME"<>3693 AND "ID_BAREME"<>3694 AND "STATUS_OF_DOSSIER"<>100 AND 
              "STATUS_OF_DOSSIER"<>65 AND "STATUS_OF_DOSSIER"<>103)
  37 - access("ID_CONTRACT_SAP"="A"."ID_CONTRACT_SAP")
       filter("ID_CONTRACT_SAP" IS NOT NULL)
  39 - access("ID_CONTRACT_SAP"(+)="ID_CONTRACT_SAP")
       filter("ID_CONTRACT_SAP"(+) IS NOT NULL)
  40 - filter("DATE_REQUEST">="DATE_BEG"(+) AND "DATE_REQUEST"<="DATE_END"(+))
  41 - access("ID_ED_RATANET"(+)="ID_ED_RATANET")
  43 - access("Q"."ID_CONTRACT_RATANET"(+)="ID_CONTRACT_RATANET")
  45 - access("BAREME_CODE"(+)="ID_BAREME")
       filter("BAREME_CODE"(+)<>3692 AND "BAREME_CODE"(+)<>3693 AND "BAREME_CODE"(+)<>3694)
  47 - access("R"."DATE_REP"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "R"."DEAL_CONTRACT_NUMB"(+)="ID_CONTRACT_SAP")
  49 - access("G"."DAY"(+)="DATE_REQUEST" AND "G"."TYPE"(+)="ID_CREDIT_TYPE" AND "G"."BAREME"(+)="ID_BAREME")
       filter("G"."BAREME"(+)<>3692 AND "G"."BAREME"(+)<>3693 AND "G"."BAREME"(+)<>3694)
  51 - access("D"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "D"."KIND"(+)=CASE  WHEN ("RATE">=9) THEN 'annuity' 
              ELSE 'linear' END  AND "D"."RATE"(+)=CASE  WHEN ("RATE">=9) THEN TRUNC("RATE")/100+CASE  WHEN ("RATE"=TRUNC("RATE")) THEN 0 WHEN 
              ("RATE">TRUNC("RATE")+0.5) THEN 0.01 ELSE 0.005 END  ELSE 0 END  AND "D"."SPAN"(+)="DURATION_MONTH")
  53 - access("S"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "S"."TYPE"(+)='AMOUNT' AND 
              "S"."KIND"(+)="NETWORK_NAMED")
  54 - filter("NETWORK_NAMED"="J"."NETWORK"(+) AND "J"."AMOUNT_END"(+)>=NVL("S"."VAL",0) AND "J"."AMOUNT_BEG"(+)<=NVL("S"."VAL",0) AND 
              "J"."DAY_BEG"(+)<=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND "J"."DAY_END"(+)>=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")))
  55 - access("J"."KIND"(+)='F3')
  56 - filter("NETWORK_NAMED"="I"."NETWORK"(+) AND INSTR(','||"I"."LIST"(+)||',',','||TO_CHAR("ID_BAREME")||',')=0 AND 
              "I"."AMOUNT_END"(+)>=NVL("S"."VAL",0) AND "I"."AMOUNT_BEG"(+)<=NVL("S"."VAL",0) AND 
              "I"."DAY_BEG"(+)<=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND "I"."DAY_END"(+)>=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")))
  57 - access("I"."KIND"(+)='F2')
  58 - filter("NETWORK_NAMED"="E"."NETWORK"(+) AND "ID_BAREME"="E"."BAREME"(+) AND "E"."AMOUNT_END"(+)>=NVL("S"."VAL",0) AND 
              "E"."AMOUNT_BEG"(+)<=NVL("S"."VAL",0) AND "E"."DAY_BEG"(+)<=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND 
              "E"."DAY_END"(+)>=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND "E"."BAREME"(+)<>3692 AND "E"."BAREME"(+)<>3693 AND 
              "E"."BAREME"(+)<>3694)
  59 - access("E"."KIND"(+)='F1')
  61 - access("F"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND ("F"."TYPE"(+)='VES_ALLO' OR 
              "F"."TYPE"(+)='VES_AUCHAN') AND "F"."KIND"(+)="NETWORK_NAMED")
  67 - access("L"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "L"."TYPE"(+)='DISCOUNT' AND "L"."KIND"(+)=CASE 
              "CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND (("RSEGMENT"=1) OR 
              ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  WHEN 'POS' THEN 'POS'||CASE  WHEN 
              (((NVL("TECHNOLOGY_OF_SALES",'')='POS') OR (NVL("TECHNOLOGY_OF_SALES",'')='Broker')) AND ((NVL("NETWORK_NAMED",'')='Comfy') OR 
              (NVL("NETWORK_NAMED",'')='Foxtrot') OR (NVL("NETWORK_NAMED",'')='Epicentr'))) THEN ('_Broker_'||"NETWORK_NAMED") WHEN 
              (((NVL("TECHNOLOGY_OF_SALES",'')='POS') OR (NVL("TECHNOLOGY_OF_SALES",'')='Broker')) AND ((NVL("NETWORK_NAMED",'')='Eldorado') OR 
              (NVL("NETWORK_NAMED",'')='Technopolis'))) THEN '_Broker_Eldorado' WHEN ((NVL("TECHNOLOGY_OF_SALES",'')='POS') OR 
              (NVL("TECHNOLOGY_OF_SALES",'')='Broker')) THEN '_Broker_Other' WHEN (((NVL("TECHNOLOGY_OF_SALES",'')='NST') OR 
              (NVL("TECHNOLOGY_OF_SALES",'')='Internet')) AND (NVL("NETWORK_NAMED",'')='ALLO')) THEN '_All_ALLO' ELSE 
              ('_'||NVL("TECHNOLOGY_OF_SALES",'')||'_All') END  ELSE "CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN 
              (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND (("RSEGMENT"=1) OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN 
              ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END ||'_All_All' END )
  69 - access("W"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND "W"."TYPE"(+)='NBI' AND 
              "W"."KIND"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND (("RSEGMENT"=1) 
              OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END )
  71 - access("Z"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND 
              "Z"."TYPE"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND (("RSEGMENT"=1) 
              OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  AND "Z"."KIND"(+)='NOT')
  73 - access("T"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND 
              "T"."TYPE"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND (("RSEGMENT"=1) 
              OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  AND 
              "T"."KIND"(+)="TECHNOLOGY_OF_SALES")
  75 - access("S"."DAY"(+)=LAST_DAY(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))) AND 
              "S"."TYPE"(+)="CREDIT_PROGRAM_NAME_DIC"||TO_CHAR(NVL("M"."SROK",NULL))||CASE  WHEN (("CREDIT_PROGRAM_NAME_DIC"='CASH') AND (("RSEGMENT"=1) 
              OR ("RSEGMENT"=2) OR ("RSEGMENT"=3))) THEN 'P' WHEN ("CREDIT_PROGRAM_NAME_DIC"='CASH') THEN 'D' ELSE '' END  AND 
              "S"."KIND"(+)="NETWORK_NAMED")
  77 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_INSURANCE_FULL','S_CFA_INSURANCE_FULL',3) IS NULL)
  79 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CONTRACT_FULL_ALL','S_CFA_CONTRACT_FULL_ALL',3) IS NULL AND 
              SYS_AUDIT(1,'ADBUS','V_CFA_DICINSURANCETYPE_FULL','S_CFA_DICINSURANCETYPE_FULL',3) IS NULL)
  80 - access("ID"="INSURANCE_TYPE_ID")
       filter("CREDIT_PROGRAM_NAME_DIC"<>'CAR' OR "ID_INSUR_PRODUCT"=1 OR "ID_INSUR_PRODUCT"=4)
  82 - access("ID_CONTRACT_RATANET"="ID_CONTRACT_RATANET")
  86 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_CONTRACT_FULL_ALL','S_CFA_CONTRACT_FULL_ALL',3) IS NULL)
  87 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL AND 
              SYS_AUDIT(1,'ADBUS','V_CFA_EXT_DISTRIB_DIC','S_CFA_EXT_DISTRIB_DIC',3) IS NULL AND 
              SYS_AUDIT(1,'ADBUS','V_CFA_DIC_BAREME_FULL','S_CFA_DIC_BAREME_FULL',3) IS NULL)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 5 Sql Plan Directives used for this statement


Заранее благодарен !
1 дек 15, 14:55    [18497700]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
HOME_X
Member

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

Благодарен что отозвались !

Версия
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production


Код вызова
begin
    spusb.p_explain_plan('
create table AA20 as 
with TABLEDAY    as (select Trunc(T.DREP,''MM'') DBEG,
                            Last_Day(T.DREP)   DEND
                       from (select To_Date(''01-10-2015'',''DD-MM-YYYY'') DREP
                               from DUAL
                            ) T        
                    ),
BASE as (select /*+ Materialize1 */
                A.ID_CONTRACT_SAP
           from ADBUS.V_CFA_STATUSOFCONTR_FACT A
          inner join TABLEDAY B
             on Trunc(A.DATE_FININSURANCE) between B.DBEG and B.DEND
           left join ADBUS.V_CFA_CONTRACT_FULL_ALL D
             on (D.ID_CONTRACT_SAP=A.ID_CONTRACT_SAP)
          where Not D.STATUS_OF_DOSSIER   in(''1'',''2'',''65'',''100'',''103'')
            and Not D.ID_BAREME           in(''3692'',''3693'',''3694'')
            and D.CREDIT_PROGRAM_NAME_DIC in(''CASH'',''POS'' ,''CAR'' )
        )                     
select /*+ NO_GATHER_OPTIMIZER_STATISTICS */
       B.*
  from BASE A
 left join P00061242.LIST_KREDIT B
    on (    B.ID_CONTRACT_SAP=A.ID_CONTRACT_SAP
        and B.ID_CONTRACT_SAP in (select * from BASE)
       )
'
);
end;


Код view - здесь необходимо соединение
left join PM_EDIT J
create or replace view list_kredit as
select
           M.ID_CONTRACT_SAP,
           M.SC,
           M.RN,
           M.DBEG,
           M.KIND,
           M.GROUP_ID,
           M.NETWORK,
           M.CHANNEL,
           M.TECHNOLOGY,
           M.CONST,
           M.BAREME,
           M.TOTAL,
           M.INSUR_TYPE,
           M.INSUR_CODE,
           M.INSUR_CPI_CP,
           M.INSUR_CLIENT,
           M.INSUR_CPI_FE,
           M.OPENING-M.OPENING_ADD*M.PARNTER_RATE          OPENING,
           M.PAYMENT_FEE+M.SMS_FEE*(1-M.PARNTER_RATE)  PAYMENT_FEE,
           M.PAYMENT_RATE,
           M.AMOUNT_LOAN,
           M.ANNUITY,
           M.SROK,
           M.OPENING_ADD,
           M.PARNTER_RATE,
           M.SMS_FEE,
           M.SUB0_RATE,
           M.SUB1_RATE,
           M.AGENT_RATE,
--           M.COMIS_REST,
           M.TOTAL*M.SUB0_RATE SUB0_AMOUNT,
           M.TOTAL*M.SUB1_RATE SUB1_AMOUNT,
           M.GRACE,
--           M.COMIS_RATE,
           M.DEALER,
           M.RESERVE,
           M.NBI_CONST,
--           Round(M.INSUR_REWARD,2)             INSUR_REWARD,
           Round(M.INSUR_CLIENT*M.DEALER,2)    PAYMENT_DEALER,
--           Round(M.TOTAL*M.COMIS_RATE+M.COMIS_REST+M.OPENING_ADD*M.PARNTER_RATE+M.SMS_FEE*M.PARNTER_RATE*M.SROK,2) COMIS_AMOUNT,
           M.PAYMENT_DIS*Nvl(L.VAL,1)         PAYMENT_DISCONT,
           Case when M.KIND like 'CAR%' then M.AMOUNT_LOAN-(M.OPENING-M.OPENING_ADD*M.PARNTER_RATE)
                else M.INSUR_CLIENT+Round(M.TOTAL*(1-M.SUB0_RATE),2) end PAYMENT_SUB0,
           Case when M.KIND like 'CAR%' then M.AMOUNT_LOAN-(M.OPENING-M.OPENING_ADD*M.PARNTER_RATE)
               else M.INSUR_CLIENT+M.TOTAL                +(M.OPENING-M.OPENING_ADD*M.PARNTER_RATE)  end PAYMENT_SUB0_APR,
--           M.ANNUITY+Case when M.GRACE=0           then M.PAYMENT_FEE  else 0 end +
--           (M.TOTAL +Case when M.NETWORK='Foxtrot' then M.INSUR_CLIENT else 0 end)*M.SUB1_RATE PAYMENT_SUB1,
           M.ANNUITY+Case when M.GRACE=0           then M.PAYMENT_FEE  else 0 end              PAYMENT_SUB1_APR,
           M.ANNUITY PAYMENT_GRACE,
           M.RATE_ACTIVE,
           M.FTP_RATE,
           Irr(Null,Null,M.AMOUNT_LOAN-M.PAYMENT_DIS,
                         M.ANNUITY,
                         M.GRACE,
                         M.ANNUITY,
                         M.SROK,
                         M.ANNUITY
              ) IRR_SC,
           M.R_FTP,
           M.R_PRICING
      from (
            select 
                   L.*,
                   Case when L.KIND='POS' then 'POS'||
                                                      Case when Nvl(L.TECHNOLOGY,'') in('POS','Broker')   and Nvl(L.NETWORK,'') in('Comfy','Foxtrot','Epicentr') then '_Broker_'||L.NETWORK
                                                           when Nvl(L.TECHNOLOGY,'') in('POS','Broker')   and Nvl(L.NETWORK,'') in('Eldorado','Technopolis')     then '_Broker_Eldorado'
                                                           when Nvl(L.TECHNOLOGY,'') in('POS','Broker')                                                          then '_Broker_Other'
                                                           when Nvl(L.TECHNOLOGY,'') in('NST','Internet') and Nvl(L.NETWORK,'') in('ALLO')                       then '_All_ALLO'
                                                           else                                                                                                       '_'||Nvl(L.TECHNOLOGY,'')||'_All'
                                                      end
                        else L.KIND||'_All_All'
                   end GROUP_ID,
                   Coalesce(S.VAL,T.VAL,Z.VAL) RESERVE,
                   Nvl(W.VAL,0)                NBI_CONST,
                   Nvl(E.VAL,L.SUB1_RATI)      SUB1_RATE
--                   Nvl(Nullif(Nvl(I.VAL               ,0)+Nvl(                                   Y.VAL,0),0),       L.COMIS) COMIS_RATE
--                   Nvl(Nullif(Nvl(L.INSUR_CLIENT*Q.VAL,0)+Nvl(L.INSUR_CPI_PREMIUM-L.INSUR_CLIENT*X.VAL,0),0),L.INSUR_CPI_CP) INSUR_REWARD
--                   Nvl(J.VAL,0)+Round(Nvl(R.VAL,0)*Nvl(L.AMOUNT_LOAN/Nullif(L.AMOUNT_KEY,0),0),2) COMIS_REST
              from (
                     select B.ID_CONTRACT_SAP,
                            B.ID_CONTRACT_SAP||Nvl2(M.SROK,'/'||M.SROK,'')  SC,
                            B.ID_CONTRACT_RATANET                      RN,
                            Trunc(A.DATE_FININSURANCE)                 DBEG,
                            B.CREDIT_PROGRAM_NAME_DIC||Nvl(M.SROK,'')||
                            Case when B.CREDIT_PROGRAM_NAME_DIC='CASH' and B.RSEGMENT in(1,2,3) then 'P'
                                 when B.CREDIT_PROGRAM_NAME_DIC='CASH'                          then 'D'
                                                                                                else ''
                            end  KIND,
                            B.ID_CREDIT_TYPE                          CONST,
                            B.ID_BAREME                               BAREME,
                            B.TOTAL_PRICE-B.DOWN_PAYMENT              TOTAL,
                            B.OPENING_FEE_PAYMENT                     OPENING,
                            B.MONTHLY_FEE_PAYMENT                     PAYMENT_FEE,
                            B.MONTHLY_FEE_RATE                        PAYMENT_RATE,
                            B.AMOUNT_OF_LOAN                          AMOUNT_LOAN,
                            Nvl(S.VAL,0)                              AMOUNT_KEY,
                            Nvl(F.VAL,0)                              AMOUNT_VES,
                            Case when B.RATE=0 then 0.0001 else B.RATE end/100 RATE_ACTIVE,
                            Nvl(B.GRACE_PERIOD,0)                     GRACE,
                            Nvl(Q.OPENING_FEE_PAYMENT_ADDITIONAL,0)   OPENING_ADD,
                            Nvl(Q.SMS_FEE,0)                          SMS_FEE,
                            Nvl(K.PARTNER_PERCENT,0)                  PARNTER_RATE,
                            Least(Nvl(M.SROK,B.DURATION_MONTH),B.DURATION_MONTH) SROK,
                            Round(B.AMOUNT_OF_LOAN*(B.RATE/1200*Power((1+B.RATE/1200),Least(Nvl(M.SROK,B.DURATION_MONTH),B.DURATION_MONTH))  )
                                                       /NullIf((Power((1+B.RATE/1200),Least(Nvl(M.SROK,B.DURATION_MONTH),B.DURATION_MONTH))-1),0),2
                                 ) ANNUITY,
                            C.NETWORK_NAMED                           NETWORK,
                            C.CHANNEL_OF_SALES                        CHANNEL,
                            C.TECHNOLOGY_OF_SALES                     TECHNOLOGY,
                            I.INSURANCE_MONTHLY_PAYMENT_TYPE          INSUR_TYPE,
                            I.CODE_OF_INSURANCE_PROG                  INSUR_CODE,
                            I.ID_INSUR_PRODUCT                        INSUR_PRODUCT,
                            Nvl(I.INSURANCE_COMMISSION_PREMIUM,0)     INSUR_CPI_PREMIUM,
                            Nvl(Nvl2(LTrim(I.INSURANCE_MONTHLY_PAYMENT_TYPE,'N'),I.INSURANCE_MONTHLY_PAYMENT,INSURANCE_PAYMENT),0) INSUR_CLIENT,
                            Nvl(Least(Case when I.INSURANCE_COMMISSION_PREMIUM=0 then Nvl(I.INSURANCE_PAYMENT,0)*Nvl(G.AGENT,0) else I.INSURANCE_COMMISSION_PREMIUM end,Case when Nvl(I.INSURANCE_PAYMENT,0)*Nvl(G.AGENT,0)=0 then I.INSURANCE_COMMISSION_PREMIUM else Nvl(I.INSURANCE_PAYMENT,0)*Nvl(G.AGENT,0) end)*Nvl2(LTrim(I.INSURANCE_MONTHLY_PAYMENT_TYPE,'N'),I.INSURANCE_MONTHLY_PAYMENT/Nullif(I.INSURANCE_PAYMENT,0),1),0) INSUR_CPI_CP,
                            Nvl(Least(Case when I.INSURANCE_COMMISSION_PREMIUM=0 then Nvl(I.INSURANCE_PAYMENT,0)*Nvl(G.AGENT,0) else I.INSURANCE_COMMISSION_PREMIUM end,Case when Nvl(I.INSURANCE_PAYMENT,0)*Nvl(G.AGENT,0)=0 then I.INSURANCE_COMMISSION_PREMIUM else Nvl(I.INSURANCE_PAYMENT,0)*Nvl(G.AGENT,0) end)*Nvl2(LTrim(I.INSURANCE_MONTHLY_PAYMENT_TYPE,'N'),I.INSURANCE_MONTHLY_PAYMENT/Nullif(I.INSURANCE_PAYMENT,0),0),0) INSUR_CPI_FE,
                            Nvl(G.SUB0,0)                             SUB0_RATE,
                            Nvl(G.SUB1,0)                             SUB1_RATI,
                            Nvl(G.COMIS,0)                            COMIS,
                            Nvl(G.AGENT,0)                            AGENT_RATE,
                            Nvl(G.DEALER,0)                           DEALER,
                            Nvl(D.VALUE,0)                            FTP_RATE,
                            Nvl(R.A_GROUP_RES_1_REST_GRN,0)           PAYMENT_DIS,
                            Nvl2(D.RowId,1,0)                         R_FTP,
                            Nvl2(G.RowId,1,0)                         R_PRICING
                       from ADBUS.V_CFA_CONTRACT_FULL_ALL B
                       left join ADBUS.V_CFA_STATUSOFCONTR_FACT A
                         on (A.ID_CONTRACT_SAP=B.ID_CONTRACT_SAP)
                       left join ADBUS.V_CFA_EXT_DISTRIB_DIC C
                         on (    C.ID_ED_RATANET = B.ID_ED_RATANET
                             and A.DATE_REQUEST between C.DATE_BEG and C.DATE_END
                            )
                       left join (select 
                                         A.ID_CONTRACT_RATANET,
                                         Max(A.CODE_OF_INSURANCE_PROG)         CODE_OF_INSURANCE_PROG,
                                         Max(A.INSURANCE_MONTHLY_PAYMENT_TYPE) INSURANCE_MONTHLY_PAYMENT_TYPE,
                                         Sum(A.INSURANCE_MONTHLY_PAYMENT)      INSURANCE_MONTHLY_PAYMENT,
                                         Sum(A.INSURANCE_PAYMENT)              INSURANCE_PAYMENT,
                                         Sum(A.INSURANCE_COMMISSION_PREMIUM)   INSURANCE_COMMISSION_PREMIUM,
                                         ListAgg(C.ID_INSUR_PRODUCT,',') within group (order by C.ID_INSUR_PRODUCT) ID_INSUR_PRODUCT
                                    from ADBUS.V_CFA_INSURANCE_FULL A
                                   inner join ADBUS.V_CFA_CONTRACT_FULL_ALL B
                                      on (B.ID_CONTRACT_RATANET=A.ID_CONTRACT_RATANET)
                                   inner join ADBUS.V_CFA_DICINSURANCETYPE_FULL C
                                      on (C.ID=A.INSURANCE_TYPE_ID)
                                   where Not(B.CREDIT_PROGRAM_NAME_DIC ='CAR' and Not C.ID_INSUR_PRODUCT in(1,4))
                                   group by A.ID_CONTRACT_RATANET
                                 ) I
                         on (I.ID_CONTRACT_RATANET=B.ID_CONTRACT_RATANET)
                       left join /*ADBUS.*/V_CFA_CONTRACT_DELTA Q
                         on (  Q.ID_CONTRACT_RATANET=B.ID_CONTRACT_RATANET)
                       left join ADBUS.V_CFA_DIC_BAREME_FULL K
                         on (  K.BAREME_CODE=B.ID_BAREME)
                       left join PM_PRICING G
                         on (    G.DAY   =A.DATE_REQUEST
                             and G.TYPE  =B.ID_CREDIT_TYPE
                             and G.BAREME=B.ID_BAREME
                            )
                       left join PM_FTP D
                         on (    D.DAY =Last_Day(Trunc(A.DATE_FININSURANCE))
                             and D.KIND=Case when B.RATE>=9 then 'annuity' else 'linear' end
                             and D.RATE=Case when B.RATE>=9 then Trunc(B.RATE)/100+Case when B.RATE=Trunc(B.RATE)     then 0
                                                                                        when B.RATE>Trunc(B.RATE)+0.5 then 0.01
                                                                                        else 0.005 end
                                             else 0 end
                             and D.SPAN=B.DURATION_MONTH
                            )
                       left join T_KPS_DA_BY_COL R
                         on (    R.DEAL_CONTRACT_NUMB=B.ID_CONTRACT_SAP
                             and R.DATE_REP=Last_Day(Trunc(A.DATE_FININSURANCE))
                            )
                       left join PM_MARK S
                         on (    S.DAY=Last_Day(Trunc(A.DATE_FININSURANCE))
                             and S.TYPE='AMOUNT'
                             and S.KIND=C.NETWORK_NAMED
                            )
                       left join PM_MARK F
                         on (    F.DAY=Last_Day(Trunc(A.DATE_FININSURANCE))
                             and F.TYPE in ('VES_ALLO','VES_AUCHAN')
                             and F.KIND=C.NETWORK_NAMED
                            )
                     left join (select 'CAR' CREDIT_PROGRAM_NAME_DIC,Null SROK from DUAL
                                 union all
                                select 'CAR' CREDIT_PROGRAM_NAME_DIC,  38 SROK from DUAL
                               ) M
                         on (M.CREDIT_PROGRAM_NAME_DIC=B.CREDIT_PROGRAM_NAME_DIC)
                      where Not B.STATUS_OF_DOSSIER   in('1','2','65','100','103')
                        and Not B.ID_BAREME           in('3692','3693','3694')
                        and B.CREDIT_PROGRAM_NAME_DIC in('CASH','POS' ,'CAR' )
                   ) L
              left join PM_MARK S
                on (    S.DAY =Last_Day(L.DBEG)
                    and S.TYPE=L.KIND
                    and S.KIND=L.NETWORK
                   )
              left join PM_MARK T
                on (    T.DAY =Last_Day(L.DBEG)
                    and T.TYPE=L.KIND
                    and T.KIND=L.TECHNOLOGY
                   )
              left join PM_MARK Z
                on (    Z.DAY =Last_Day(L.DBEG)
                    and Z.TYPE=L.KIND
                    and Z.KIND='NOT'
                   )
              left join PM_MARK W
                on (    W.DAY =Last_Day(L.DBEG)
                    and W.TYPE='NBI'
                    and W.KIND=L.KIND
                   )
              left join PM_EDIT E
                on (     L.DBEG between E.DAY_BEG and E.DAY_END
                     and 'F1'      = E.KIND
                     and L.NETWORK = E.NETWORK
                     and L.BAREME  = E.BAREME
                     and L.AMOUNT_KEY between E.AMOUNT_BEG and E.AMOUNT_END
                   )
              left join PM_EDIT I
                on (     L.DBEG between I.DAY_BEG and I.DAY_END
                     and 'F2'      = I.KIND
                     and L.NETWORK = I.NETWORK
                     and Instr(','||I.LIST||',',','||L.BAREME||',')=0
                     and L.AMOUNT_KEY between I.AMOUNT_BEG and I.AMOUNT_END
                   )
-- Здесь проблема
              left join PM_EDIT J
                on (    L.DBEG between J.DAY_BEG and J.DAY_END
                    and 'F3'      = J.KIND
                    and L.NETWORK = J.NETWORK
                    and L.AMOUNT_KEY between J.AMOUNT_BEG and J.AMOUNT_END
                   )
--              left join PM_EDIT Y
--                on (     L.DBEG between Y.DAY_BEG and Y.DAY_END
--                     and 'F4'        = Y.KIND
--                     and L.NETWORK   = Y.NETWORK
--                     and L.TECHNOLOGY= Y.TECHNOLOGY
--                     and L.AMOUNT_KEY between Y.AMOUNT_BEG and Y.AMOUNT_END
--                   )
--              left join PM_EDIT R
--                on (     L.DBEG between R.DAY_BEG and R.DAY_END
--                     and 'F5'      = R.KIND
--                     and L.NETWORK = R.NETWORK
--                     and L.AMOUNT_KEY between R.AMOUNT_BEG and R.AMOUNT_END
--                   )
--              left join PM_EDIT Q
--                on (     L.DBEG between Q.DAY_BEG and Q.DAY_END
--                     and 'F6'      = Q.KIND
--                     and L.NETWORK = Q.NETWORK
--                     and Instr(Q.LIST,L.INSUR_CODE)>0
--                     and L.AMOUNT_VES between Q.AMOUNT_BEG and Q.AMOUNT_END
--                   )
--              left join PM_EDIT X
--                on (     L.DBEG between X.DAY_BEG and X.DAY_END
--                     and 'F7'        = X.KIND
--                     and L.NETWORK   = X.NETWORK
--                     and L.TECHNOLOGY= X.TECHNOLOGY
--                     and L.AMOUNT_VES between X.AMOUNT_BEG and X.AMOUNT_END
--                   )
           ) M
      left join PM_MARK L
        on (    L.DAY=Last_Day(M.DBEG)
            and L.TYPE='DISCOUNT'
            and L.KIND=M.GROUP_ID
           );
1 дек 15, 15:02    [18497737]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
старпывот
Guest
HOME_X,

50 в седьмой степени это уже не 50 записей. а обойтись можно и одним джоином хоть на тысячу значений.
1 дек 15, 15:09    [18497799]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
HOME_X
Member

Откуда:
Сообщений: 2539
старпывот
HOME_X,

50 в седьмой степени это уже не 50 записей. а обойтись можно и одним джоином хоть на тысячу значений.


По разным ключам ? .... ну наверное !
И потом Case значение по полям в секции SELECT.... ? реализация будет громоздка (если выхода не будет)

Хотелось бы слышать обоснование - почему LEFT не работает, полагаю это более проффесионально.
1 дек 15, 15:21    [18497884]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
HOME_X
Member

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

почему изменился вложенность HASH JOIN SEMI ?
1 дек 15, 15:22    [18497901]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
Kamael
Member

Откуда: Алмата
Сообщений: 374
[quot HOME_X]HOME_X,

dynamic statistics used: dynamic sampling (level=2)

Соберите статистику
2 дек 15, 07:18    [18500658]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
HOME_X,

"И потом Case значение по полям в секции SELECT.... ? реализация будет громоздка (если выхода не будет)" - у тебя конечно не громоздкие 7 лефтджоинов... они то намного проще чем 1лефт джоин +pivot +case
P.S.
в очередной раз убеждаюсь что у меня всегда будет работа.... даже если каждый год будет появляться 100 гениев, на хлеб с красной икрой мне точно хватит, если человек с 2умя тысячами сообщений не понимает что пишет говнокод.
2 дек 15, 11:13    [18501602]     Ответить | Цитировать Сообщить модератору
 Re: Сколько раз можно сделать LEFT JOIN (сраННый вопрос)  [new]
O_O***
Guest
Ух ты жесть какая.
А, спрашивается, нахрена такой запрос?
Его надо переписывать. Как - это уж сами думайте.
7 дек 15, 17:54    [18525837]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить