Re: maximum number of expressions in a list is 1000
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 3 Jan 2013 15:36:31 -0000
----- Original Message -----
From: "Sayan Malakshinov" <xt.and.r@xxxxxxxxx>
To: "Michael Dinh" <mdinh235@xxxxxxxxx>
Cc: <oracle@xxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
