Almost certainly correct.
A (native) full outer join can't use in index "into" a table because the
full outer join needs to be an outer join in both directions.
You could try disabling the full outer join - as a test you could 'alter
session set "_optimizer_native_full_outer_join" = off' to see if that makes
enough of a difference to performance; but it's possible that the only
option is to ensure that you can get enough memory to avoid the spill to
disc. (In your version of Oracle/Exadata a hash join spill to disc can be
a huge fraction of the total run-time because the I/O can't "cheat".)
Regards
Jonathan Lewis
On Wed, 23 Dec 2020 at 18:22, Lok P <loknath.73@xxxxxxxxx> wrote:
Table COX has PK on column (EID,BID,OID,ETYP) and table CS has index on
column (EID) and table BOS has index on (BID, OID). But none of the index
is getting used in the query path, is it because the FULL OUTER JOIN cant
use index+ nested loop path here. Is this understanding correct?
