Dear all,
I have two tables: 2_MASTER(master_id, child_id) containing 44M rows, and Z_CHILD(child_id, attribute_1, attr_2, attr_3, attr_4) containing 74M rows. I have the following indexes defined:
MASTER(master_id, child_id)
MASTER(child_id, master_id)
CHILD(child_id)
CHILD(attr_1)
CHILD(attr_2, attr_3, attr_4)
The two tables have up to date statistics. I'm running oracle 12.1.
When I issue the following query:
SELECT
r.*
FROM
Z_MASTER rc
JOIN
Z_CHILD r
ON r.child_id = rc.child_id
WHERE
rc.master_id = 13966164 ;
I got the following execution plan (from SQL Developer):
My query seems to run fast, as expected, so currently I have no particular performance issue.
Can someone shed some light on why there is a HASH JOIN between the actual result of the query and a TABLE ACCESS (FULL) ? I'm not comfortable with interpreting plans, which probably explains my misunderstanding here. It seems to me that the first nested loop is doing the job... I don't understand why there is an extra one involving a full table scan (with cardinality of 1).
Thanks