Oracle Multiple Overlapping Between Statements?

It depends on the query as to how the optimizer will handle it. You can test it in SQLPlus using autotrace and looking at the Predicate Information (this is done on Oracle version 10.2.0.3): SQL>set autot traceonly SQL> 1 select l 2 from (SELECT l 3 FROM (SELECT LEVEL l 4 FROM dual CONNECT BY LEVEL =5 AND "L"=7 AND "L" 1 select l 2 from (SELECT l 3 FROM (SELECT LEVEL l 4 FROM dual CONNECT BY LEVEL =5 OR "L">=7)) 2 - filter(LEVEL.

1 What a great answer! Thanks! I guess the safe way to do it then is for me to just make sure it gets done on my end.

– davidemm Sep 9 '09 at 22:07.

It's worth looking at what happens when we run a trial against a real table with an index. This example table has 69,241 rows and a non-unique index on COL_3, with statistics. Case 1: yer basic two overlapping BETWEEN clauses SQL> set autotrace traceonly exp SQL> SQL> select * from big_table 2 where col_3 between 0.8 and 1 3 or col_3 between 0.9 and 1.1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3993303771 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14737 | 805K| 176 (1)| 00:00:03 | |* 1 | TABLE ACCESS FULL| BIG_TABLE | 14737 | 805K| 176 (1)| 00:00:03 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("COL_3"=0.9 OR "COL_3"=0.8) SQL> Upshot: The index is ignored and a full table scan ensues Case 2: the BETWEEN clauses share an upper bound SQL> select * from big_table 2 where col_3 between 0.8 and 1.1 3 or col_3 between 0.9 and 1.1 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 1461639892 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7924 | 433K| 114 (0)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 7924 | 433K| 114 (0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | BIG3_IDX | 7924 | | 17 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL_3"=0.8 OR "COL_3">=0.9) SQL> Upshot: The index is used for the upper bound and a full table scan is avoided Case 3: the BETWEEN clauses share an lower bound SQL> select * from big_table 2 where col_3 between 0.8 and 1.1 3 or col_3 between 0.8 and 1.2 4 / Execution Plan ---------------------------------------------------------- Plan hash value: 3993303771 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15146 | 828K| 176 (1)| 00:00:03 | |* 1 | TABLE ACCESS FULL| BIG_TABLE | 15146 | 828K| 176 (1)| 00:00:03 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("COL_3"=0.8) SQL> Upshot: The index is ignored and a full table scan ensues Case 4: the two BETWEEN ranges are merged into a single clause SQL> select * from big_table 2 where col_3 between 0.8 and 1.1 3 / Execution Plan ---------------------------------------------------------- Plan hash value: 1461639892 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7924 | 433K| 114 (0)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 7924 | 433K| 114 (0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | BIG3_IDX | 7924 | | 17 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL_3">=0.8 AND "COL_3" Upshot: The index is used for both the upper and lower bounds So, in summary, if the two BETWEEN clauses overlap and there is an index on the column then it could be worth the effort of merging them.

1 +1 for highlighting the potential effect on performance – Jeffrey Kemp Sep 10 '09 at 12:38 I am using an index, so this was good to know. Thanks! – davidemm Sep 10 '09 at 20:06.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.