Oracle sort nested query and rownum

i have a query that takes too much. It's running a 10g oracle instance. TABLE_A has 30.000.000 rows. TABLE_B has 300.000 rows.

SELECT A.F1, A.F2, B.F1
  FROM (  SELECT A.F1, A.F2, B.F1
          FROM TABLE_A A LEFT JOIN TABLE_B B ON A.ID_B = B.ID_B
          WHERE A.F3 = ? AND A.F4 = ?
        ORDER BY B.F1)
 WHERE ROWNUM < 100

I tried to create a view:

CREATE VIEW TABLE_B_SORTED AS SELECT * FROM TABLE_B ORDER BY F1

modifying the query like that

SELECT A.F1, A.F2, B.F1
  FROM (  SELECT A.F1, A.F2, B.F1
          FROM TABLE_A A LEFT JOIN TABLE_B_SORTED B ON A.ID_B = B.ID_B
          WHERE A.F3 = ? AND A.F4 = ?
       )
 WHERE ROWNUM < 100

but the order is not manteined.

I also tried to modify the query in this way

SELECT A.F1, A.F2, T.F1
  FROM (  SELECT A.F1, A.F2, T.F1
          FROM TABLE_A A LEFT JOIN (SELECT * FROM TABLE_B B ORDER BY B.F1 ) T ON A.ID_B = T.ID_B
          WHERE A.F3 = ? AND A.F4 = ?
       )
 WHERE ROWNUM < 100

but the order is not manteined.

Any suggestion?

Plan
SELECT STATEMENT  ALL_ROWSCost: 8.943  Bytes: 2.871  Cardinality: 99                        
    7 COUNT STOPKEY                      
        6 VIEW MY_SCHEMA. Cost: 8.943  Bytes: 146.247  Cardinality: 5.043                  
            5 SORT ORDER BY STOPKEY  Cost: 8.943  Bytes: 226.935  Cardinality: 5.043              
                4 HASH JOIN OUTER  Cost: 8.881  Bytes: 226.935  Cardinality: 5.043          
                    2 TABLE ACCESS BY INDEX ROWID TABLE TABLE_A Cost: 8.117  Bytes: 172.725  Cardinality: 4.935      
                        1 INDEX RANGE SCAN INDEX I_TABLE_A Cost: 27  Cardinality: 10.166  
                    3 TABLE ACCESS FULL TABLE TABLE_B Cost: 758  Bytes: 2.791.520  Cardinality: 279.152
开发者_JS百科

You should create probably create an ascending index on b.f1 if your query takes too much time.

Additionally if you are performing a LEFT JOIN this means that you may have NULL values in B. Do you want them first or last?

Probably you should better do:

   SELECT /*+ first_rows_100 */ -- do not hesitate to use Oracle hints!
          a.f1, a.f2, b.f1
     FROM table_b b
    INNER JOIN table_a a
       ON a.id_b = b.id_b
    WHERE a.f3 = ? and a.f4 = ?
    ORDER BY b.f1 ASC

Also, ordering a join source is completely useles (second and third query). JOINing never guarantees that resulting rows keep the order in which they are found in source tables.