I have problem with query (duration 40s), when I use temp table is much faster (<2s).
Can I do anything to improve query? Using temporary tables is cumbersome.
Here is sqexplain, first for regular query, second with temp table:
QUERY: (OPTIMIZATION TIMESTAMP: 05-30-2025 09:38:28)
------
select
et_filename,
REPLACE(transfer.err_text, 'Export:','') Dateiname,
transfer.status Prio,
transfer.l_timestamp Export,
sftp.l_timestamp SCPExport
FROM et_transfer
LEFT JOIN gwy_test01 transfer ON transfer.prog = 'TransferFromHop' AND transfer.status > 0 and DATE(transfer.l_timestamp) >= TODAY
AND transfer.err_text like 'Export %' AND LOWER(REPLACE(transfer.err_text, 'Export: ','')) LIKE LOWER(et_filename || '_' || to_char(sysdate, '%Y%m%d') || '%' )
LEFT JOIN gwy_test01 sftp ON transfer.err_text = sftp.err_text
and sftp.prog = 'SFTPTransfer' AND sftp.status > 1 AND DATE(sftp.l_timestamp) = TODAY
WHERE et_check_hour <= (CURRENT::DATETIME HOUR TO HOUR)::char(2)::INTEGER
AND
(
transfer.l_timestamp IS NULL
OR
(
transfer.l_timestamp < CURRENT - 15 UNITS MINUTE
AND sftp.l_timestamp IS NULL
)
)
Estimated Cost: 2014028
Estimated # of Rows Returned: 1632265216
1) gwy.et_transfer: SEQUENTIAL SCAN
Filters: gwy.et_transfer.et_check_hour <= CURRENT year to fraction(3)::datetime hour to hour::char(2)::integer
2) gwy.transfer: INDEX PATH
(1) Index Name: gwy.gwy_6_idx1
Index Keys: prog status l_timestamp err_text (Key-First) (Serial, fragments: ALL)
Lower Index Filter: (gwy.transfer.prog = 'TransferFromHop' AND gwy.transfer.status > 0 )
Index Key Filters: (gwy.transfer.err_text LIKE 'Export %' )
ON-Filters:((((gwy.transfer.prog = 'TransferFromHop' AND gwy.transfer.status > 0 ) AND DATE (gwy.transfer.l_timestamp ) >= TODAY ) AND gwy.transfer.err_text LIKE 'Export %' ) AND LOWER(REPLACE (gwy.transfer.err_text , 'Export: ' , '' ) ) LIKE LOWER((((gwy.et_transfer.et_filename || '_' )|| TO_CHAR (SYSDATE year to fraction(5), '%Y%m%d' ) )|| '%' )) )
NESTED LOOP JOIN(LEFT OUTER JOIN)
3) gwy.sftp: INDEX PATH
(1) Index Name: gwy.gwy_5_idx1
Index Keys: prog status l_timestamp (Serial, fragments: ALL)
Lower Index Filter: (gwy.sftp.prog = 'SFTPTransfer' AND gwy.sftp.status > 1 )
ON-Filters:(((gwy.transfer.err_text = gwy.sftp.err_text AND gwy.sftp.prog = 'SFTPTransfer' ) AND gwy.sftp.status > 1 ) AND DATE (gwy.sftp.l_timestamp ) = TODAY )
DYNAMIC HASH JOIN (LEFT OUTER JOIN)
Dynamic Hash Filters: gwy.transfer.err_text = gwy.sftp.err_text
PostJoin-Filters:(gwy.transfer.l_timestamp IS NULL OR (gwy.sftp.l_timestamp IS NULL AND gwy.transfer.l_timestamp < CURRENT year to fraction(3)- interval( 15) minute(9) to minute ) )
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 et_transfer
t2 transfer
t3 sftp
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 171 171 171 00:00.00 9
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 0 21327 17683965 00:31.01 11425
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 171 145894 00:36.35 1957177
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 129359 22376 129359 00:00.35 13572
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 171 1632265216 129359 171 11 00:37.73 2014028
QUERY: (OPTIMIZATION TIMESTAMP: 05-30-2025 09:40:33)
------
select *
from gwy_test01 transfer
where
transfer.prog = 'TransferFromHop' AND transfer.status > 0 and DATE(transfer.l_timestamp) >= TODAY and transfer.err_text like 'Export %'
into temp gwytmp with no log
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) gwy.transfer: INDEX PATH
Filters: (((DATE (gwy.transfer.l_timestamp ) >= TODAY AND gwy.transfer.prog = 'TransferFromHop' ) AND gwy.transfer.err_text LIKE 'Export %' ) AND gwy.transfer.status > 0 )
(1) Index Name: gwy.gwy_3_idx3
Index Keys: l_timestamp (desc) (Serial, fragments: ALL)
(fragments might be eliminated at runtime because filter contains
runtime constants)
Upper Index Filter: gwy.transfer.l_timestamp >= EXTEND (TODAY ,year to second)
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 transfer
t2 gwytmp
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 1 00:00.00 4
type table rows_ins time
-----------------------------------
insert t2 0 00:00.00
QUERY: (OPTIMIZATION TIMESTAMP: 05-30-2025 09:40:33)
------
select
et_filename,
REPLACE(transfer.err_text, 'Export:','') Dateiname,
transfer.status Prio,
transfer.l_timestamp Export,
sftp.l_timestamp SCPExport
FROM et_transfer
LEFT JOIN gwytmp transfer ON LOWER(REPLACE(transfer.err_text, 'Export: ','')) LIKE LOWER(et_filename || '_' || to_char(sysdate, '%Y%m%d') || '%' )
LEFT JOIN gwy_test01 sftp ON transfer.err_text = sftp.err_text
and sftp.prog = 'SFTPTransfer' AND sftp.status > 1 AND DATE(sftp.l_timestamp) = TODAY
WHERE et_check_hour <= (CURRENT::DATETIME HOUR TO HOUR)::char(2)::INTEGER
AND
(
transfer.l_timestamp IS NULL
OR
(
transfer.l_timestamp < CURRENT - 15 UNITS MINUTE
AND sftp.l_timestamp IS NULL
)
)
Estimated Cost: 19202
Estimated # of Rows Returned: 1913148
1) gwy.et_transfer: SEQUENTIAL SCAN
Filters: gwy.et_transfer.et_check_hour <= CURRENT year to fraction(3)::datetime hour to hour::char(2)::integer
2) gwy.transfer: SEQUENTIAL SCAN (Serial, fragments: ALL)
ON-Filters:LOWER(REPLACE (gwy.transfer.err_text , 'Export: ' , '' ) ) LIKE LOWER((((gwy.et_transfer.et_filename || '_' )|| TO_CHAR (SYSDATE year to fraction(5), '%Y%m%d' ) )|| '%' ))
NESTED LOOP JOIN(LEFT OUTER JOIN)
3) gwy.sftp: INDEX PATH
(1) Index Name: gwy.gwy_5_idx1
Index Keys: prog status l_timestamp (Serial, fragments: ALL)
Lower Index Filter: (gwy.sftp.prog = 'SFTPTransfer' AND gwy.sftp.status > 1 )
ON-Filters:(((gwy.transfer.err_text = gwy.sftp.err_text AND gwy.sftp.prog = 'SFTPTransfer' ) AND gwy.sftp.status > 1 ) AND DATE (gwy.sftp.l_timestamp ) = TODAY )
DYNAMIC HASH JOIN (LEFT OUTER JOIN)
Dynamic Hash Filters: gwy.transfer.err_text = gwy.sftp.err_text
PostJoin-Filters:(gwy.transfer.l_timestamp IS NULL OR (gwy.sftp.l_timestamp IS NULL AND gwy.transfer.l_timestamp < CURRENT year to fraction(3)- interval( 15) minute(9) to minute ) )
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 et_transfer
t2 transfer
t3 sftp
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 171 171 171 00:00.00 9
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 0 1 0 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 171 171 00:00.00 356
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 129359 22376 129359 00:00.34 13572
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 171 1913148 129359 171 11 00:01.37 19202
------------------------------
G W
------------------------------