|
The Oracle update statement syntax and performance analysis are based on the aceplus blog.
Views on syntax and Performance Analysis of keyword Oracle update statements
Source Oracle update statement syntax and Performance Analysis For convenience, the following simple model is created and some test data is constructed:
In BSS,
-- Customer data table
Create Table MERs
(
Customer_id number (8) Not null, -- customer ID
City_name varchar2 (10) Not null, -- City
Customer_type char (2) not null, -- customer type
...
)
Create unique index pk_customers on MERs (customer_id)
For some reason, the customer's city information is not accurate,
In the CRM subsystem of the customer service department, 20% of the customers are obtained through the active service
City and other accurate information, so you extract this part of information to a temporary table:
Create Table tmp_cust_city
(
Customer_id number (8) Not null,
Citye_name varchar2 (10) Not null,
Customer_type char (2) not null
)
1) The simplest form
-- It is confirmed that all the mermer_id values in the MERs table are 'beijing' if they are less than 1000'
-- Less than 1000 of the customers are old customers in the city before the company moved to the whole country :)
Update MERs
Set city_name = 'beijing'
Where customer_id & lt; 1000 2) two tables (multiple tables) join the update -- only join in the WHERE clause
-- The extracted data is VIP and new data is included. Therefore, the customer category is updated by the way.
Update MERs A -- use an alias
Set customer_type = '01' -- 01 is VIP, 00 is normal
Where exists (select 1
From tmp_cust_city B
Where B. customer_id = A. customer_id
) 3) two tables (multiple tables) are associated with update. The modified value is calculated from the other table.
Update MERs A -- use an alias
Set city_name = (select B. city_name from tmp_cust_city B where B. customer_id = A. customer_id)
Where exists (select 1
From tmp_cust_city B
Where B. customer_id = A. customer_id
)
-- Update has more than 2 values
Update MERs A -- use an alias
Set (city_name, customer_type) = (select B. city_name, B. customer_type
From tmp_cust_city B
Where B. customer_id = A. customer_id)
Where exists (select 1
From tmp_cust_city B
Where B. customer_id = A. customer_id
)
Note that in this statement,
= (Select B. city_name, B. customer_type
From tmp_cust_city B
Where B. customer_id = A. customer_id
)
And
(Select 1
From tmp_cust_city B
Where B. customer_id = A. customer_id
)
It is two independent subqueries. According to the execution plan, two articles are scanned for table B/index;
If the where condition is discarded, the entire table is created for table A by default.
Update, but because (select B. city_name from tmp_cust_city B where
Where B. customer_id = A. customer_id)
It may not provide "enough" value, because tmp_cust_city is only part of the customer's information,
Therefore, an error is returned (if the specified column -- city_name can be null, it is another question ):
01407,000 00, "Cannot update (% s) to null"
// * Cause:
// * Action: An alternative method can be:
Update MERs A -- use an alias
Set city_name = nvl (select B. city_name from tmp_cust_city B
Where B. customer_id = A. customer_id), A. city_name)
Or
Set city_name = nvl (select B. city_name from tmp_cust_city B
Where B. customer_id = A. customer_id), 'unknown ')
-- Of course this is not in line with the business logic 4) 3) In some cases, because table B records only 20-30% of Table A records,
Considering the use of index in Table A, Using cursor may bring better performance than associating update:
Set serveroutput on Declare
Cursor city_cur is
Select customer_id, city_name
From tmp_cust_city
Order by customer_id;
Begin
For my_cur in city_cur Loop Update MERs
Set city_name = my_cur.city_name
Where customer_id = my_cur.customer_id;
/** You can submit one or multiple rows to avoid table lock **/
-- If Mod (city_cur % rowcount, 10000) = 0 then
-- Dbms_output.put_line ('----');
-- Commit;
-- End if;
End loop;
End; 5) a special case of associated update and further exploration of performance
In Oracle's update statement syntax, in addition to the update table, it can also be a view, so there is one special case:
Update (select a. city_name, B. city_name as new_name
From MERs,
Tmp_cust_city B
Where B. customer_id = A. customer_id
)
Set city_name = new_name
This avoids two scans on table B or its indexes, provided that a (customer_id) B (customer_id) must be a unique index
Or primary key. Otherwise, an error is returned:
01779,000 00, "cannot modify a column which maps to a non key-preserved table"
// * Cause: an attempt was made to insert or update columns of a join view which
// Map to a non-key-preserved table.
// * Action: Modify the underlying base tables directly. 6) Another common Oracle Error
3) For some reason, tmp_cust_city mermer_id is not the unique index/primary key.
Update MERs A -- use an alias
Set city_name = (select B. city_name from tmp_cust_city B where B. customer_id = A. customer_id)
Where exists (select 1
From tmp_cust_city B
Where B. customer_id = A. customer_id
)
When a given a. mermer_id
(Select B. city_name from tmp_cust_city B where B. customer_id = A. customer_id)
If one additional entry is returned, the following error is returned:
01427,000 00, "single-row subquery returns more than one row"
// * Cause:
// * Action: A relatively simple approach that is similar to irresponsible is
Update MERs A -- use an alias
Set city_name = (select B. city_name from tmp_cust_city B where B. customer_id = A. customer_id) How to Understand the 01427 error. In a complicated multi-table join update statement, this error is often caused by weeks of consideration,
The preceding example is still described. A simple method is to import Table A into the value expression using group by and
Having statement to view Repeated Records
(Select B. customer_id, B. city_name, count (*)
From tmp_cust_city B, MERS MERs
Where B. customer_id = A. customer_id
Group by B. customer_id, B. city_name
Having count (*)> = 2
)
Author blog: http://blog.csdn.net/aceplus/
Related Articles
Oracle update statement syntax and Performance Analysis
Microsoft mobile windows
[Add to favorites] discussion on Anti-standard technology in Database Design
Oracle9i and Sybase ase12.5
How to install Windows 2000 on Windows XP
Comment on this article
Aceplus (2005-07-04)
/***** Correct 1 error ****/ A relatively simple approach that is similar to irresponsible is
Update MERs A -- use an alias
Set city_name = (select B. city_name from tmp_cust_city B
Where B. customer_id =
A. customer_id and rownum = 1) How to Understand the 01427 error. In a complicated multi-table join update statement, this error is often caused by weeks of consideration,
The preceding example is still described. A simple method is to import Table A into the value expression using group by and
Having statement to view Repeated Records
(Select B. customer_id, B. city_name, count (*)
From tmp_cust_city B, MERS MERs
Where B. customer_id = A. customer_id
Group by B. customer_id, B. city_name
Having count (*)> = 2
)
Aceplus (2005-07-04)
/***** Correct 1 error ****/ A relatively simple approach that is similar to irresponsible is
Update MERs A -- use an alias
Set city_name = (select B. city_name from tmp_cust_city B
Where B. customer_id =
A. customer_id rownum = 1) How to Understand the 01427 error. In a complicated multi-table join update statement, this error is often caused by weeks of consideration,
The preceding example is still described. A simple method is to import Table A into the value expression using group by and
Having statement to view Repeated Records
(Select B. customer_id, B. city_name, count (*)
From tmp_cust_city B, MERS MERs
Where B. customer_id = A. customer_id
Group by B. customer_id, B. city_name
Having count (*)> = 2
) |
