Re: Select Distinct over Database Link

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
Received on Wed Aug 21 2002 - 09:17:15 CDT