I have a query that joins a multi million row table to one containing hundreds of thousands of rows. Schema owner 'ABC' owns the smaller table and schema owner 'DEF' owns the larger table. They both have select, insert and delete privileges on each of the tables however 'DEF' also has SELECT CATALOG role.
There are no synonyms pointing either user to different versions of the same table.
When performing an EXPLAIN plan from user 'ABC' we see NESTED LOOP as the join mechanism and the query just runs and runs. the same query when EXPLAINed from the 'DEF' user (the one with SELECT CATALOG) shows a HASH join. It seems that the HASH join is the more efficient in this case but that is largely irrelevant for the question.
My question is why would the optimizer choose different joins for the same query depending on which user is running the query ?
V$SQL_SHARED_CURSOR
for the specific sql_id, where the reason is stated for creating another child cursor (and possibly a new execution plan) for the same SQL statement.