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
- Dozens more answers to tough Oracle questions from Brian Peasland are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.