Problem solve Get help with specific problems with your technologies, process and projects.

Querying more than 10 million rows over dblink

If I query more than 10 million rows from a table over dblink, will it cause any performance problems? If so, why?

Yes, it can cause a performance problem. But it really all depends on your query. Database links require a connection to the remote database. Typically, these connections run over TCP/IP. The local database queries the remote database for data from the table. This is called a "distributed query". That data must then be returned over the network to the local database. It is the transmission of this data over the network that can be the biggest performance killer of a distributed query. So when tuning this type of query, one tries to return as little data as possible over the network to satisfy the query. The Oracle8i Distributed Database Systems documentation does a pretty good job of discussing the mechanics of distributed queries and how to tune them. You can find a copy of this document at this URL: http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76960/toc.htm

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.