Re: Select Distinct over Database Link

Just 1 additional try:

Have you tried

Insert into mylocaltable
Select mykey from ( select distinct mykey from monster_at_mydblink );

This should push the distinct to the remote server and just return the subset..

gwhubert_at_hotmail.com (Gene Hubert) wrote:

>Thanks very much to all who responded. The problem was a good chance
>to learn more about explain plan and distributed queries. I found
>searches of this group to be much more useful than the Oracle docs for
>my situation. Ultimately I was inititated into the arcane ectasies of
>"THE OTHER" when using explain plan.
>
>Indeed it did turn out that there is a huge difference between:
>
>select distinct mykey from monster_at_mydblink;
>and
>insert into mylocaltable
>select distinct mykey from monster_at_mydblink;
>
>In the first case the distinct is handled on the remote db and only
>distint values of mykey are sent over the network from the romote
>table. In the 2nd case the distinct is handled on the local db so
>mykey for all records from the remote table are sent across the
>network. No amount of driving_site hints seemed to make any
>difference.
>
>A view on the remote db solved the problem but I hate creating views
>unless absolutely necessary.
>
>Turns out the other solution is:
>
>insert into mylocaltable
>select mykey from monster_at_mydblink group by mykey;
>
>The group by is passed to the remote db so only the distinct values of
>mykey go over the network.
>
>I also hate using group by when what I really want is distinct but
>probably less than I hate creating views so I'll probably go with the
>group by option.
>
>Thanks again and best luck if you have to confront "THE OTHER".
>
>Gene /*+ driven_crazy */ Hubert
>Durham, NC
>
>
>gwhubert_at_hotmail.com (Gene Hubert) wrote in message news:<7e3fa619.0208191015.52f9b18f_at_posting.google.com>...
>> 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 Wed Aug 21 2002 - 11:56:40 CDT