Re: Query Performance issue

When you said "* but it's possible that the only option is to ensure that you can get enough memory to avoid the spill to disc*. ", are you pointing towards setting manual work area size and hash_area=2GB in session level? or some other way?

I was thinking if driving the query in parallel -4 is a sensible way. What's your thought on this?

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