Introduction to update subqueries and update queries

Introduction to update subqueries and update queries

Basic knowledge
1. Associated subqueries and non-associated subqueries

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

For example:

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 a non-associated subquery

2. HINT)

Generally, the Oracle System optimizer determines the statement execution path regardless of the rule-based or cost-based method. Such a path should not be the best. Therefore, Oracle provides a method called prompt. It allows programmers to select the execution path according to their own requirements, prompting the optimizer to execute the current statement according to the execution rules. In this way, performance is better than Oracle optimization.

In general, programmers can use prompts to make optimization decisions. You can use the tips to specify the following content:

L SQL statement optimization method;

L an SQL statement is based on the overhead optimization program;

L access path for SQL statements;
L connection order of connection statements;
L connection operations in the connection statement.

If you want the optimizer to be executed according to the requirements of the programmer, a prompt should be given in the statement. The valid range of the prompt is limited. Only the prompt block can be executed as prompted. The following statement specifies the prompt:

L simple SELECT, UPDATE, and DELETE statements;
L compound subject sentence or subquery statement;
L is part of the query (UNION.

Note that the original comment statement is added with "+. Syntax:

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

Or

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

Note that after "/*", add "+" directly without being empty. Similarly, "-- +" is also a join write.

Warning If the prompt statement is incorrectly written, Oracle ignores the statement.

Common tips:

Ordered forcibly joins the table specified in the from clause.
Use_NL forcibly specifies the connection mode between two tables as Nested loop (Nested Loops)
Use_Hash forcibly specifies the connection mode between two tables as Hash Join)
Use_Merge forcibly specifies the Join mode between two tables as Merge sort Join)
Push_Subq allows non-associated subqueries to be executed in advance
Index forces the use of an Index

3. Execution Plan

In PL/SQL Developer's SQL windows, select the SQL statement with the mouse or keyboard, and press F5 to display the execution plan parsing interface:

4. Update features

For information on the internal execution of the Update system, see the appendix: internal analysis of updatetransaction. Doc

The basic point of using Update is,

1) try to use the index of the updated table to reduce unnecessary updates.
2) It takes as short as possible to update the data source. If not, insert the update content to the intermediate table, create an index for the intermediate table, and then update the data source.
3) if the primary key is updated, we recommend that you delete it and insert it again.
5. Example Table

The following two tables are described:

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 the two tables. By default, no primary key index is created.

Ii. Update

Updating a table with Update is similar to two situations: updating fields based on correlated subqueries and limiting the Update range through non-correlated subqueries. The third case is the superposition of the first two cases.

1. Update fields based on associated subqueries

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

Use tab2 to update the corresponding fields of tab1. When executing an SQL statement, the system reads a record from a row in tab1, and then finds the corresponding field to update through the Associated subquery. Whether the associated subquery can quickly find the corresponding records through the tab1 condition is a necessary condition for optimization. Therefore, it is generally required to create a Unique or a Normal index with high weighting on tab2. The execution time is roughly (the time used to query a record in tab1 + the time used to query a record in tab2) * The number of records in tab1.

If the subquery conditions are complex, see 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);

In this case, the time spent on subqueries for Updating each record in tab1 will multiply. If the number of records in tab1 is large, this update statement is almost impossible.

The solution is to extract the subquery, perform the intermediate table, create an index for the intermediate table, and replace the subquery with the intermediate table. This greatly improves the speed:

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. Use non-associated subqueries to limit the update range

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

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

In this case, the default execution mode is to first execute select workdate, cino from tab2 subquery, form a system view in the system, and then select a record in tab1, check whether a combination of workdate and cino exists in the query system view. If so, update tab1. If not, select the next record. The query time in this method is roughly equal to the number of records in tab1. The query time is equal to the subquery time + (select a record time in tab1 + search for a record time in full table scan in the System View. In the System View, the time for a full table scan to find a record varies depending on the size of tab2. If the number of tab2 records is small, the system can directly read the table to the system zone. If the number of tab2 records is large, the system view cannot be formed. In this case, every update operation is performed, the subquery will be executed once, and the speed will be very slow.

There are two optimizations 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)

Sys indicates the System View. If you do not add ordered, the system uses tab1 as the driving table by default. In this case, you need to perform a full table scan on tab1. After the prompt is added, use the System View, that is, select workdate, cino from tab2, as the driving table. Under normal circumstances, the speed can be improved much.

2) Add the cino field to the workdate on the tab2 table and rewrite the SQL statement:

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

Iii. index problems

The use of update indexes is special. Sometimes it seems that full indexes can be used, but in fact only a part is used. Therefore, we recommend that you 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 does not fully use the composite index workdate + cino on tab1. Only the workdate = '20160301' constraint can be used.

If it is written like this, there is no problem:

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

Can the update statement in oracle be queried using related subqueries?

Update table1 a set a. name = (select B. name from table2 where a. id = B. id and rownum = 1 );

In fact, it depends on your specific needs.

There are many steps, but the efficiency is relatively high:
1. create table temporary table value (select a. id, a. name, B. name,... from table1 a, table2 B where a. id = B. id)
2. delete records in Table 1. Do not drop
3. insert into table1 select the field you need from the temporary table.
 
Does mysql support update subqueries?

Generally, you cannot change the table and select from the same table in the subquery.
For example, this restriction applies to statements with the following forms:

Delete from t WHERE... (SELECT... FROM t ...);
UPDATE t... WHERE col = (SELECT... FROM t ...);
{INSERT | REPLACE} INTO t (SELECT... FROM t ...);

Exception: If the sub-query is used for the table changed in the FROM clause, the preceding ban will no longer apply.
For example:

UPDATE t... WHERE col = (SELECT... FROM t ...)
AS _ t ...);