Re: Select Distinct over Database Link

couple things you might try:

Create your local table before the run:

create table my_local_table
(mpkey an_appropriate_datatype)
storage (initial 1M next 1M ...etc)
NOLOGGING
/

Insert using the APPEND hint and the NOLOGGING option:

insert /*+ APPEND */ into my_local_table select distinct(mpkey) from monster_at_mydblink NOLOGGING
/

When you are doing the select via sqlplus, no rollback is needed. When you are doing the INSERT, (local) rollback is needed.

The nologging will speed things up a little. Having pre-allocated the local table will speed things up a little.

select distinct(mpkey) from mon
"Gene Hubert" <gwhubert_at_hotmail.com> wrote in message news:7e3fa619.0208200341.4fa14f54_at_posting.google.com...
> There are no indexes on the local table. I have tried "create table
> as select" with no improvement.
>
> Thanks,
> Gene Hubert
>
> TurkBear <jgreco1_at_mn.rr.com> wrote in message
news:<s0n2muco83iqa72f38idma3fe6cfg96i3i_at_4ax.com>...
> > 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 Tue Aug 20 2002 - 15:01:40 CDT