| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Some weird behavior with a collection in a subquery.
Vlad,
I am not sure, why CBO goes for semi-join, but I think you can steer it to "materializing" your "function-based subquery" first by (for example) something like this:
select count(*) from (
select t1.object_name,t1.object_type, t2.object_name
from t1, t2
where t1.object_id in (
select *
from table( cast(
getnumericlist(10) as table_of_number)) nlist
union
select rownum-1 from dual
)
and t2.object_id = t1.object_id
and t1.status = 0
and t2.status = 0);
But what's the advantage of the procedural solution here with cast() and table() in favour of a straight sql?
Thanks,
Boris Dali.
> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jun 11 2005 - 21:54:36 CDT
 |
 |