Re: Some weird behavior with a collection in a subquery.
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: vlovsky@xxxxxxxxx
- Date: Sun, 12 Jun 2005 15:09:32 -0400 (EDT)
Vlad,
I don't exactly understand why using union prevents
you from specifing a cardinality hint, but to avoid
HJ, you can probably go with hints? Something like
this ought to do it:
select /*+ ordered */ count(*)
from (
select /*+ use_nl( t1 t2) */
t1.object_name,t1.object_type, t2.object_name
from t1, t2
where t1.object_id in (
select /*+ cardinality(10)
*/ *
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)
Now, instead of going with rownum and a union, what I
think should work, is the no_merge hint. I tried it
here and got ORA-3113. On the bright side - it might
make a stronger case for support. After all ORA-3113
when hint is supplied, sounds more serious, than
suboptimal plan
Thanks,
Boris Dali.
--- Vlad Sadilovskiy <vlovsky@xxxxxxxxx> wrote:
> Boris,
>
> I appreciate that you tried it out. I've used rownum
> > 0 to prevent
> unnesting... No luck. Yours gives me following:
>
> Execution Plan
>
----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=3DCHOOSE
> (Cost=3D429 Card=3D1 Bytes=3D=
> 27)
> 1 0 SORT (AGGREGATE)
> 2 1 HASH JOIN (Cost=3D429 Card=3D16336
> Bytes=3D441072)
> 3 2 HASH JOIN (Cost=3D248 Card=3D16336
> Bytes=3D326720)
> 4 3 VIEW OF 'VW_NSO_1' (Cost=3D78
> Card=3D16336 Bytes=3D212368=
> )
> 5 4 SORT (UNIQUE) (Cost=3D78
> Card=3D16336 Bytes=3D16336)
> 6 5 UNION-ALL
> 7 6 COLLECTION ITERATOR (PICKLER
> FETCH) OF 'GETNUM
> ERICLIST'
>
> 8 6 COUNT
> 9 8 TABLE ACCESS (FULL) OF
> 'DUAL' (Cost=3D25 Card=3D
> 8168)
>
> 10 3 TABLE ACCESS (FULL) OF 'T1'
> (Cost=3D145 Card=3D48880 Byt
> es=3D342160)
>
> 11 2 TABLE ACCESS (FULL) OF 'T2'
> (Cost=3D145 Card=3D48888 Bytes
> =3D342216)
>
>
> Statistics
>
----------------------------------------------------------
> 1 recursive calls
> 0 db block gets
> 1212 consistent gets
> 81 physical reads
> 0 redo size
> 379 bytes sent via SQL*Net to client
> 503 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL>
>
> When using union I loose ability to provide
> cardinality and by default
> it is size of the block. Hence hash joins.
>
> To your second question. The function is used only
> for the test case.
> The real solution is base on binding ARRAYs from
> jdbc in place of that
> function. They are no different from each other.
>
> Thank you again.
>
> - Vladimir
>
> On 6/11/05, Boris Dali <boris_dali@xxxxxxxx> wrote:
> > Vlad,
> >=20
> > 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:
> >=20
> > 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 =3D t1.object_id
> > and t1.status =3D 0
> > and t2.status =3D 0);
> >=20
> > But what's the advantage of the procedural
> solution
> > here with cast() and table() in favour of a
> straight
> > sql?
> >=20
> > Thanks,
> > Boris Dali.
> --
> //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
Other related posts:
