Re: IN LIST in PL/SQL

There is one annoying detail to cope with in this strategy - the optimizer assumes that a table(cast()) has 8,168 rows to it, and chooses a join order accordingly.

In some cases this can be fixed by creating an in-line non-mergeable view of the table() and using the cardinality(alias, row_count) hint to give Oracle a better idea of the number of rows expected. Your guess may be more appropriate than Oracle's

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG -  v$ and x$
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1074726954.513643_at_yasure...


> Noons wrote:

>

> > "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message

news:400D19DC.4D03_at_yahoo.com...


> >

> >>You can use a collection type to do this...Something like:

> >>

> >>type t is table of varchar2(80);

> >>

> >>declare

> >>  x t := t('04','08','13');

> >>begin

> >>  for i in (

> >>    select * from t1

> >>    where col in (

> >>      select column_value

> >>      from table(cast(x as t)) )

> >

> >

> > Ni-s-s-s-s-s!......

>

> Brilliant.

>

> -- 

> Daniel Morgan

> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

> damorgan_at_x.washington.edu

> (replace 'x' with a 'u' to reply)

>