Getting (large) data via db link

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.

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.

Quote:

---

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.

If you do need a join,
you could consider using the 'DRIVING_SITE' hint

Hope this helps
Gert

Quote:

---

Originally posted by pmiletic

Quote:

---

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.