hash_anti and hash_semi joins.

just a couple of questions (817)

observations :
in some cases X_semi requires the INside column to be not null (this is empirical though)

and it seems to be in ALL cases that X_anti requires the column to be NOT NULL. (I changed hash to X to allow for all cases)

This is not documented anywhere.

If this is not met, then the operations seem to switch to highly inefficient FILTERs.

SQL is of type
semi:
select count (*) from x
where x.col1 in (select col2 from Y) .

anti :
or select count (*) from x
where x.col1 not in (select col2 from Y)

and also in cases like
select count (*) from x
where x.col1 not in (select col2 from Y where x.col1=y.col2)

The (liberal) trials of hints don't seem to help. Only the alteration NOT NULL seems to work.
I note the CBO costs the filter cheaper than the _SJ,_AJ, but if the subquery is large then the CPU usage is massive.

It's SQL from a tool. Enhancement of the tool will take a long time to get them to create the cols NOT NULL.

Anyone have any tips ?

eg hint oh_go_on_then_I_know_its_not_null=TRUE

9201 has changes (not least of which that the ALWAYS_ init vars are gone... somebody probably did the tests and found that NL_SJ and NL_AJ were better in 90% of cases although ya can still hint it if you want)

Metalink offers some (one or two other people found the same thing) but otherwise no.

TIA T Received on Thu Dec 05 2002 - 13:26:26 CST