Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Efficient SELECT statement needed

Efficient SELECT statement needed

From: Colin Woods <newsgroupcw_at_hotmail.com>
Date: Fri, 22 May 1998 10:19:40 +0100
Message-ID: <3565432C.58F7@hotmail.com>


Folks

I need a efficient select statement as I need to process up 60,000 records from a single table.

Let say we have table T, that contains the following columns and data



| A | B | C | D | <-- Column names.


| 1 | x | y | t | <-- Column data.
| 2 | x | y | z |
| 3 | x | y | z |
| 4 | x | y | z |
| 6 | x | y | z |
| 7 | a | b | c |
| 8 | a | b | c |
| 9 | x | b | c |

What I need to do is SELECT everything from table T, but only SELECT one column for records that have consecutive values of A and equal values of B, C and D, and show the start and finish values of A as well as the values of B, C and D.

The above table would return the following result.



| Start A | Finish A | B | C | D |


| 1 | 1 | x | y | t |
| 2 | 6 | x | y | t |
| 7 | 8 | a | b | c |
| 9 | 9 | x | b | c |

We already have a solution to this problem using PL/SQL but it is much to slow when dealing with a large number of records. This solution selects all records from table T into a cursor. Each record was then processed one by one. When a 'run' is detected the finish A value is stored in an extra column E of the first record in the run, with all further records being deleted.

If anyone can provide a way to approach solving this problem it would be much appreciated.

Thank you

Colin Woods. Received on Fri May 22 1998 - 04:19:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US