Re: Select Distinct over Database Link

If you have any indexes on mylocaltable drop them before the insert.. have you tried
create table newlocaltable as select distinct mypkey from monster_at_mydblink; ? Is it as fast as the select distinct mypkey from monster_at_mydblink;

Just some thoughts...

gwhubert_at_hotmail.com (Gene Hubert) wrote:

>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

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Mon Aug 19 2002 - 16:00:28 CDT