Re: Maximun number of unions in a single query

On Mon, 6 Jun 2005 14:59:31 -0700, "Andreas Sheriff" <spamcontrol_at_iion.com> wrote:

>Does anyone know the maximum number of unions you can use in a SQL query?
>
>For example:
>
>select 1 from dual union all
>select 2 from dual union all
>select 3 from dual union all
>...
>select [x-2] from dual union all
>select [x-1] from dual union all
>select [x] from dual;
>
>What is the upper limit of x?

 Since it doesn't appear to be documented as a limit in the manual (at least not in the usual place), there's one way to find out... knock up a script to parse increasingly long statements until it explodes.

 I just put one together and I can't find a limit - an 85kb statement with an absurd 3000 "union all"s still runs (although it takes some time to parse!). This was on 10g. Looks like the 64k statement limit documented in 9i has been dropped in 10g - it's disappeared from the Logical Database Limits page of the Reference manual and this statement is definitely longer.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool