Re: Query Performance issue

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_at_gmail.com> 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?
>
>
>

--
http://www.freelists.org/webpage/oracle-l