See Oracle Query principle from SELECT statement (Understanding Oracle's query mechanism)

The first step: the client sends the statement to the server-side execution

When we execute a SELECT statement on the client, the client sends the SQL statement to the server side, allowing the server-side process to process the statement. That is, the Oracle client does nothing, and his main task is to send some SQL statements generated by the client to the server side. Although there is a database process on the client side, the role of this process is not the same as the process on the server. The database process on the server does not handle the SQL statement. However, there is a problem to note that the client process and the server process is one by one corresponding. In other words, after the client connects to the server, a process is formed between the client and the server, and we are called the client process on the client side, and we are called the server process on the server. So, because all SQL statements are executed by the server process, some people liken the server process to the "shadow" of the client process.

Step Two: Statement parsing

When the client sends the SQL statement to the server, the server process parses the statement. In the same vein, this parsing work is done on the server side. Although this is only an analytic action, it will do a lot of "gestures".

1. Query the cache. The server process does not go directly to the database query when it receives the SQL statement that the client sends over. Instead, the database is first looked up in the cache, if there is an execution plan for the same statement. If in the data cache, just someone else is using the query, the server process executes the SQL statement directly, eliminating the subsequent work. Therefore, the use of high-speed data cache, you can improve the query efficiency of SQL statements. On the one hand, it is more efficient to read data from memory than data files in the hard disk, on the other hand, because of the reason for this statement parsing.

However, it is important to note that this data cache is not the same as the data cache of some client software. Some client software sets up the data cache on the client side of the application in order to improve query efficiency. Due to the existence of these data caches, the query efficiency of the client application software can be improved. However, if the other person in the server made the relevant changes, due to the application of the data cache, the data can not be reflected in the changes in time to the client. From this, it can be seen that the data cache of the application software is not matter with the database server's high-speed data cache.

2. Check the legality of the statement. When the corresponding SQL statement is not found in the cache, the database server process begins to check the validity of the statement. The main point here is to check the syntax of the SQL statement to see if it conforms to the grammatical rules. This error message is fed back to the client if the server process considers the SQL statement to be inconsistent with the syntax rules. In the course of this syntax check, the SQL statement contains the table name, column name, and so on SQL he is only a grammar check.

3. Language meaning check. If the SQL statement conforms to the syntax definition, the server process then checks the fields, tables, and so on in the statement. Look at the fields and whether the tables are in the database. If the table name and column name are inaccurate, the database will feed back the error message to the client.

So, sometimes when we write the SELECT statement, if the syntax and table name or column name at the same time, the system is prompted to say syntax error, wait until the syntax is completely correct, then prompted to say that the column name or table name is wrong. If you can master this order, you can save time when the application is wrong.

4. Get the object resolution lock. When the syntax and semantics are correct, the system will lock the objects we need to query. This is mainly to ensure the consistency of the data, to prevent us in the process of the query, other users of the structure of this object changes. For the principle and method of locking, I have in other articles have been specifically described, here will skip the talk.

5. Check the data access rights. After the syntax and semantics are checked, the client is not necessarily able to get the data. The server process also checks to see if the user you are connecting to has permission to access the data. If the user you connect to the server does not have access to data, the client will not be able to obtain the data. Therefore, sometimes we query the data, hard to write SQL statements, compiled through, but finally, the system returned a "No access to data" error message, let us angry half-dead. This may be encountered during the development and debugging of the front-end application software. Therefore, to be aware of this problem, the database server process checks the syntax and semantics before it checks access rights.

6. Determine the best execution plan. The server process does not directly query the database file when there is no problem with the syntax and the permissions match. The server process optimizes the statement according to certain rules. However, it is important to note that this optimization is limited. Generally in the process of application software development, the SQL language of the database needs to be optimized, the role of this optimization is much larger than the server process of self-optimization. Therefore, generally in the application software development time, the database optimization is indispensable.

When the optimizer of the server process determines the best execution plan for this query statement, the SQL statement is saved with the execution plan to the data cache. In this case, when there is this query, we omit the syntax, semantics, and permission check steps, and execute the SQL statement directly to improve the efficiency of SQL statement processing.

Step three: Statement execution

Statement parsing is simply parsing the syntax of the SQL statement to make sure that the server knows exactly what the statement is meant to say. The database server process does not actually execute this SQL statement until the statement resolution is complete.

This statement is executed in two different cases. The first is that if the data block of the selected row is already read into the data buffer, the server process will pass the data directly to the client instead of querying the data from the database file. If the data is not in a buffer, the server process queries the data from the database file and puts the data into the data buffer.

It's still important to note that there are many kinds of caches defined in the Oracle database. Like the SQL statement above, the cache is cached with the now-spoken data cache. When we were learning the database, we needed a clear understanding of the caches and the role of each kind of cache. This is very useful for our subsequent database maintenance and database optimization.

Fourth Step: Extract Data

When the statement executes, the queried data is still in the server process and has not been routed to the client's user process. So, in the server-side process, there is a piece of code that is dedicated to extracting data. His role is to return the results of the query to the client process, thus completing the entire query action.

From this entire query processing process, we in the database development or application software development process, we need to pay attention to the following points:

One is to understand that the database cache is not the same as applying the software cache. The database cache exists only on the database server side and does not exist on the client. Only then can you ensure that the content in the database cache is consistent with the contents of the database file. In order to prevent the occurrence of data dirty reading and wrong reading according to the relevant rules. While the application software involves the data cache, because with the database cache is not matter, therefore, the application software data cache although may improve the data query efficiency, but, but has broken the data consistency request, sometimes may have the dirty reading, the wrong reading and so on occurrence. So, sometimes, there is a special feature on the application that is used to clear the data cache when necessary. However, the purge of this data cache simply clears the data cache on this computer, or simply clears the application's data cache without clearing the database's data cache.

The second is that most SQL statements are handled in accordance with this process. Our DBA or Oracle database-based developers understand the processing of these statements, which is very helpful for us to develop and debug the SQL statements involved. Sometimes, mastering these principles can reduce the time we get out of the wrong line. In particular, it is important to note that the database is the examination of the data query permission after the syntax semantics. Therefore, sometimes if the optical database of the Authority control principle, may not meet the needs of application software permissions control. At this point, you need to apply the software foreground settings, to achieve the rights management requirements. And, sometimes the application database Rights management, but also a bit cumbersome, will increase the workload of server processing. Therefore, for query permission control of records, fields, and so on, most programs involve people who prefer to implement in the application rather than on the database.

See Oracle Query principle from SELECT statement (Understanding Oracle's query mechanism)