The execution process of Oracle database SQL statements

The first is the simplest link database process, the JDBC standard linking process:

1. Load the JDBC driver and generally load the driver with the use of class. Forname () Reflection to load the drive

Oracle:Class.forName ("Oracle.jdbc.driver.OracleDriver");

SQLServer:Class.forName ("Com.microsoft.jdbc.sqlserver.SQLServerDriver");

MySql:Class.forName ("Com.mysql.jdbc.Driver");

2. Define the URL, primarily to specify the database hostname, port, and database name of the desired link.

Oracle:url = "Jdbc:oracle:thin: @localhost: 1521:databasename";

Sqlserver:url = "Jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=databasename";

Mysql:url = "Jdbc:mysql://localhost:3306/databasename"

3. Establish a connection

With the database URL username password can establish a connection with the database, the connection class is Connection

Connection conn = drivermanager.getconnection (URL address, user name, password);

4 Creates a statement object that is used by the statement object to send queries and commands to the database. It is created by the connection createstatement () method

Statement stmt = Conn.createstatement ();

5 Execute the query or update, with the statement object you can use its executequery () method to send the SQL query

String sql = "SELECT * FROM table";

The ExecuteQuery method of the ResultSet result set Object Statement object returns a result set object

ResultSet rs = stmt. ExecuteQuery (SQL);

Common methods for statement objects:

ExecuteQuery (): Executes a SQL query and returns data in ResultSet, resultset may be empty, but not null.

Executeupdate (): Executes update, INSERT, delete command, returns the number of rows affected, which can be 0. It also supports DDL commands, such as CREATE table, DROP table, ALTER TABLE.

6. Processing results

The result of the processing can be moved in the table using the next method of resultset each row resultset the next method returns a Boolean value of TRUE indicates that there is data below, in each row of results we can use the ResultSet object's GetXXX ("Field name") Gets the data for the field in the row with GetString (""); GetInt (""); You can also use GetXXX (index number): Here the index number starts at 1.

Example: while (Rs.next ()) {

String a = rs.getstring ("ID");

}

7 Closing the connection

Connection.close (); Close connection

Closing the connection closes the corresponding statement and ResultSet objects

Written to this place, saying that using PreparedStatement to perform precompiled SQL statements can be more secure than using a string.

String sql = "SELECT * from table where a=?" and B =? "Where A is a character type data B is an integer

PreparedStatement PSTMT = conn. PREPAREDSTATEMENGT (SQL);

Pstmt.setstring (1, "www"); The first question mark value is WWW

Pstmt.setint (2,5); The second question mark has a value of 5

