Re: Arbitrary amount of BETWEENs
Date: Wed, 30 Apr 2008 01:58:52 -0700 (PDT)
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);
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