Re: Some weird behavior with a collection in a subquery.
- From: Vlad Sadilovskiy <vlovsky@xxxxxxxxx>
- To: Lex de Haan <lex.de.haan@xxxxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Jun 2005 23:09:12 -0400
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@xxxxxxxxxxxxxx> 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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
