Здравствуйте. Пожалуста помогите упростить этот запрос.
При left join -e таблиц s и s2 запрос очень долго срабатывает.
Хотя Таблица s и таблица s2 в отдельности срабатывают за 1-2 минуты.
| Код |
select rownum,
s.codep_subject_etl KOD,
s.nl_subject_etl IMYA,
DECODE(s.LOCKED, '0', 'OPEN', 'CLOSE') AS OPEN_CLOSEfrom,
NVL2(S2.ID_SUB_CODES, '1', '0') IS_BANK
from (select se.id_subject_etl,
se.codep_subject_etl,
se.nl_subject_etl,
se.locked
from dwh.det_subject_etl se
where se.id_subject_etl NOT IN
(select a.id_subject_cli_etl
from dwh.fct_transaction t, dwh.det_account a
where t.date_calculate between to_date(&DT1, 'DD.MM.YYYY') and
to_date(&DT2, 'DD.MM.YYYY')
and (t.id_account_deb = a.id_account OR
t.id_account_cred = a.id_account))) s
left join (SELECT F.ID_SUB_CODES, F.ID_SUBJECT_ETL
FROM DWH.CON_ASS_SUB_CODES F, DWH.DET_SUB_CODES DS
WHERE F.ID_SUB_CODES = DS.ID_SUB_CODES
AND DS.CODE_SUB_CODES = '6'
and sysdate between f.dt_open and f.dt_close) s2
ON S.ID_SUBJECT_ETL = S2.ID_SUBJECT_ETL
|
План запроса такой:
| Цитата |
SELECT STATEMENT, GOAL = CHOOSE Cost=18640080772 Cardinality=86574 Bytes=5281014
COUNT
FILTER
HASH JOIN OUTER Cost=5980 Cardinality=86574 Bytes=5281014
TABLE ACCESS FULL Object name=DET_SUBJECT_ETL Cost=628 Cardinality=9596 Bytes=479800
VIEW Object owner=ELNARA Cost=5087 Cardinality=1260956 Bytes=13870516
HASH JOIN Cost=5087 Cardinality=1260956 Bytes=44133460
TABLE ACCESS BY INDEX ROWID Object name=DET_SUB_CODES Cost=2 Cardinality=1 Bytes=8
INDEX RANGE SCAN Object name=AK_SUB_CODES Cost=1 Cardinality=1
TABLE ACCESS FULL Object name=CON_ASS_SUB_CODES Cost=5054 Cardinality=6304780 Bytes=170229060
CONCATENATION
FILTER
NESTED LOOPS Cost=107654 Cardinality=2547 Bytes=81504
TABLE ACCESS BY INDEX ROWID Object name=FCT_TRANSACTION Cost=5774 Cardinality=50940 Bytes=1018800
INDEX RANGE SCAN Object name=DATE_CALC_INDX Cost=280 Cardinality=91693
TABLE ACCESS BY INDEX ROWID Object name=DET_ACCOUNT Cost=2 Cardinality=1 Bytes=12
INDEX UNIQUE SCAN Object name=PK_DET_ACCOUNT Cost=1 Cardinality=1
FILTER
NESTED LOOPS Cost=107654 Cardinality=2547 Bytes=81504
TABLE ACCESS BY INDEX ROWID Object name=FCT_TRANSACTION Cost=5774 Cardinality=50940 Bytes=1018800
INDEX RANGE SCAN Object name=DATE_CALC_INDX Cost=280 Cardinality=91693
TABLE ACCESS BY INDEX ROWID Object name=DET_ACCOUNT Cost=2 Cardinality=1 Bytes=12
INDEX UNIQUE SCAN Object name=PK_DET_ACCOUNT Cost=1 Cardinality=1
|
|
