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

From: Vlad Sadilovskiy <vlovsky_at_gmail.com>
Date: Sun, 12 Jun 2005 12:31:39 -0400
Message-ID: <df9f25d505061209314b46067a@mail.gmail.com>

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_at_yahoo.ca> 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.

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

Received on Sun Jun 12 2005 - 12:36:51 CDT