Re: How to duplicate rows in a table?

<schonlinner_at_yahoo.com> a écrit dans le message de news: 1144756263.903968.24640_at_g10g2000cwb.googlegroups.com...
| 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
|

insert all
into segs values(start, via, pax)
into segs values(via, end, pax)
select start, via, end, pax from trips
/

Regards
Michel Cadot Received on Tue Apr 11 2006 - 11:27:46 CDT