Database performance tuning: Understanding

I. Introduction

This text works is the third on the performance of database performance tuning. Works on a text () stresses that in-depth understanding of planning to run a single table, single table operation planning is to understand the basis for planning multi-table operation.

Connect two tables, there are three running methods: 

1) nested loop join; 

2) hash connection; 

3) merge connection. 

Two tables in connection select which of the three? This depends on the index, and the connection cost. Highlights of the third article in the (paper) say that the text works, nested loop join, Part IV stresses that the text works hash connection, the fifth chapter about the works that merge the text link. Will be planned later in the sixth chapter, and EXISTS IN subquery subquery.

View the Gallery NO · 1 chapter:

Dream up a database, oracle database, sql server database, the database run planning is not fundamentally different, so the application works up to a dream text article database as an example database to stop planning, this text works we select oracle 10g database as an example.

After reading this article, should be able to read these three databases nested loop join operation planning. Another point is the stated demand: As the oracle of the stream of code is not open, and I got it running plans description of the basis, the cost of cost and the 10,053 documents the results of an end to anti push, though, as the direction from the large sense, does not appear difficult, just do start a discussion.

Second, in-depth understanding of the nested loop operation planning

Oracle database to run programs appear daily in two ways:

• 
  
• 1) autotrace on command;
• 
  
• 2) explain plan for the command;

Example application set autotrace command:

We did not score that run the "set autotrace on explain;" statement, the following query, insert, update, delete statement, there will be planning to run until the run "set autotrace off;" statement. If it is set "set autotrace on;", in addition to planning a run outside, there will be a part of the amount of case information useful visit. This highlights the text works do not involve assessment of the cost of the planning inquiry. 6 pp.