Re: maximum number of expressions in a list is 1000

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 3 Jan 2013 15:36:31 -0000
Message-ID: <432B368306034DBE9F4BFA8FFBC5B2C9_at_Primary>


  • Original Message ----- From: "Sayan Malakshinov" <xt.and.r_at_gmail.com> To: "Michael Dinh" <mdinh235_at_gmail.com> Cc: <oracle_at_dunbar-it.co.uk>; <oracle-l_at_freelists.org> Sent: Thursday, January 03, 2013 3:28 PM Subject: Re: maximum number of expressions in a list is 1000

| Michael,
|
| Why do you think that it is because if "IN (...) OR IN (...)"?
| imho in your case root of the problem is a plan, not syntax. Could you
| show good and bad plans?
| My point is that a gtt is not very different from "in list". For
| example, nested loop with gtt is like "in list iterator", and "filter"
| access with list is not worse than hash/merge join.
|

A couple of thoughts on the comparison:

There may be differences in pinning and latching between IN lists and nested loops - on the other hand, though, the in-list is usually sorted, so for extreme cases you may reduce the need to re-read (index) blocks that have been flushed from memory between the first and second occurrence of similar values (but you might obviate that by using an inline 'select distinct' from the gtt.

The difference between a hash join and a large inlist might be more obvious - with a hash table the comparison would typically be made once by as a row is matched against the content of a haash bucket. With an inlist each incoming row would be compared (on average) with half the in-list before a match was found (when there was a match - and the whole list if there wasn't). This could lead to a significant difference in CPU usage.

As usual - depends on the data and the query

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 03 2013 - 16:36:31 CET

Original text of this message