Home » SQL & PL/SQL » SQL & PL/SQL » DB Link or Temp Table? (10g)  () 1 Vote
DB Link or Temp Table? [message #353384] Mon, 13 October 2008 12:51 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Not sure if this is a *bad* question. But just curious, if I were to fetch some data from another database (some big amount of data). Is it good to use dblink or is it much better to create staging table from the other database, populate it, and then just select from that staging table? Thanks.
Re: DB Link or Temp Table? [message #353389 is a reply to message #353384] Mon, 13 October 2008 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No general answer, it depends on each case.

Regards
Michel
Re: DB Link or Temp Table? [message #353390 is a reply to message #353389] Mon, 13 October 2008 13:16 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thanks Michel, I just ask that because we were ask to change the "dblink" style to staging table style because they said it slows down the servers.
Re: DB Link or Temp Table? [message #353477 is a reply to message #353384] Tue, 14 October 2008 00:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
if you are creating a staging table locally then there are some obvious points of interest:

Quote:
1) both solutions require you to move the same amout of data across your network
2) both solutions require you to consume memory/disk space to house rows for use locally

so far not much difference

Quote:
3) staging tables can be commited thus making the data viewable after use (good for debugging)
4) staging tables can be queried over and over without incurring the network overhead that would be required in referencing the data more than once on the db_link
5) staging tables can be indexed thus making access to them potentially faster if the rowset is indeed queried different ways

sounds good for staging tables so far, but...

Quote:
6) staging tables require maintenance even if only to remember to truncate them when you are done with them, otherwise they continue to consume space with data not being used
7) staging tables are by definition a form a snapshot and thus are immediately stale after they are populated. This can be good if you don't want the picture to change, bad if you need to keep in synch with updates while you work with the data copy

So, the question is not should I use a link or staging table, but rather, how do the behavioral differences between the two strategies align to the data processing goals supporting my business need.

As for "slowing down the servers", what do they expect will be different when a staging table is used? If the data is referenced only once, then there will be little difference. Ask the people who told you "it slows down the servers" to explain their reasoning because I am not sure they know what they are talking about.

Good luck, Kevin

[Updated on: Tue, 14 October 2008 00:48]

Report message to a moderator

Re: DB Link or Temp Table? [message #353481 is a reply to message #353477] Tue, 14 October 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very good summary, Kevin. ./fa/2115/0/

Regards
Michel
Re: DB Link or Temp Table? [message #353488 is a reply to message #353390] Tue, 14 October 2008 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And how is this staging table getting its data?

[edit: hm.. should not have kept this open so long. Kevin beat me waaaaay before]

[Updated on: Tue, 14 October 2008 00:53]

Report message to a moderator

Re: DB Link or Temp Table? [message #353639 is a reply to message #353477] Tue, 14 October 2008 07:48 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, Thanks for all the replies. The query on the "source db" runs for about 2-3 hours. It is a big summary query, which just runs once in a while for a sample day.

I've read an article yesterday saying also that the cons of doing the query over a database link would be the "processing of the data" (i.e. sorting, joins, etc.). Compared to the just pulling the process data from a staging table.

What I did now is create a staging table from the source db, then truncate that table every time I need to reload. Then just insert-select to our table on our database. I've put an index on that staging table. One thing I noticed is that when I ran the query from the source db, it is using the index (table has been analyzed). But when I ran that query from our database, adding the dblink, it just do a full table scan. Is that the expected behavior? Or do I need to configure something else?

Also not sure if this has already been answered. Is the running a query over a database link and running it directly on that source db have that much of a difference of putting the "pressure" on the source db? (not considering the load on network issue)

Thank you very much again.


[Updated on: Tue, 14 October 2008 07:52]

Report message to a moderator

Re: DB Link or Temp Table? [message #353668 is a reply to message #353384] Tue, 14 October 2008 09:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I presume the staging table you are creating exists on your local database, not the remote database that is the source of the data? Last time I was doing this I recall the following:

Quote:
1) truncate does an implied commit
2) there are issues with doing commits on a remore site in a distributed transaction. Your transaction will fail in a wierd way. Don't have the error message or number handy thought sorry.


Are you doing a truncate on the remote site? I presume not because if things are still the same, your transaction with changes pending locally, would die.

Kevin

Re: DB Link or Temp Table? [message #353699 is a reply to message #353668] Tue, 14 October 2008 13:30 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, Thanks again. Here's how the current setup goes:


database A

create table..a_stg

procedure procA is
begin
     execute immediate..truncate a_stg;

     insert into a_stg
     select sum()...etc.;
end;

We ran first this procA here in database A

Then in...

database B

procedure procB is
begin
     insert into b_table
     select ... from a_stg@databaseA;
end;

Run procB.



Thanks again.
Re: DB Link or Temp Table? [message #353702 is a reply to message #353699] Tue, 14 October 2008 13:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK, so I had problems with this kind of process. These problems showed up about one year ago for me (9i database) so Oracle may have relaxed some rules since then but...

Quote:
If you are in the middle of a distributed transaction with changes on the local database, and you try to issue the truncate on the remote site to truncate your remote table, you will get an error and the transaction will be canceled.

This is what I remember.

I solved my problem be creating my staging table on the local database. Then the truncate is issued on the local site and hence the commit implied by the truncate is issued on the local site.

Can anyone else offer more insight?

Kevin x79427
Re: DB Link or Temp Table? [message #353707 is a reply to message #353702] Tue, 14 October 2008 15:10 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi, Thanks again. I learned something new =)


I tried your cases, if this is the correct flow. Based on this, DDL operations are not allowed remotely if the DDL is being executed from the local site. Unless the DDL is executed ON the remote site.



Session 1:

create table t_at_db1(x int);

insert into t_at_db1 values(2);


Session 2:

create table t_at_db2(x int);
insert into t_at_db2 values(5);

create procedure p is
begin
    execute immediate 'truncate table t_at_db2';
end;
/

Session 1:

SQL> begin
  2      update t_at_db1 set x = 4;
  3      execute immediate 'truncate table t_at_db2@link';
  4      --p@link;
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database
ORA-06512: at line 3

Using procedure

SQL> begin
  2      update t_at_db1 set x = 4;
  3      --execute immediate 'truncate table t_at_db2@link';
  4      p@link;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Re: DB Link or Temp Table? [message #353709 is a reply to message #353707] Tue, 14 October 2008 16:01 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
good job. However I wonder if there is more (though maybe not for you).

I recall the commits on the remote site, during a distributed transaction where disallowed.

Maybe I am mixed up though.

Anyone else know?

Kevin
Previous Topic: help with query
Next Topic: datatypes question
Goto Forum:
  


Current Time: Fri Dec 09 11:42:32 CST 2016

Total time taken to generate the page: 0.09365 seconds