Re: Some weird behavior with a collection in a subquery.

I've been working to resolve this for some months now. You might check the test case I provided. The query looks like any other select ... from t where t.f in (subquery) would look. The statistics is up to date and cardinality hint on the subqury gives CBO a good picture on what should be done.

Moreover when I use _always_semi_join=3Doff CBO does exactly as expected. Meaning doing nested loops join when subqury cardinality is little and hash join if it's higher. 10053 trace shows proper cardinality and selectivity and such. But if it's not restricted by _always_semi_join=3Doff it calculates cardinality of this join as:

outer cardinality * selectivity of join field of outer

as if I had cardinality of the subquery equals to 1. But I also see CBO accepts subqury cardinality properly and consistently with the hint. Other way which is more reasonable is that CBO thinks that number of distinct values coming from the subqury is 1. But how do I tell it that they are all unique? Dynamyc sampling doesn't help either. This is true at least in 9i.

According to this formulae, real example arrives at 1-3 rows out of this join. CBO favors this path because further operations are done on these few rows and are much cheaper than they are in reality.

The problem with the parameter is that I want to legitimate semi-joins still exist so I don't want to set that parameter.

Anyway, thanks for the suggestions. Oracle TS is working on this for same time with no luck so far. I hope they will come up with something.

• Vlad

On 6/10/05, Lex de Haan <lex.de.haan_at_naturaljoin.nl> wrote:
> ah -- I see. so they behave "sort of" as subqueries, and they are resolve=
d by
> the CBO using a semi join.
> well, that's not a bad choice per se -- Oracle typically tries to flatten=
 all
> subquery constructs into joins, optionally using semi- or antijoins to gu=
arantee
> the correct results.
>=20
> if the semijoin is giving a bad performance, you might want to find out w=
hy --
> rather than trying to prevent them from happening. a semijoin is normally=
 a
> quite effective operation. there is a hidden parameter, _ALWAYS_SEMI_JOIN=
, that
> you might play around with. the default is CHOOSE, and you can set it to
> NESTED_LOOPS, MERGE, or HASH.
>=20
> Normally, when using "real" subqueries, you can use the NO_UNNEST hint in=
 the
> subquery to prevent subquery unnesting. Not sure how this could be done i=
n your
> environment, though.
>=20
> Last but not least, but maybe rather obvious -- these are also some thing=
s to
> check:
>=20
> - are the estimated CBO selectivities/cardinalities way off reality, or p=
retty
> close?
> - are the statistics up to date?
>=20
> kind regards,
>=20
> Lex.
>

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