ALWAYS_SEMI_JOIN & ALWAYS_ANTI_JOIN parameters

[did02]

Hello,

I'm tuning a database.
I used a tool that indicates me to set up these variables in the InitSID.ora

ALWAYS_SEMI_JOIN = HASH #default value = NESTEED_LOOPS
ALWAYS_ANTI_JOIN = HASH #default value = NESTEED_LOOPS

I did it. But now, it seems that many queries run very slow.

I take an eye in the Oracle doc : these parameters indicates to Oracle what algorythm it has to use.
But in the SQL Reference doc, I only found Equijoins, Self Joins and Outer Joins.

What are semi join and anti join ?
Could someone tell me how set up these parameters ?

Thanks in advance.
Rgds,
Did02

 

[spenglerr]

a semi join is : EXISTS
an anti join is: NOT IN

if you set ALWAYS_SEMI_JOIN = HASH you should have a multi-CPU server that is able to process queries parallel.
To use hash you should also set the HASH_AREA_SIZE parameter in your ini-file. Also the CPU_COUNT and PARALLEL_MAX_SERVERS should be set then.