Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Select Distinct over Database Link

Re: Select Distinct over Database Link

From: <Kenneth>
Date: Tue, 20 Aug 2002 14:59:11 GMT
Message-ID: <3d625712.3713780@news.capgemini.se>


On Tue, 20 Aug 2002 17:33:29 +0400, "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote:

The commit frequency was certainly not meant to solve the problem (please READ my post before self-making such assumptions.......Hmm).

The point was simply : When Gene fetches the 1M rows one by one, he has no problems. When he bulk inserts the rows, he gets problems for some reason impossible to know without execution plan. Thus, fetching the rows one by one and inserting them one by one would most likely not give any problems.

Those "intermediate" commits, as you call them, are just good practice when inserting > 1M records. Nothing to do with the problem itself.

>Hmm... Don't think that introducing intermediate commits will improve the
>situation - indeed it looks like the insert first attempts to transfer the whole
>table and then sort it and remove duplicates. Committing more often won't
>help if this is the case - transfer and sort will take place anyway. My guess is
>that providing adequate temp space is the solution. Another solution would
>be something like this:
>
>alter table mylocaltable add constraint pk$mylocaltable primary key(mypkey);
>(on empty table)
>
>begin
> for rec in (select * from monster_at_dblink) loop
> begin
> insert into mylocaltable values(rec.mypkey, rec.col1, ...);
> exception
> when dup_val_on_index then null; -- just ignore duplicates
> end;
> end loop;
>end;
>/
>commit;
>
>This will surely run for *some* time as Oracle will also update pk index,
>but it will not require any sorting and will process data as it comes from
>dblink, so temp space shouldn't be of concern. You can measure speed
>by limiting the query in FOR loop to some 100,000 rows first (using
>where rownum <= 100000) and see if projected speed is acceptable, then
>truncate the table, remove limit and run block again.
>
>Corrections and additions welcome.
>
>--
>Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
>Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
>All opinions are mine and do not necessarily go in line with those of my employer.
>
>
><Kenneth Koenraadt> wrote in message news:3d6232b7.18578474_at_news.capgemini.se...
>> On 20 Aug 2002 04:41:20 -0700, gwhubert_at_hotmail.com (Gene Hubert)
>> wrote:
>>
>> It seems that your statement
>>
>> insert into mylocaltable
>> select distinct mypkey from monster_at_mydblink;
>>
>> first fetches all the 500 M rows and then does the sort on your local
>> DB. Check the execution plan.
>>
>> Anyway, an easy solution is to create a PL/SQL block to do the job,
>> like this :
>>
>> declare
>>
>> cursor mycur is
>> select distinct(mypkey)
>> from monster_at_mydblink;
>>
>> COMMIT_FREQUENCY CONSTANT binary_integer := 100000;
>>
>> i binary_integer := 0;
>>
>> begin
>>
>> for mycur_rec in mycur loop
>> insert into mylocaltable values (mycur_rec.mypkey);
>> i := i + 1;
>> if mod(i,COMMIT_FREQUENCY ) = 0 then
>> commit;
>> end if;
>> end loop;
>>
>> commit;
>>
>> end;
>> /
>>
>>
>>
>>
>> >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 - 09:59:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US