Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to use update sub-query, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1, related subquery and unrelated subquery

In a non-associative subquery, the internal query executes only once and returns its value to the external query, and then the external query uses the value returned by the internal query in its processing. In the associated subquery, the internal query is executed once for each row of data returned by the external query. In addition, in the associated subquery, the flow of information is bidirectional. Each row of data from the external query passes a value to the subquery, and then the subquery executes for each row of data once and returns its record. The external query then makes a decision based on the returned records.

Such as:

SELECT o1.CustomerID, o1.OrderID, o1.OrderDateFROM Orders o1WHERE o1.OrderDate = (SELECT Max (OrderDate) FROM Orders o2WHERE o2.CustomerID = o1.CustomerID)

Is an associated subquery

SELECT o1.CustomerID, o1.OrderID, o1.OrderDateFROM Orders o1WHERE o1.OrderDate IN (SELECT TOP 2 o2.OrderDateFROM Orders o2WHERE o2.CustomerID = o1.CustomerID) ORDER BY CustomerID

Is an unrelated subquery

2, prompt (HINT)

In general, whether rule-based or cost-based methods are used in optimization, the optimizer of the Oracle system determines the execution path of the statement. The path of such a choice should not be the best. Therefore, Oracle provides a method called prompting. It allows programmers to choose the execution path according to their own requirements, that is, to prompt the optimizer to follow what execution rules to execute the current statement. This is better in performance than Oracle optimization autonomic decisions.

In general, programmers can use prompts to make optimization decisions. The following can be specified by using prompts:

Optimization method of l SQL statement

For a SQL statement, optimize the goal of the program based on overhead

Access path accessed by l SQL statement

Connection order of connection statement

The join operation in the connection statement.

If you want the optimizer to follow the programmer's requirements, give a hint in the statement. The valid range of prompts is limited, that is, only the block of statements with prompts can be executed in accordance with the requirements of the prompt. The following statement specifies the prompt:

Simple SELECT, UPDATE, DELETE statements

L compound main statement or subquery statement

L forms part of the query (UNION).

The prompt is specified by adding "+" to the original comment statement. The syntax is as follows:

[SELECT | DELETE | UPDATE] / * + [hint | text] * /

Or

[SELECT | DELETE | UPDATE]-- + [hint | text]

Be careful not to add "+" directly after "/ *". Similarly, "- +" is also written in succession.

Warning: if the prompt statement is not written correctly, Oracle ignores the statement.

Common tips are:

Ordered forces joins in the order of the tables specified in the from clause

Use_NL forces the join between two tables to be a nested loop (Nested Loops)

Use_Hash forces the connection between two tables to be a hash join (Hash Join)

Use_Merge forces the join between two tables to be a merge sort join (Merge Join)

Push_Subq allows unrelated subqueries to be executed in advance

Index enforces an index

3, execute the plan

Select the SQL statement with the mouse or keyboard in the SQL WINDOWS of PL/SQL Developer, and then press F5, and the interface for performing plan parsing appears:

4. Characteristics of Update

The internal implementation of Update can be found in the enclosure: internal Analysis of update transactions. Doc

The basic point of using Update is

1) try to use the indexes on the updated table to reduce unnecessary updates

2) updating the data source takes as little time as possible. If not, insert the update content into the intermediate table, then index the intermediate table, and then update it.

3) if the primary key is updated, it is recommended to delete and insert.

5, table for example

The following exposition will focus on the following two tables:

Create table tab1 (workdate varchar2 (8), cino varchar2 (15), val1 number, val2 number)

Create table tab2 (workdate varchar2 (8), cino varchar2 (15), val1 number, val2 number)

Create table tab3 (workdate varchar2 (8), cino varchar2 (15), val1 number, val2 number)

Create table tab4 (workdate varchar2 (8), cino varchar2 (15), val1 number, val2 number)

Workdate and cino are the keywords of two tables, and there is no primary key index by default.

Second, two cases of Update

