RE: list limit for IN predicate?

The in-list problem is a query optimizer classic. Ingres turns in-lists into
sequences of or'ed "=" predicates (e.g. a = value1 or a = value2 or ...).
Query optimizers usually represent where clauses and other expressions as
tree structures and then use recursive calls to analyze their contents. A
large in-list predicate spawns a very deep tree structure (because of the
transformation to or'ed "="s) and the recursive analysis of this tree
results in many calls being piled onto the C call stack. By the time you add
up the stack frame requirements for each of these recursive calls, a large
enough in-list can exceed the size of the call stack being used by the query
compilation. Since 1.2 (or maybe 2.0) Ingres has incorporated a technique
which detects the stack overflow and just fails the query. Before then, the
stack overflow was permitted to happen and arbitrary server memory
overlaying took place. This could lead to failure of other user threads and
even server failure.

The number of entries in the in-list is the critical factor in whether the
stack overflow happens or not (not the size of the constant values in the
in-list entries). Tests I ran last July showed that the default stack size
(64K?) allows roughly 150 entries in an in-list in pre-2.0 releases of
Ingres. 2.0 changes reduced this to a little under 100, but a fix produced
in the October/November 1998 timeframe increased the threshold up to over
200 (for 2.0, only). 

If this is a chronic problem, the C stacksize should be increased using CBF.
Unfortunately, this affects all user threads running on the server (meaning
more memory is allocated for everyone). So if this causes you concern and if
this is truly a scheduleable maintenance query, you could bring up a server
with the increased stacksize, run the queries, then restart the server with
the smaller stacksize.

Doug.

Doug Inkster
Computer Associates Intl.,
2580 Innes Road,
Gloucester, Ont.  Canada

phone: (613)837-1236
email: douglas.inkster@cai.com   or inkdo01@cai.com

> -----Original Message-----
> From:    Keith Jamieson [SMTP:pdkj02@email.mot.com]
> Sent:    Thursday, February 18, 1999 11:07 AM
> To:    info-ingres@ams.org
> Subject:    Re: list limit for IN predicate?
> 
> I encountered a similar problem a few years ago. I was building up a query
> using the In clause, but as soon as we got more than 255 list items, the
> query failed.
> 
> Dennis Roesler wrote:
> 
> > Does anyone know what the maximum number of items there can be in a list
> > for the IN predicate.  I have a maintenance script that extracts
> > information from one dB, builds a list for the IN predicate, connects to
> > a different dB, and runs a query based on the list generated.  In one
> > instance the list that was built had ~450 items in the list, which
> > crashed the server.
> >
> > Is it a matter of the size of the list in characters and not items in
> > the list?  The whole list, including the quotes around the items in the
> > list, commas, etc. was ~5k characters.
> >
> > Thanks
> >
> > Dennis
> 
> 


--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026