Re: maximum number of expressions in a list is 1000
From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 9 Jan 2013 14:25:41 -0800
Message-ID: <CAORjz=Pv_bG1VTJtySbfQyaEpzyGhD592yNjvqn+8xyAreOeGA_at_mail.gmail.com>
On Wed, Jan 2, 2013 at 2:51 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:
>
> or have a global temporary table to hold the list
>
> insert into gtt {statement that generates 1,000+ values efficiently}
>
> select from gtt, {rest of tables} where {join}
>
Date: Wed, 9 Jan 2013 14:25:41 -0800
Message-ID: <CAORjz=Pv_bG1VTJtySbfQyaEpzyGhD592yNjvqn+8xyAreOeGA_at_mail.gmail.com>
On Wed, Jan 2, 2013 at 2:51 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:
>
> or have a global temporary table to hold the list
>
> insert into gtt {statement that generates 1,000+ values efficiently}
>
> select from gtt, {rest of tables} where {join}
>
Seconding this recommendation, not that Jonathan's recommendations need seconding.
But seriously, that is too many inlist values.
Use GTT and refactor the SQL to deal with it properly.
BTW I have seen up to 35k bind variables in a where clause. Obviously Oracle is not converting that to an inlist.
Thankfully the client has rewritten that bit to use GTT.
Not quite off topic: Build yourself a test SQL like that and see how long it takes to parse.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 09 2013 - 23:25:41 CET