There are two ways to update a table with Update: to update the fields according to the associated subquery, and to limit the scope of the update through non-associative subqueries. If there is a third case, it is the superposition of the first two cases.

1. Update the field according to the associated subquery

Update tab1 tSet (val1, val2) = (select val1, val2from tab2where workdate = t.workdateand cino = t.cino)

Update the corresponding fields of tab1 through tab2. When the SQL statement is executed, the system reads the record one line at a time from the tab1, and then finds the corresponding field to update through the associated subquery. Whether the relevant subquery can quickly find the corresponding records through the conditions of tab1 is a necessary condition whether the optimization can be realized. Therefore, it is generally required to build a Unique or high ranking Normal index on tab2. The time taken to execute is approximately (the time it takes to query a record in tab1 + the time it takes to query a record in tab2) * the number of records in tab1.

If the subquery conditions are complex, such as the following statement:

Update tab1 tSet (val1, val2) = (select val1, val2from tab2 ttwhere exists (select 1from tab3where workdate = tt.workdateand cino = tt.cino) and workdate = t.workdateand cino = t.cino)

At this point, the time spent updating each record in the tab1 on the subquery will multiply, and if there are more records in the tab1, this update statement is almost impossible to complete.

The solution is to extract the subquery into the intermediate table, then index the intermediate table and replace the subquery with the intermediate table, so that the speed can be greatly improved:

Insert into tab4select workdate, cino, val1, val2from tab2 ttwhere exists (select 1from tab3where workdate = tt.workdateand cino = tt.cino); create index tab4_ind01 on tab4 (workdate, cino); Update tab1 tSet (val1, val2) = (select val1, val2from tab4 ttwhere workdate = t.workdateand cino = t.cino)

2. Limit the scope of updates through non-related subqueries

Update tab1 tset val1 = 1where (workdate, cino) in (select workdate, cino from tab2)

Update the val1 field of the corresponding record in the tab1 based on the data range provided by tab2.

In this case, the default execution mode of the system is to first execute select workdate, cino from Tab2 sub-query to form a system view in the system, and then select a record in tab1 to query whether there is a corresponding combination of workdate and cino in the system view. If so, update tab1, if not, select the next record. The query time in this way is roughly equal to: subquery query time + (time to select a record in tab1 + scan the whole table to find a record time in system view) * number of records in tab1. Where "scan the whole table in the system view for a record time" will vary depending on the size of the tab2. If the number of tab2 records is small, the system can directly read the table to the system area; if there are a large number of tab2 records, the system can not form a system view, then every update action will do a subquery, the speed will be very slow.

There are two kinds of optimization for this situation.

1) add an index to the workdate and cino fields on tab1, and add a prompt.

The modified SQL statement is as follows:

Update / * + ordered use_nl (sys, t) * / tab1 tset val1 = 1where (workdate, cino) in (select workdate, cino from tab2)

Where sys represents the system view. If you do not add the ordered prompt, the system will default to the tab1 table as the driver table, and then the tab1 will be scanned all the tables. After adding the prompt, use the system view, that is, select workdate, cino from tab2, as the driver table, under normal circumstances, the speed can be greatly improved.

2) add the index to the workdate and cino fields on the tab2 table, and rewrite the SQL statement:

Update tab1 tset val1 = 1where exists (select 1from tab2where workdate = t.workdateand cino = t.cino)

Third, the index problem

The use of update index is special, sometimes it seems that the full index can be used, but in fact only part of it is used, so it is recommended to write the fields of the composite index together.

For example:

Update / * + ordered use_nl (sys, t) * / tab1 tset val1 = 1where cino in (select cino from tab2) and workdate = '200506'

This SQL statement cannot be fully used for the compound index workdate + cino on tab1. All that can be used is the constraints of workdate='200506'.

If it's written like this, there's no problem:

Update / * + ordered use_nl (sys, t) * / tab1 tset val1 = 1where (workdate, cino) in (select workdate, cino from tab2) the above is how to use update subquery. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.