Re: Query Performance issue

Just did a quick try with the alter session , "_optimizer_native_full_outer_join" = off', the plan does change, but still does not seem to be coming within <30 seconds. I will try to run it fully and see.

On Thu, Dec 24, 2020 at 1:03 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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




text/plain attachment: Sql_plan_with_Alter_session.txt