Select Distinct over Database Link

Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production SunOS 5.6

I'm having a problem with a query over a database link. If (in SQLPlus) I interactively do:

select distinct mypkey from monster_at_mydblink;

The result set (almost 1 million records) scrolls across my screen in about 15 minutes. monster_at_mydblink has a bit under 500 million records in it.

Now if I do:

insert into mylocaltable
select distinct mypkey from monster_at_mydblink;

The query hangs or runs out of temp space if I let it go long enough. I've tried the DRIVING_SITE hint but to no effect. It acts like all 500 million records are going over the network to the database where I am logged on.

I'm looking to get the latter query to run in some reasonable time, say 30 minutes more or less. Thanks for any assistance,

Gene Hubert
Durham, NC Received on Mon Aug 19 2002 - 13:15:05 CDT