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

From: Vlad Sadilovskiy <vlovsky_at_gmail.com>
Date: Sun, 12 Jun 2005 18:08:37 -0400
Message-ID: <df9f25d5050612150887447ea@mail.gmail.com>

Boris,

It didn't prevent me from specifing cardinality hint, but it didn't work either. Check the estimated cardinality in the plan I posted. Regarding the hints. This type of query is used all over the place. I can't say I would want to write my own CBO to handle arrays and joined tables of different sizes. That's exactly how it will look like.

The bug does exist, and recognized by the Oracle TS and development. So, I don't need to go for tricking CBO in weird plans that crash the engine.

• Vlad

On 6/12/05, Boris Dali <boris_dali_at_yahoo.ca> wrote:
> Vlad,

>=20

> 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:

>=20


> 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 =3D t1.object_id
> and t1.status =3D 0
> and t2.status =3D 0)

>=20

> 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

>=20

> Thanks,
> Boris Dali.

>=20
>

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

Received on Sun Jun 12 2005 - 18:14:46 CDT