| Overlapping Ranges [message #141051] |
Thu, 06 October 2005 14:36 |
dev73
Messages: 12
Registered: October 2005
Location: B
|
Junior Member |
|
|
I have table like this:-
CREATE TABLE RN (RF NUMBER(10), RT NUMBER(10));
INSERT INTO RN (RF, RT) VALUES(1,10);
INSERT INTO RN (RF, RT) VALUES(11,15);
INSERT INTO RN (RF, RT) VALUES(18,20);
INSERT INTO RN (RF, RT) VALUES(18,20);
INSERT INTO RN (RF, RT) VALUES(25,30);
INSERT INTO RN (RF, RT) VALUES(25,30);
INSERT INTO RN (RF, RT) VALUES(41,50);
INSERT INTO RN (RF, RT) VALUES(2,9);
I want a query to show me all overlapping ranges and
whom they overlap.
I wrote this.
SELECT * FROM rn a, rn b
WHERE (a.rowid != b.rowid)
and
(
a.rf between b.rf and b.rt
or
a.rt between b.rf and b.rt
or
b.rf between a.rf and a.rt
or
b.rt between a.rf and a.rt
)
its working. But is there anyother better way.
Because RN table have 1,100,000 rows, and this query
is taking more than 7, 8 hours, even when I have created
indexes. Looks like it has to make 1,100,000 * 1,100,000
comparisons.
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
