Arbitrary amount of BETWEENs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dee - 29 Apr 2008 00:01 GMT

Hi...

I have an index organized table on which i'll be carrying out a select
of the following type...

select value from table
where key between 1 and 4 or key between 9 and 11 ....... or key
between 100 and 201

The current issue is, that the amount of range selects is arbitrary
and in worst case this query could end up awfully long. All that comes
to my mind right now is gluing a string up dynamically in pl/sql and
executing it... However.. Makes my stomach turn over big time as I'm
guessing this involves serious parsing and processing overhead
eliminating the win-win on doing range selects on an index organized
table...

I'd appreciate if anyone could point me in the direction of a cleaner
approach which i might have overlooked :)

Regards :)

Reply to this Message

phil_herring@yahoo.com.au - 29 Apr 2008 01:01 GMT

Something like this, maybe?

SQL> create table test_pairs
2 (
3 what_we_are_looking_for varchar2(100),
4 the_key integer
5 );
Table created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('one', 1);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('two', 2);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('three', 3);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('four', 4);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('five', 5);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('six', 6);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('seven', 7);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('eight', 8);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('nine', 9);
1 row created.
SQL>
SQL> insert into test_pairs (what_we_are_looking_for, the_key) values
('ten', 10);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create or replace type pair_of_integers_typ as object
2 (
3 integer_1 integer,
4 integer_2 integer
5 );
6 /
Type created.
SQL>
SQL> create or replace type pair_of_integers_tab as table of
pair_of_integers_typ;
2 /
Type created.
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_pairs_of_integers pair_of_integers_tab :=
pair_of_integers_tab();
4 rec_test_pairs test_pairs%rowtype;
5
6 begin
7
8 v_pairs_of_integers.extend;
9 v_pairs_of_integers(1) := pair_of_integers_typ(4, 5);
10
11 v_pairs_of_integers.extend;
12 v_pairs_of_integers(2) := pair_of_integers_typ(7, 8);
13
14 for rec_test_pairs in
15 (
16 select
17 *
18 from
19 test_pairs tp,
20 table(cast(v_pairs_of_integers as
pair_of_integers_tab)) ranges
21 where
22 tp.the_key between ranges.integer_1 and
ranges.integer_2
23 ) loop
24
25
dbms_output.put_line(rec_test_pairs.what_we_are_looking_for || ' ' ||
rec_test_pairs.the_key);
26
27 end loop;
28
29 end;
30 /
four 4
five 5
seven 7
eight 8
PL/SQL procedure successfully completed.
SQL>

-- Phil

Reply to this Message

dee - 30 Apr 2008 02:09 GMT

Well.. It does the trick but unfortunately this statement has a huge
severe impact on performance itself probably due to the cartesian join
(100ms vs 30s). Worth mentioning is that the table at present time
contains more than 6.000.000 rows and growing. But thanks though for a
detailed example :) Any other suggestions?

Reply to this Message

phil_herring@yahoo.com.au - 30 Apr 2008 03:02 GMT

> Well.. It does the trick but unfortunately this statement has a huge
> severe impact on performance itself probably due to the cartesian join
> (100ms vs 30s). Worth mentioning is that the table at present time
> contains more than 6.000.000 rows and growing. But thanks though for a
> detailed example :) Any other suggestions?

I suggest you won't last in this line of work if you give up that
easily.

With very little effort, and on a table with 32 million rows, I got it
down to under 2s with 30 pairs of dates. I could probably make it
faster.

Read up on performance tuning.

-- Phil

Reply to this Message

Dion Cho - 30 Apr 2008 09:58 GMT

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

Reply to this Message