recently researched SQL optimization, the following articles are transferred from the Internet:1.Grammar single table:UPDATETable nameSETColumn Name = New valueWHEREColumn Name = A value such as: Update t_join_situation set join_state= ' 1 ' whereyear= ' 2011 ' Update Year for "2011" of the Data join_state field is "1". If the updated field is indexed, the update rebuilds the index and the update is inefficient. Multiple table associations, and update the field values of one table to the fields in another table: Update table A set a. Field 1 = (select B. field 1 from table B where a. field 2=b. Field 2) where exists (select 1 from table B WH Ere a. field 2=b. Field 2) Oracle's UPDATE statement does not pass MSSQL so simple and easy to write, even if written out, but the implementation may be reported that this is due toSetWhere the subquery has detected multiple rows of data values,OracleSpecifies a one-to-one update, so an error occurs. To resolve this it must be ensured that the value one by one corresponds to the check out.2.PrincipleUpdateThe principle of the statement is to firstwhereAfter the condition has been traced to the data, ifSet, the subquery executes the query to assign the value to the updated field and perform the update. For example: Update table A set a. Field 1 = (select B. field 1 from table B where a. field 2=b. Field 2) where exists (select 1 from table B where a. field 2=b. Field 2). Look up all the data in table A, loop through each piece of data, verify that the data conforms to exists (select 1 from table B where a. field 2=b. Field 2) condition, if yes then execute (select B. field 1 from table B where a. field 2=b. Field 2) query, Find the corresponding value in update a. Field 1. You must have exists (select 1 from table B where a. field 2=b. Field 2) To update the associated table, otherwise update the field 1 of the other data for table A to a null value.Second,ImproveOracleA variety of solutions to update efficiency1.StandardUpdatesyntax when you need to update the table is a single or updated field does not need to be associated with other bands come over, then the final selection of the standardUpdateStatement, the fastest, the best stability, and returns the number of affected bars. IfwhereIf the fields in the criteria are indexed, the update is more efficient. But when you need to update the fields for the associated table,UpdateThe efficiency is very poor.2.Inline ViewUpdate methodInline ViewThe Update method is to update a temporary established view. such as: Update (select A.join_state asjoin_state_a,b.join_state as Join_state_bfrom t_join_situation A, T_people_info b where a . People_number=b.people_numberand a.year= ' and a.city_number= ' M00000 ' and a.town_number= ' M51000 ') setjoin_state _a=join_state_b in parentheses creates a view from the associated two tables, setting the updated field in set. This workaround is more intuitive and executes faster than the wording. But the primary key of table B must be in the where condition, and is associated with "=" to be updated table, otherwise report the error: 3.merge Update method Merge is a statement that is unique to Oracle, with the following syntax: Merge into table_name ALIAS1
USING (table|view|sub_query) alias2
On (Join condition)
When matched then
UPDATE table_name
SET col1 = Col_val1,
col2 = Col2_val
When isn't matched then
INSERT (column_list) VALUES (column_values); Its principle is to select the data in the ALIAS2, each with the ALIAS1 on (join condition) comparison, if the match, the Update operation (update), if not match, insert operation (insert). The execution of the merge does not return the number of rows affected. The merge statement is cumbersome and can only be associated with a maximum of two tables, and complex statements with the merge Update method will be ineffective and inefficient. 4. Fast cursor update syntax such as: beginfor CR in (query statement) loop–-Loop--UPDATE statement (based on query results set) Endloop; --End Loop end;oracle supports fast cursors and does not need to be defined to write cursors directly to the for loop, which makes it easier for us to update the data in batches. In addition to Oracle's ROWID physical field (Oracle defaults to each table with the rowID field and is a unique index), you can quickly navigate to the record you want to update. Examples are as follows: Beginfor CR in (select A.rowid,b.join_state from t_join_situation a,t_people_info bwhere A.people_number=b.people_ Numberand a.year= ' and a.city_number= ' M00000 ' and a.town_number= ' M51000 ') loopupdate t_join_situation set Join_ State=cr.join_state Whererowid = cr.rowid;endloop;end; The advantages of using fast cursors are many, can support complex query statements, update accurately, no matter how large the data update efficiency is still high, but do not return the impact of the number of rows.Third,Conclusion
| Scheme |
Suggestions |
| Standard update syntax |
A single-table update or a simpler statement takes advantage of this scenario. |
| Inline View Update method |
It is better to use this scheme when two tables are associated and the table being updated is associated with the associated table primary key. |
| Merge Update method |
It is better to use this scheme when two tables are associated and the table being updated is not associated with the associated table primary key. |
| Fast Cursor Update method |
Multi-table association and complex Logic, the adoption of this scheme is more excellent. |
Speed of real-time testing:
--48466
Bar Data
--1.297Update (select A.join_state as join_state_a,b.join_state as Join_state_bfrom t_join_situation A, t_people_info b where A.P Eople_number=b.people_numberand a.year= ' and a.city_number= ' M00000 ' and a.town_number= ' M51000 ') set join_state_a =join_state_b
--7.156Update T_join_situation a set a.join_state= (select B.join_state from T_people_info bwhere A.people_number=b.people_ Numberand a.year= ' and a.city_number= ' M00000 ' and a.town_number= ' M51000 ') whereexists (Select1from t_people_info Bwhere A.people_number=b.people_numberand a.year= ' and a.city_number= ' M00000 ' and a.town_number= ' M51000 ')
--3.281Beginfor cr in (select A.rowid,b.join_state from t_join_situation a,t_people_info bwhere A.people_number=b.people_ Numberand a.year= ' and a.city_number= ' M00000 ' and a.town_number= ' M51000 ') loopupdate t_join_situation set Join_ State=cr.join_state Whererowid = cr.rowid;endloop;end;
--1.641Mergeinto t_join_situation ausing t_people_info bon (A.people_number=b.people_numberand a.year= ') and A.city_ Number= ' M00000 ' and a.town_number= ' M51000 ') Whenmatchedthenupdateset a.join_state=b.join_state
Oracle's UPDATE statement optimization study
