Subject Re: IN predicate limit
Author Adam
--- In, "Dmitry Yemanov"
<dimitr@u...> wrote:
> "Adam" <s3057043@y...> wrote:
> >
> > (ID IN ('1','2','3',......,'1499') OR ID IN
> > ('1500','1501','1502',......,'2002'))
> What amount of rows is your predicate expected to match? 5% of the

It varies and depends on the permissions of who is using it. On a
site level, generally only a small percentage is visible, maybe up to
100 or 200 records, 5% may be realistic, but it depends on the
customer. Some customers have a few large sites, others have multiple
small sites. The main problem we found during testing is where an
area office or head office is involved. They tend to have effectively
God mode in the application, so they can include up to 100% of the
individuals, however they tend to look at a subset of date ranges
which means the percentage of the table drops as time goes on.

> Do you understand that if ID is indexed (I suppose it is) your
query will
> force 2000 separate index scans? Unless the output covers only a few
> percents of the huge table, I really doubt this is faster than a
> scan.

I will force a natural scan (ID+0 in ()) and compare the performance.
It may not be the PK of the table, I used ID as an example. In fact
our IDs have no business meaning to the customer, it is far more
likely to be a list of foreign key values.

> > It seems that I can not get the PLAN using any of my normal
tools. (It
> > comes back blank). On further investigation, it is actually
coming up
> > with a sensible plan, (IBPlanalyzer and IBAdmin both show it
> > indexed reads and it is definately not doing a table scan). What
> > cause this, and is it something I should be concerned about?
> Plan buffer (passed by the tool) is not big enough.

Thats alright then.

By the way, my dunit test cases have found the next limit (64KB in a
single SQL statement), but I knew that anyway. It hits after about
12000 members using this technique in case anyone is interested. It
is a significant improvement over 1499.

Thanks for the answer.