what are the best tuning ways when more than 2 Left outer joins?

I've to tune the below query.
SELECT A****N_DATES_STATIONS_ID,
A.STATION_MASTER_ID, A****N_DATES_ID,B.CALL_LETTERS, B.DMA_ID
FROM
CONTRACT****N_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
ON
A.STATION_MASTER_ID=B.STATION_MASTER_ID
WHERE
(A.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0' OR
B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')


When I searched for Access plan & Optimized query in DB2 command center, I found the below optimized query.

SELECT Q3.$C4 AS "RUN_DATES_STATIONS_ID", Q3.$C6 AS
"STATION_MASTER_ID", Q3.$C3 AS "RUN_DATES_ID", Q3.$C1
AS "CALL_LETTERS", Q3.$C0 AS "DMA_ID"
FROM
(SELECT Q2.DMA_ID, Q2.CALL_LETTERS, Q2.IBMSNAP_LOGMARKER,
Q1****N_DATES_ID, Q1****N_DATES_STATIONS_ID, Q1.IBMSNAP_LOGMARKER,
Q1.STATION_MASTER_ID
FROM CONTRACT****N_DATES_STATIONS AS Q1 LEFT OUTER JOIN
CUSTOMER.STATION_MASTER AS Q2 ON (Q1.STATION_MASTER_ID
= Q2.STATION_MASTER_ID)) AS Q3
WHERE ((Q3.$C5 = '1900-01-01-11.16.30.000000') OR (Q3.$C2
= '1900-01-01-11.16.30.000000'))

The query is not working and giving an error Q3.$c5, Q3.$c2 are not valid.
both should refer to IBMSNAP_LOGMARKER field for the two tables.

Can anyone suggest/improve the query?

I am not sure about the syntax problem, but anytime you have a query with an OR predicate, and different table columns are involved in the OR predicate, then sometimes a rewrite with UNION can dramatically improve the performance (so that the OR's are eliminated, or at least they refer to the same column).

I tried with UNION but it's taking more time than the original.

The given query is showing 11535 Timer's On and the UNION shows 16450 Timer On's in the Access Plan Graph.


Below is the query.

SELECT A****N_DATES_STATIONS_ID,
A.STATION_MASTER_ID, A****N_DATES_ID,B.CALL_LETTERS, B.DMA_ID
FROM
CONTRACT****N_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
ON
A.STATION_MASTER_ID=B.STATION_MASTER_ID
WHERE
(A.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0' OR
B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')

I don't see your UNION query.

11535 timerons is not a lot for a query of this type, and it may actually run faster is stats are not accurate. Did you actually run the query?

yes. the query with union is
SELECT A****N_DATES_STATIONS_ID,
A.STATION_MASTER_ID, A****N_DATES_ID,B.CALL_LETTERS, B.DMA_ID
FROM
CONTRACT****N_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
ON
A.STATION_MASTER_ID=B.STATION_MASTER_ID
WHERE
A.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0'
UNION
SELECT A****N_DATES_STATIONS_ID,
A.STATION_MASTER_ID, A****N_DATES_ID,B.CALL_LETTERS, B.DMA_ID
FROM
CONTRACT****N_DATES_STATIONS A LEFT OUTER JOIN CUSTOMER.STATION_MASTER B
ON
A.STATION_MASTER_ID=B.STATION_MASTER_ID
WHERE
B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')



I've also tried with sorting of the 2nd table
below is the query.

SELECT  A****N_DATES_STATIONS_ID,A.STATION_MASTER_ID,
        A****N_DATES_ID,B.CALL_LETTERS,B.DMA_ID from
CONTRACT****N_DATES_STATIONS A  LEFT OUTER JOIN
 (SELECT  STATION_MASTER_ID,CALL_LETTERS,IBMSNAP_LOGMARKER,
        &# 160;      DMA_ID FROM CUSTOMER.STATION_MASTER
ORDER BY STATION_MASTER_ID) AS B ON
A.STATION_MASTER_ID=B.STATION_MASTER_ID
WHERE
(A.IBMSNAP_LOGMARKER=  '1900-01-01 11:16:30.0' OR
B.IBMSNAP_LOGMARKER= '1900-01-01 11:16:30.0')

This is taking 12539.84 Timer on's.

Is there any chance to improve the performance still? since there are large number of data, it's affecting the execution of time.

Look at the execution plan and determine what operation contributes most to the query cost, then try to eliminate or speed up that operation.

There are few table scans in the Access paths. Please suggest me how to avoid the table scans.

You can rewrite this query to an inner join.
The WHERE-clause requires that a column of table B is equal to a value and is therefore not <NULL>.

Are there indexes on the predicates? Keep in mind that DB2 will do a table scan once the number of rows is below a certain threshold, regardless of whether an index exists.