-
I need to query 3.5 million rows from remote database on a regular basis. Query will take subset of columns with or without condition.
I am still not sure the way Oracle will behave when data are queried across db link: will all rows with all columns be moved, or will it be transfer all rows with requested subset of columns, or ...?
I did notice speed up if querying a view on remote database comparing with executing full statement locally.
Any advice on what could be the most efficient way of transferring large amount of data via db link.
Or maybe instead of using db link, I do spool and load data !?
-
Oracle locates all queried data in the buffer of remote database and returns to you only those fields which you specified in 'SELECT' clause.
You may use COPY command to transfer data.
Best wishes!
Dmitri
-
If you're doing a simple select statement with where conditions, the query will be processed at the remote site and just the rows that satisfy the where clause will be returned. However, if you are joining a local and remote table, the query will be performed locally.
Jeff Hunter
-
Originally posted by kgb
You may use COPY command to transfer data.
What COPY command?
-
Consider setting up a snapshot to be built during the off hours. Your query can then run locally against the snapshot, urh, materialized view.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
-
If you do need a join,
you could consider using the 'DRIVING_SITE' hint
Hope this helps
Gert
-
Originally posted by pmiletic
Originally posted by kgb
You may use COPY command to transfer data.
What COPY command?
Copy - this is sqlplus command, that use syntax:
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table[(column, column, column, ...)] USING query
where database has the following syntax: username[/password]@connect_identifier
Copies data from one Oracle database to a table in another. APPEND, CREATE, INSERT or REPLACE specifies how COPY treats the existing copy of the destination table (if it exists). USING query identifies the source table and determines which rows and columns COPY copies from it.
-
Thanks for your assistance guys.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|