ResultSet rs = Pstmt.executequery ();

 Public Static Connection getconnection () {    null; Try {     class.forname ("Oracle.jdbc.driver.OracleDriver");   Load Hardware driver     String dburl = "Jdbc:oracle:thin: @localhost: 1521:dataname";  Defining URLs

     String username = "Test";     = "123456";     = drivermanager.getconnection (dburl, username, password);//Establish link   }catch( ClassNotFoundException e) {           e.printstacktrace ();   } Catch (SQLException e) {           e.printstacktrace ();   }        return Conn;}

In the framework of SPRING-JDBC, there is a template class: JdbcTemplate, which encapsulates these processes directly, for example:

@Override PublicAccount Read (string username) {String sql= "SELECT * FROM account WHERE username =?"; Object Object=NULL; Try{Object=jdbctemplate.queryforobject (SQL,Newobject[] {username}, accountrowmap); } Catch(emptyresultdataaccessexception e) {return NULL; }        return(account) object; }

Accountrowmap implementation of the RowMapper interface, is the 6 above said processing results.

Large aspects can be said: Load the driver (motive), define the URL (to determine the target), establish a link (to determine the relationship), create a statement object (get the object), execute the query (start a relationship), process the result (communication score), close the link (completion process). The specific Java code is as follows:

This is a relatively large aspect, specifically, we focus on the implementation of the process, what happened during this time is more complex, such as: How SQL statements are handled in the database, the order of execution?

Under the Oracle Database system architecture, the SQL statement is generated by the user process and then to the corresponding server process, which is then executed by the servers process, and if the SELECT statement, the server process also needs to pass the execution results back to the user process.

The first is the user generated SQL statements, to the corresponding service side of the process, and then take the SQL execution order.

The execution of SQL statements is generally as follows:

Parse--bind (BIND)-Execute (Execute)-Extract (fetch only requires this step for select)

Analytical

When a server process receives an SQL statement, it first translates it into the most efficient step to execute the SQL statement, which is known as the execution plan.

Step 1: Check the shared pool for the SQL text, parse tree, and execution plan that were stored after the same SQL statement was parsed. If the generated execution plan can be found from the cache library of the shared pool, then the SQL statement does not need to be parsed again, it can directly be cached by the library to get the resulting execution plan, and thus directly jump to the binding or execution phase, which is called Soft parsing .

However, if the corresponding execution plan cannot be found in the library cache of the shared pool, you must continue parsing the SQL, generating the execution plan, which is called a hard parse

In the buffer pool resolved SQL, there will be a corresponding hash value corresponding to it, you can query through the V$sql view, see the following example:

Sql>SELECT *  fromSCOTT. DEPTWHEREDEPTNO=Ten; SQL>SELECT *  fromSCOTT. DEPTWHEREDEPTNO= -; SQL> SELECTHash_value, ADDRESS, executions, Sql_text2  fromV$sql3 WHERESql_text like 'SELECT * from SCOTT. DEPT WHERE deptno%'4 ; Hash_value ADDRESS Executions Sql_text---------- -------- ---------- --------------------------------------------------------------------------------44283662527ee4b7c1 SELECT *  fromSCOTT. DEPTWHEREDEPTNO= -421540549427eea3bc1 SELECT *  fromSCOTT. DEPTWHEREDEPTNO=Ten

Let's clear the execution plan for the shared pool cache first, and then use the binding variable to see the transformation of the execution plan

Sql> ALTERSYSTEM FLUSH Shared_pool; System Alteredsql>VARIABLE Deptno Number; SQL> EXECUTE:d Eptno:= Ten;P L/Sqlproceduresuccessfully Completeddeptno---------TenSQL> SELECT *  fromSCOTT. DEPTWHEREDEPTNO=:d eptno;deptno dname LOC------ -------------- -------------TenACCOUNTING NEW Yorksql> EXECUTE:d Eptno:= -;P L/Sqlproceduresuccessfully Completeddeptno--------- -SQL> SELECT *  fromSCOTT. DEPTWHEREDEPTNO=:d eptno;deptno dname LOC------ -------------- ------------- -Dallassql> SELECTHash_value, ADDRESS, executions, Sql_text2  fromV$sql3 WHERESql_text like 'SELECT * from SCOTT. DEPT WHERE deptno%'; Hash_value ADDRESS Executions Sql_text---------- -------- ---------- 366930297927d2ba1c2 SELECT *  fromSCOTT. DEPTWHEREDEPTNO=:d Eptno

Step 2: parsing, analyzing whether the syntax of SQL statements conforms to specifications, and measuring the meaning of expressions in statements

Step 3: Check for semantic errors and permissions. Semantic analysis, check that all database objects designed in the statement exist, and that the user has appropriate permissions.

Step 4: view transformations and expression conversions translate query statements that involve views into corresponding base table query statements. Convert complex expressions to simpler equivalent join expressions.

Step 5: decide on the best execution plan. The optimizer generates multiple execution plans that are brought into the statistics to find the execution plan with the least execution cost as the execution plan for executing this SQL statement

Step 6: Cache SQL text, parse tree, execution plan to library cache, store address, and hash value of SQL statement.

Binding

If a binding variable is used in the SQL statement, the declaration of the bound variable is scanned, and the bound variable is assigned a value. The value of the variable is then brought into the execution plan.

Perform

This phase executes SQL according to the execution plan, producing the result of execution. Different types of SQL statements, and the execution process is different.

Select query

Check that the required data block is already in the buffer cache, and if it is already in the buffer cache, the contents of the direct reader can be. This type of reading is called logical reading . If the required data is not in the buffer cache, the server process needs to scan the data block and read the corresponding data block to the buffer cache, which is called a physical read . Compared to logical reads, it consumes more CPU and IO resources.

Modify operation (INSERT, UPDATE, DELETE)

Step 1: Check that the required database has been read into the buffer cache. If buffer cache already exists, execute step 3

Step 2: If the required database is not in the buffer cache, the server reads the data block from the data file into the buffer cache

Step 3: Lock the data row for the table you want to modify (row Exclusive Lock), and then take an exclusive lock on the data row that you need to modify

Step 4: Copy the redo record of the undo data to the log buffer, generate the data row revocation data, copy the Redo record modified by the data row to the log buffer, modify the data row.

Step 5: revocation data to generate data modifications

Step 6: copy data modified redo records to the log buffer

Step 7: Modify the contents of the data row and, if the previous buffer is a clean buffer, it will become dirty buffering.

Extraction

Extracts only the steps of a SELECT query statement. Gets the row of records for the query and, if necessary, sorts the results of the query.

Modify the http://www.cnblogs.com/kerrycode/p/3356845.html above

The execution process of Oracle database SQL statements