nested query vs Joins in Oracle

Question

I know we have a lot of questions about this, but mine is specific to Oracle. I always thought using join is faster in performance. But today, I have 2 queries that returns the same data, but one uses join and one is nested. Explain plan shows the cost for nested query is better. I don't know what to think. When should I use nested query and when should I not? We're using Oracle 11.2.

Here's the text with explain plan for each:

select (select p.organization_code from apps.mtl_parameters p where p.organization_id = ship_from_org_id)  
from ont.OE_ORDER_LINES_all l
where header_id = (select header_id from ont.oe_order_headers_all where order_number = '9000385403')
and l.ordered_item = 'SSFRAMES';

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |     1 |    21 |   120   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID  | MTL_PARAMETERS          |     1 |     8 |     1   (0)|
|   2 |   INDEX UNIQUE SCAN           | MTL_PARAMETERS_U1       |     1 |       |     0   (0)|
|   3 |  TABLE ACCESS BY INDEX ROWID  | OE_ORDER_LINES_ALL      |     1 |    21 |   116   (0)|
|   4 |   INDEX RANGE SCAN            | XXOE_ORDER_LINES_ALL_X6 |   586 |       |     6   (0)|
|   5 |    TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL    |     1 |    12 |     4   (0)|




select p.organization_code 
from ont.OE_ORDER_LINES_all l
inner join ont.oe_order_headers_all oha on oha.header_id = l.header_id 
inner join apps.mtl_parameters p on p.organization_id = l.ship_from_org_id                
where l.ordered_item = 'SSFRAMES' and oha.order_number = '9000385403'

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |     1 |    41 |   119   (0)|
|   1 |  NESTED LOOPS                  |                      |       |       |            |
|   2 |   NESTED LOOPS                 |                      |     1 |    41 |   119   (0)|
|   3 |    NESTED LOOPS                |                      |     1 |    33 |   118   (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL |     1 |    12 |     4   (0)|
|   5 |      INDEX RANGE SCAN          | OE_ORDER_HEADERS_U2  |     1 |       |     3   (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID| OE_ORDER_LINES_ALL   |     1 |    21 |   114   (0)|
|   7 |      INDEX RANGE SCAN          | OE_ORDER_LINES_N1    |   586 |       |     3   (0)|
|   8 |    INDEX UNIQUE SCAN           | MTL_PARAMETERS_U1    |     1 |       |     0   (0)|
|   9 |   TABLE ACCESS BY INDEX ROWID  | MTL_PARAMETERS       |     1 |     8 |     1   (0)|


select p.organization_code 
from ont.OE_ORDER_LINES_all l
inner join ont.oe_order_headers_all oha on l.header_id in oha.header_id 
inner join apps.mtl_parameters p on p.organization_id in l.ship_from_org_id                
where l.ordered_item = 'SSFRAMES' and oha.order_number = '9000385403';

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |     1 |    40 |   115   (0)|
|   1 |  NESTED LOOPS                  |                         |       |       |            |
|   2 |   NESTED LOOPS                 |                         |     1 |    40 |   115   (0)|
|   3 |    NESTED LOOPS                |                         |     1 |    32 |   114   (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL    |     1 |    11 |     4   (0)|
|   5 |      INDEX RANGE SCAN          | OE_ORDER_HEADERS_U2     |     1 |       |     3   (0)|
|   6 |     TABLE ACCESS BY INDEX ROWID| OE_ORDER_LINES_ALL      |     1 |    21 |   110   (0)|
|   7 |      INDEX RANGE SCAN          | XXOE_ORDER_LINES_ALL_X6 |   582 |       |     5   (0)|
|   8 |    INDEX UNIQUE SCAN           | MTL_PARAMETERS_U1       |     1 |       |     0   (0)|
|   9 |   TABLE ACCESS BY INDEX ROWID  | MTL_PARAMETERS          |     1 |     8 |     1   (0)|

Answer 1

Just looking at the explain plan won't give you what query is actually going to execute faster. It's merely what the optimizer guesses given the current information.

Also, cardinality guessing errors tend to propagate to higher level operations. E.g. the subselect that you placed in the SELECT clause is only counted once for the whole statement, but of course should be counted for every expected record to be returned.

Finally, the equivalent of the equal join would be a IN (... ) condition not a = ( ... ) condition.