Oracle: Left join very big table and limit the joi

Question

I have two tables. The second one references to the first one by m_id.

Main table

M_ID | M_FIELD
  1  | 'main1'
  2  | 'main2'
  3  | 'main3'

Sub-table

S_ID | S_FIELD  | S_ORDER | M_ID
  1  | 'sub1-1' |    1    |  1
  2  | 'sub1-2' |    2    |  1
  3  | 'sub1-3' |    3    |  1
  4  | 'sub2-1' |    1    |  2
  5  | 'sub2-2' |    2    |  2
  6  | 'sub2-3' |    3    |  2
  7  | 'sub3-1' |    1    |  3
  8  | 'sub3-2' |    2    |  3
  9  | 'sub3-3' |    3    |  3

I need to join these two tables (by M_ID) but from the Sub-table I need only the row with the largest value of S_ORDER.

So the expected result of the query is:

M_ID | M_FIELD | S_FIELD
  1  | 'main1' | 'sub1-3'
  2  | 'main2' | 'sub2-3'
  3  | 'main3' | 'sub3-3'

There is working solution with analytical function in the answer of this question: How do I limit the number of rows returned by this LEFT JOIN to one? (I will post it at the bottom) But the problem is that Sub-Table is very big (and is actually a view with some inner calculations) and this kind of subquery works way too long. So I suppose I need to filter out the table by m_id first and only after that find the field with the largest S_ORDER

I need something simple like this (which fails because the second level subquery doesn't see the M.M_ID field outside):

SELECT m.*,
       (SELECT s_field
        FROM (SELECT s_field
              FROM t_sub s
              WHERE s.m_id = m.m_id
              ORDER BY s_order DESC)
        WHERE ROWNUM = 1) s_field
 FROM t_main m;

The code to create and populate the test schema:

CREATE TABLE t_main (m_id NUMBER PRIMARY KEY,
                     m_field VARCHAR2(10));
CREATE TABLE t_sub  (s_id NUMBER PRIMARY KEY,
                     s_field VARCHAR2(10),
                     s_order NUMBER,
                     m_id NUMBER );
INSERT INTO t_main VALUES (1,'main1');
INSERT INTO t_main VALUES (2,'main2');
INSERT INTO t_main VALUES (3,'main3');
INSERT INTO t_sub VALUES (1,'sub1-1', 1, 1);
INSERT INTO t_sub VALUES (2,'sub1-2', 2, 1);
INSERT INTO t_sub VALUES (3,'sub1-3', 3, 1);
INSERT INTO t_sub VALUES (4,'sub2-1', 1, 2);
INSERT INTO t_sub VALUES (5,'sub2-2', 2, 2);
INSERT INTO t_sub VALUES (6,'sub2-3', 3, 2);
INSERT INTO t_sub VALUES (7,'sub3-1', 1, 3);
INSERT INTO t_sub VALUES (8,'sub3-2', 2, 3);
INSERT INTO t_sub VALUES (9,'sub3-3', 3, 3);
COMMIT;

Working solution mentioned above (working too slow with large T_SUB table):

SELECT m.*,
       s.s_field
FROM t_main m
LEFT JOIN
  (SELECT *
   FROM
     (SELECT ts.*,
             ROW_NUMBER() OVER (PARTITION BY m_id
                                ORDER BY s_order DESC) AS seq
      FROM t_sub ts)
   WHERE seq = 1) s ON s.m_id = m.m_id;

The DB we use is Oracle 10g

Thank you very much for your help

Answer 1

select t.*, s.s_field from t_main t
left join (select m_id, min(s_field) keep(dense_rank first order by s_order desc) as s_field 
       from t_sub group by m_id) s on (s.m_id = t.m_id)

Answer 2

try this

SELECT m.*,
       (select s.s_field 
          from t_sub s
         where s.m_id = m.m_id
           and s.s_order = (select max(s_order) from t_sub where t_sub.m_id = s.m_id)
           and rownum = 1)
FROM t_main m

or you can try this (it's your code but some modifications)

SELECT m.*,
      (select s.s_field from 
       (SELECT s_field, m_id
          FROM t_sub
         --where t_sub.m_id = m.m_id
         order by s_order DESC) s
        where s.m_id = m.m_id
          and rownum = 1)
FROM t_main m