select question

From: <kazelot_at_thenut.eti.pg.gda.pl>
Date: Mon, 23 Jul 2001 15:39:25 +0200
Message-ID: <Pine.LNX.4.33.0107221723380.7399-100000@thenut.eti.pg.gda.pl>

I have a table with varchar2(5) column, for example:

> select spqmc from proba;

spqmc

---

10000
10001
10002
10004
10005
10007

(Unfortunatelly there is much more rows there, about 500).

I need one select that would give me continuos ranges. I mean

> select r_from, r_to from (some magic select);
r_from r_to

---

10000	   10002
10004	   10005
10007	   10007

I came up with one, terribly slow, solution. Maybe it can be done in some other way.

Here is what I got:

1. I found continuos block of numbers (takes about 50ms for 100 rows):

SELECT A.SPQMC OD1 , B.SPQMC DO1
FROM PROBA A, PROBA B
WHERE
   B.SPQMC - A.SPQMC =

      (SELECT (COUNT(*)-1) FROM
       PROBA LICZ WHERE
       LICZ.SPQMC >= A.SPQMC AND SPQMC <= B.SPQMC)

   AND
   B.SPQMC >= A.SPQMC od1 do1

---

10000	10000
10000	10001
10000	10002
10001	10001
10001	10002
10002	10002
10004	10004
10004	10005
10005	10005
10007	10007

2) I restrict the list "from the right" (about 1 second for 100 rows): SELECT OD1, MAX(DO1) DO2
FROM (
    1)
)
GROUP BY OD1 od1 do2

---

10000	10002
10001	10002
10002	10002
10004	10005
10005	10005
10007	10007

3) Now I restrict the list "from the left" (about 1 second for 100 rows): SELECT MIN(OD1) od1, DO2
FROM (
        2)
)
GROUP BY Do2

od1 do2

---

10000	10002
10004	10005
10007	10007

I use Oracle 8.1.6. Is there any trick here that could solve or speed up this thing? Received on Mon Jul 23 2001 - 08:39:25 CDT