Re: Using a PLSQL Table with an IN clause

Do you have an index on the MUNICIPIO.ID ?

Have you used "explain plan" to find out the execution path with the table cast () ?

How about rewriting the query with the table cast() in the FROM clause with a join, something like:

select

    /*+ ordered */
    mu.*
from

    (select /*+ no_merge */ distinct column_value id     from
     TABLE (CAST(v_munisTable AS tMunisTable) )     ) ct ,
    municipio mu
where

    mu.id = ct.id
;

The distinct has to be there to cater for the possibility of multiple occurences of the same town in the list.

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

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Robert Bowen wrote in message
<68b4256f.0204050020.399211c9_at_posting.google.com>...


>Hello once again!

>

>Well, that seemed to do the trick! As I stated in my previous post, I

>have found out that using an IN clause in my case is impossible, I

>simply have too many values. With 70 values it takes 3 minutes! With

>1,000+ (a possibility) it could takes eons! But thanks anyway, it's

>good to know how to do this for future reference, I'll stuff it in my

>"library" (my head ...).

>

>As for the other post -- look man, sorry to offend, my apologies. I

>simply found it hard to believe that suddenly my db sh*t the bed, you

>know? Much more likely that I was trying to do something stupid or

>illegal with an internal oracle object and in doing so generated a

>generic, catch-all "DON'T DO THAT DUMMY" error ... Kind of like when

>you forget the semicolon at the end of the line in a perl script. Load

>the script in a browser and the error "Internal error! Call the system

>adminisrator!" comes up. The difference is if you look for this error

>in a perl book it will tell you "You probably forgot a semicolon

>doofus!" Whereas oracle (according to your post) says it is indeed a

>big deal.

>

>But I am willing to bet that the ORA600 error is a generic one, that

>it's possible for it to pop up when you try to do something illegal,

>and it doesn't necessarily mean your db is totally hosed. It certainly

>seems I was trying to do something illegal with a nested table.

>Perhaps a quick call to oracle would answer this question. But we

>don't have a service contract and I don't want to spend the $$ or

>waste time perusing newsgroups looking for the answer.

>

>I am sure you would agree life is too short to get into arguments over

>something so silly on a database newsgroup. So please accept my

>apologies, I admit my post was rude. I thought I was being blown off,

>which I was not. And I hope my post elicits genuine "warm and

>fuzzies", instead of facetious ones. (One could smell the sarcasm

>coming off your post ...)

>

>Again, thanks for everyone's help in this. Perhaps future surfers will

>save some time and energy reading this series of posts.

>

>Cheers,

>Bob

>> try

>>

>> (MUNICIPIO.ID IN (SELECT column_value FROM TABLE (CAST(v_munisTable AS

>> tMunisTable) ) ) )

>>

>> note "column_value" instead of *