Home » SQL & PL/SQL » SQL & PL/SQL » create table from remote db (10 g)
create table from remote db [message #332945] Thu, 10 July 2008 03:08 Go to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I try to create table through remote db link like that:

create TABLE TMP_MAPE
TABLESPACE tmp_data1
 NOLOGGING COMPRESS PCTFREE 1
AS
SELECT  F.ORDER_DETAIL_ID_KEY, F.ORDER_ID_KEY, F.PRODUCT_ID_KEY, 
        F.CREATION_DT_KEY, F.REQUESTED_DT_KEY, F.WO_FINISHED_DT_KEY, 
        F.WO_RETURN_DT_KEY, F.PROD_INST_ID_KEY, F.ORDER_ITEM_STATUS, 
        F.CREATION_DT, F.DEVICE_RETURN_DT, F.WO_FINISHED_DT, 
        F.WO_RETURN_DT, F.EFF_START_DT, F.LAST_ACTION, 
        F.EFF_END_DT, F.CONTRACT_SIGN_DT, F.NOTE, 
        F.REQUESTED_END_DT, F.PARAMETRIC_PROD_IND, F.REJECT_REASON, 
        F.NO_OF_PRODUCTS, F.POPULATION_TM
FROM EWHTMOB.FCT_EWH_ORDER_DETAILS@dwhdatp1 F


But this query still running and nothing to do.
I make a connect to Oracle 10g too.

Could somebode tell me what should I do?


thanks

Re: create table from remote db [message #332947 is a reply to message #332945] Thu, 10 July 2008 03:11 Go to previous messageGo to next message
lazycat
Messages: 57
Registered: November 2005
Member
you can login on the remote db

and see the v$longops
and v$session_wait

tks
Re: create table from remote db [message #332948 is a reply to message #332945] Thu, 10 July 2008 03:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How big is the table going to be - how many rows?

Re: create table from remote db [message #332951 is a reply to message #332948] Thu, 10 July 2008 03:15 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
It's about 80.000 records
but another tables have a much more records like this one.

Re: create table from remote db [message #332953 is a reply to message #332951] Thu, 10 July 2008 03:21 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
I have no access to take a look at the the v$longops
and v$session_wait.

And what about to make a empty table
and after that to insert records?
Re: create table from remote db [message #332955 is a reply to message #332953] Thu, 10 July 2008 03:24 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
First Check how much time query takes to select the data from the remote table via firing your select query.

Regards,
Rajat Ratewal

[Updated on: Thu, 10 July 2008 03:24]

Report message to a moderator

Re: create table from remote db [message #332956 is a reply to message #332945] Thu, 10 July 2008 03:31 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
single select from remote table takes time very fast.
Do not recognise it's a remote table.
Re: create table from remote db [message #332958 is a reply to message #332956] Thu, 10 July 2008 03:36 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Define Fast First for this query??

SELECT  F.ORDER_DETAIL_ID_KEY, F.ORDER_ID_KEY, F.PRODUCT_ID_KEY, 
        F.CREATION_DT_KEY, F.REQUESTED_DT_KEY, F.WO_FINISHED_DT_KEY, 
        F.WO_RETURN_DT_KEY, F.PROD_INST_ID_KEY, F.ORDER_ITEM_STATUS, 
        F.CREATION_DT, F.DEVICE_RETURN_DT, F.WO_FINISHED_DT, 
        F.WO_RETURN_DT, F.EFF_START_DT, F.LAST_ACTION, 
        F.EFF_END_DT, F.CONTRACT_SIGN_DT, F.NOTE, 
        F.REQUESTED_END_DT, F.PARAMETRIC_PROD_IND, F.REJECT_REASON, 
        F.NO_OF_PRODUCTS, F.POPULATION_TM
FROM EWHTMOB.FCT_EWH_ORDER_DETAILS@dwhdatp1 F


Time taken to get results from this query.

Rajat Ratewal
Re: create table from remote db [message #332959 is a reply to message #332953] Thu, 10 July 2008 03:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would take as long.
The bottleneck is almost certainly bringing the data across the database link.
Re: create table from remote db [message #332961 is a reply to message #332959] Thu, 10 July 2008 03:40 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
The query takes time 480msec.
Re: create table from remote db [message #332962 is a reply to message #332959] Thu, 10 July 2008 03:42 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Mr. mape says it gives faster result.

Quote:

single select from remote table takes time very fast.
Do not recognise it's a remote table.


Regards,
Rajat

Re: create table from remote db [message #332971 is a reply to message #332961] Thu, 10 July 2008 03:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is this the time it takes to return all the rows, or just the first ones.
How long does this query take:
SELECT count(*) 
FROM   
(SELECT  F.ORDER_DETAIL_ID_KEY, F.ORDER_ID_KEY, F.PRODUCT_ID_KEY, 
        F.CREATION_DT_KEY, F.REQUESTED_DT_KEY, F.WO_FINISHED_DT_KEY, 
        F.WO_RETURN_DT_KEY, F.PROD_INST_ID_KEY, F.ORDER_ITEM_STATUS, 
        F.CREATION_DT, F.DEVICE_RETURN_DT, F.WO_FINISHED_DT, 
        F.WO_RETURN_DT, F.EFF_START_DT, F.LAST_ACTION, 
        F.EFF_END_DT, F.CONTRACT_SIGN_DT, F.NOTE, 
        F.REQUESTED_END_DT, F.PARAMETRIC_PROD_IND, F.REJECT_REASON, 
        F.NO_OF_PRODUCTS, F.POPULATION_TM
FROM EWHTMOB.FCT_EWH_ORDER_DETAILS@dwhdatp1 F;)
Re: create table from remote db [message #333021 is a reply to message #332971] Thu, 10 July 2008 05:30 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
The query of count takes time 90msecs.
I don't know what is a really should be written
but whenever I run create table as select ...
query is running and running and that's all.
Re: create table from remote db [message #333030 is a reply to message #333021] Thu, 10 July 2008 06:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm sorry, but I'm having a hard time believing you.
I srongly doubt that your query is bringing 80,000 records across a database link in less than 0.5 seconds.

Can you post a cut and paste of a SQL*Plus session showing you running the COUNT query and showing the results. If you do 'set timing on' before you run the query, that will provide a timing for the execution.
Re: create table from remote db [message #333085 is a reply to message #333030] Thu, 10 July 2008 08:41 Go to previous messageGo to next message
mape
Messages: 241
Registered: July 2006
Location: Slovakia
Senior Member
Ok, I did 'set timing on' and the time result of query is:
00:01:03.71.
Number of records is 14604564.

Re: create table from remote db [message #333088 is a reply to message #333085] Thu, 10 July 2008 08:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, then it's pretty clear what the problem is.
You're trying to fetch 14,000,000 records, each of 23 fields, over a database link.
That's going to take a very long time, and there's very little you can do about it.

The Count query is probably being executed on the remote database - you could do an explain plan if you like to check that.

I suspect that you might be better off exporting the table at the far end and importing it in your local Db, or dumping the table contents to a flat file and loading it using an external table or SQL Loader.
Re: create table from remote db [message #333194 is a reply to message #333088] Thu, 10 July 2008 15:32 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... or, perhaps / if possible, create a materialized view in the local database as select from the remote table (and, of course, use materialized view in a query).
Re: create table from remote db [message #333284 is a reply to message #333088] Fri, 11 July 2008 02:34 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
My assumption here is OP is having a dblink setup between two database which are of the same database version or atleast 10g and above. We had issues in the past when dblink from 10g to 8i or 9i. Sometimes it just hangs and we need to flush the shared pool to get it back working.

We do pull loads of data (in the range of 10M - 130M records and from 5 columns to 85 columns with total record size around 2 GB - 10GB) over dblink and it finishes in a reasonable amount of time (less than 1 hour during batch window and probably around 1.5 hours during online day). I would expect a similar sort of timing if you use pro*c routine and ftp and direct path load the data or probably little less than that. But the hassle of maintaining three scripts to the maintenance of one script we opt for the dblink and we never had any issues.

Just a thought.

Regards

Raj

[Edit:] This message was intended to be addressed to OP but by mistake @JRowbottom, I addressed this to you.

P.S : I have not bench-marked it step by step so I cannot say for sure.

[Updated on: Fri, 11 July 2008 02:52]

Report message to a moderator

Previous Topic: Dynamic Insert Statement
Next Topic: how to exchange the non partiton table to partition table
Goto Forum:
  


Current Time: Mon Dec 05 04:35:43 CST 2016

Total time taken to generate the page: 0.09647 seconds