Re: Arbitrary amount of BETWEENs

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Wed, 30 Apr 2008 01:58:52 -0700 (PDT)
Message-ID: <cc512dcc-3f32-4014-844e-a66e64e8e4a3@t12g2000prg.googlegroups.com>


Very similar approach was well explained by Tom Kyte.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

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