Thanks for suggestion.
I tried various settings , in sql:
Original Message:
Sent: Fri May 30, 2025 10:01 AM
From: Art Kagel
Subject: Query using temp table is much faster then join/subquery
GW:
First step: Change OPTCOMPIND in your ONCONFIG file from 2 to 0:
onmode -wf OPTCOMPIND=0
Then try running the original query again. That query is taking 40sec and over 37 of those seconds are being consumed to build and query a hash table for a hash join. Changing the OPTCOMPIND will force the optimizer to avoid using hash joins which should make a significant difference.
Next, I notice that some of the estimated rows for various steps in the sqlexplain output are vastly different from the actual rows produced by that step. That likely means that your data distributions are either out-of-date or insufficiently detailed for the optimizer to make good decisions. I STRONGLY suggest that you run my dostats utility on at least those tables involved in the query and try to run it again. I suspect that that will be the final nail in this problem.
If you do not have my dostats utility go to my web site (My Utilities
| ASK Database Management | remove preview |
 | | My Utilities | | Utils2_ak is my primary package of utilities. This is the May 18, 2025 release. Included: dbping.ec - Tests connections and reports connection time as well as the actual host and... | | View this on ASK Database Management > |
|
|
) and download my utils2_ak package (free & open source), build it (on Linux just unzip the archive and type "make" - for other platforms there are comments in the Makefile and in the myschema.mk.norcs file in the myschema.d subdirectory and notes in the Building text file).
Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Fri May 30, 2025 04:04 AM
From: G W
Subject: Query using temp table is much faster then join/subquery
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
------------------------------