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. --- Vlad Sadilovskiy <vlovsky@xxxxxxxxx> wrote: > 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 > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- //www.freelists.org/webpage/oracle-l