Re: Max size of query?

Daniel Morgan <damorgan_at_exesolutions.com> writes:
>Hallvard B Furuseth wrote:
>> Daniel Morgan <dmorgan_at_exesolutions.com> writes:
>>> I believe that this list size is unlimited except by the common sense of
>>> the person banging out the code. For example I would never write what
>>> you have done above. Rather I would do this:
>>>
>>> SELECT ...
>>> FROM ...
>>> WHERE uname IN (
>>> SELECT uname
>>> FROM some_other table);
>>
>> Then I'd have to insert these user names in a table first.
>
> They are already there or you couldn't find them could you?

Half of the information is in a table, sort of: A table with a log over previous operations. Interspersed with other information which can't be easily selected away. I want to process users that have not previously been processed, which means I have to select all users with log IDs that have been inserted since last time the program ran - and these IDs are not simply 'all IDs highier than <highest ID last time>'. So we maintain a list with this format: '1-20,23,24,27-57' over which IDs have been processed so far. I select IDs which have not yet been processed, and lalter users with these IDs.

We could redesign the database with triggers when a new log ID is inserted, but the database will be redesigned soon anyway, so we don't want to fiddle with the old database design more than absolutely necessary.

> But yes you would. However if it is a large enough number of names
> that you are concerned about how many Oracle can handle how long will
> it take you to type them into your IN statement vs how long to put
> them into a table?

*Type* them? I keep saying, the list is generated. As in "automatically".

> The same amount of time. And yet in one case you can only use
> them once and in the other case you can reuse them forever.

I have no need or wish to reuse them. The list is generated once, used, and then never seen again. I don't see the point of creating a table, inserting them, and afterwards deleting them again just to be able to select on them once.

-- 
Hallvard