Hatik Hatik - 1 year ago 50
SQL Question

Oracle Efficiently joining tables with subquery in FROM

Table 1:

| account_no | **other columns**...
+------------+-----------------------
| 1 |
| 2 |
| 3 |
| 4 |


Table 2:

| account_no | TX_No | Balance | History |
+------------+-------+---------+------------+
| 1 | 123 | 123 | 12.01.2011 |
| 1 | 234 | 2312 | 01.03.2011 |
| 3 | 232 | 212 | 19.02.2011 |
| 4 | 117 | 234 | 24.01.2011 |


I have multiple join query, one of the tables(Table 2) inside a query is problematic as it is a view which computes many other things, that is why each query to that table is costly. From Table 2, for each
account_no
in Table 1 I need the whole row with the greatest
TX_NO
, this is how I do it:

SELECT * FROM TABLE1 A LEFT JOIN
( SELECT
X.ACCOUNT_NO,
HISTORY,
X.BALANCE
FROM TABLE2 X INNER JOIN
(SELECT
ACCOUNT_NO,
MAX(TX_NO) AS TX_NO
FROM TABLE2
GROUP BY ACCOUNT_NO) Y ON X.ACCOUNT_NO = Y.ACCOUNT_NO) B
ON B.ACCOUNT_NO = A.ACCOUNT_NO


As I understand at first it will make the inner join for all the rows in Table2 and after that left join needed
account_no
's with Table1 which is what I would like to avoid.

My question: Is there a way to find the
max(TX_NO)
for only those accounts that are in Table1 instead of going through all? I think it will help to increase the speed of the query.

Answer Source

I think you are on the right track, but I don't think that you need to, and would not myself, nest the subqueries the way you have done. Instead, if you want to get each record from table 1 and the matching max record from table 2, you can try the following:

SELECT * FROM TABLE1 t1
LEFT JOIN
(
    SELECT *
    FROM TABLE2
    WHERE ROW_NUMBER() OVER (PARTITION BY account_no ORDER BY TX_No DESC) = 1
) t2
    ON t1.account_no = t2.account_no

If you want to continue with your original approach, this is how I would do it:

SELECT *
FROM TABLE1 t1
LEFT JOIN TABLE2 t2
    ON t1.account_no = t2.account_no
INNER JOIN
(
    SELECT account_no, MAX(TX_No) AS max_tx_no
    FROM TABLE2
    GROUP BY account_no
) t3
    ON t2.account_no = t3.account_no AND
       t2.TX_No      = t3.max_tx_no

Instead of using a window function to find the greatest record per account in TABLE2, we use a second join to a subquery instead. I would expect the window function approach to perform better than this double join approach, and once you get used to it can even easier to read.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download