Re: Arbitrary amount of BETWEENs

From: Dion Cho <>
Date: Wed, 30 Apr 2008 01:58:52 -0700 (PDT)
Message-ID: <>

Very similar approach was well explained by Tom Kyte.

If PL/SQL object looks ugly, simple global temporary table is good alternative, like:

create global temporary table x_temp(v1 int, v2 int); insert into x_temp values(1, 100);
insert into x_temp values(200, 210);

select *
from t, x_temp x
where t.c1 between x.v1 and x.v2;

Insertion part on global temporary table is optimizable using bulk insertion technique like PL/SQL bulk insert or java batch insert. But there are a couple pitfalls here.

  • Values should be distinct, like (1~100), (200~210), (300~350). They shouldn't be overlapped.
  • Optimizer doesn't understand the purpose of temporary table or PL/ SQL table/sets. Sometimes manual optimization using hints would be necessary.

Dion Cho Received on Wed Apr 30 2008 - 03:58:52 CDT

Original text of this message