So I found an interesting quirk today, though I haven't found relevant documentation yet.
Say you're joining two tables:
SELECT *
FROM parent p
LEFT OUTER JOIN child c ON (c.parent_Id = p.parent_id)
WHERE p.indexed_column = :bv1
AND c.another_indexed_column = :bv2;
Because we have a predicate against that column of child the left-outer join is treated like an inner join -- specifically: we get the same plan as an inner join. However, if we're instead using a function-based index, in my case on nvl(two columns from child):
SELECT *
FROM parent p
LEFT OUTER JOIN child c ON (c.parent_Id = p.parent_id)
WHERE p.indexed_column = :bv1
-- We've created a function-based index on the nvl() below
AND NVL(c.another_indexed_column, c.a_third_indexed_column) = :bv2;
The optimizer treats this as a left outer join, where the rows of child are optional and so only plans that accommodate that are permissible. In my case, we in fact get a much worse plan than an inner join produces. You would think that since nvl() is deterministic/idempotent that the optimizer could sort all this out, but seemingly not.
The real queries & plans are below. The numbers in end-of-line comments are Buffer Reads for each predicate with a left-outer-join and then an inner-join. Note that in my case the best plan is an indexed read of each table, followed by a hash-join of the results.
SELECT /*+ gather_plan_statistics */
r.resv_num,
r.property_id,
r.resv_status_code,
r.resv_type_code,
r.arrival_date,
r.departure_date,
ru.lockoff_num,
ru.pm_unit_num,
ru.hskeeping_service_code
FROM p_reservation r left outer JOIN p_resv_unit ru ON (ru.resv_num = r.resv_num)
WHERE r.property_id = 'SST' AND r.resv_status_code = 'OPN'
-- AND ru.pm_unit_num = '171M' -- 5.3K, 5.3K
-- AND ru.lockoff_num = '171MP' -- 4.6K, 4.6K
AND NVL(ru.lockoff_num, ru.pm_unit_num) = '171MP' -- 25K, 4.6K
;
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 17125 (100)| | 22 |00:00:00.01 | 25629 |
|* 1 | FILTER | | 1 | | | | | 22 |00:00:00.01 | 25629 |
| 2 | NESTED LOOPS OUTER | | 1 | 1 | 58 | 17125 (1)| 00:00:03 | 36070 |00:00:00.07 | 25629 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| P_RESERVATION | 1 | 3129 | 106K| 1710 (1)| 00:00:01 | 5090 |00:00:00.01 | 4221 |
|* 4 | INDEX RANGE SCAN | RESERV_PROP_STAT | 1 | 3129 | | 12 (0)| 00:00:01 | 5090 |00:00:00.01 | 96 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| P_RESV_UNIT | 5090 | 6 | 138 | 5 (0)| 00:00:01 | 36068 |00:00:00.07 | 21408 |
|* 6 | INDEX RANGE SCAN | RESVUNT_RESERV_FK_I | 5090 | 6 | | 3 (0)| 00:00:01 | 36068 |00:00:00.03 | 15384 |
-------------------------------------------------------------------------------------------------------------------------------------------------------
And then with an inner join:
SELECT /*+ gather_plan_statistics */
r.resv_num,
r.property_id,
r.resv_status_code,
r.resv_type_code,
r.arrival_date,
r.departure_date,
ru.lockoff_num,
ru.pm_unit_num,
ru.hskeeping_service_code
FROM p_reservation r INNER JOIN p_resv_unit ru ON (ru.resv_num = r.resv_num)
WHERE r.property_id = 'SST' AND r.resv_status_code = 'OPN'
-- AND ru.pm_unit_num = '171M' -- 5.3K, 5.3K
-- AND ru.lockoff_num = '171MP' -- 4.6K, 4.6K
AND NVL(ru.lockoff_num, ru.pm_unit_num) = '171MP' -- 25K, 4.6K
;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3764 (100)| | 22 |00:00:00.01 | 4657 | | | |
|* 1 | HASH JOIN | | 1 | 3129 | 177K| 3764 (1)| 00:00:01 | 22 |00:00:00.01 | 4657 | 1268K| 1268K| 1454K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| P_RESERVATION | 1 | 3129 | 106K| 1710 (1)| 00:00:01 | 5090 |00:00:00.01 | 4221 | | | |
|* 3 | INDEX RANGE SCAN | RESERV_PROP_STAT | 1 | 3129 | | 12 (0)| 00:00:01 | 5090 |00:00:00.01 | 96 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| P_RESV_UNIT | 1 | 6416 | 144K| 2054 (1)| 00:00:01 | 2174 |00:00:00.01 | 436 | | | |
|* 5 | INDEX RANGE SCAN | SSWANK_RU_LOCKOFF_UNIT_NUM_N2 | 1 | 6416 | | 23 (0)| 00:00:01 | 2174 |00:00:00.01 | 11 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
So there's a bit of a curiosity, at least to my mind.