How to duplicate rows in a table?

Hi,

I wonder whether anybody has an idea of how to enhance the following: Suppose a table which has 2-segment-trips in it (i.e. somebody took the train/plane in order to go from New York to Boston and then to Dallas, i.e. two segments: New York-Boston, Boston-Dallas) as follows:

Table TRIPS:
START VARCHAR2(32),
VIA VARCHAR2(32),
END VACRHAR2(32),
PAX NUMBER Now I want to flatten this TRIP-table into another table which only has "segments":
Table SEGS:
START VARCHAR2(32),
END VARCHAR2(32),
PAX NUMBER What I'm currently doing is this:
INSERT /*+APPEND*/ INTO SEGS (
select START,VIA,PAX FROM TRIPS
union all
select VIA,END,PAX FROM TRIPS
)

But this requires two full table scans on TRIPS (which is a large table...).
Is there any way to circumvent the second full table scan? My human brain tells me something like: Take a row from TRIPS and make two out of it! But how do I tell it Oracle?

One idea in the project was to use a dummy temporary table which has 2 rows in it and will be joined with TRIPS without any join condition: This would result in a cartesian join which doubles all rows. But it lasts even longer.

Any other elegant solution to this problem?

Best regards,
  Alex Received on Tue Apr 11 2006 - 06:51:03 CDT