使用子查询更新列值的UPDATE语句的一种优化方法
今天复习Oracle知识,执行书中的例子,无意间发现一个关于UPDATE优化的方法,具体如下:
1、基于hr用户为scott用户创建employees表,为其创建主键索引,并为department_id创建索引EMP_DEPARTMENT_IX。
2、创建一个employees的副本employees2,并为其创建主键索引EMP_EMP_ID_PK2。
3、更新employees2表department_id=90的员工salary列为原来的1.1倍。
CREATE TABLE employees AS SELECT * FROM hr.employees;
CREATE TABLE employees2 AS SELECT * FROM hr.employees;
alter table EMPLOYEES
add constraint EMP_EMP_ID_PK primary key (EMPLOYEE_ID);
alter table EMPLOYEES2
add constraint EMP_EMP_ID_PK2 primary key (EMPLOYEE_ID);
create index EMP_DEPARTMENT_IX on EMPLOYEES (DEPARTMENT_ID);
UPDATE employees2 SET salary=salary*1.1 WHERE department_id=90;
COMMIT;
SELECT last_name,salary from employees WHERE department_id=90;
SELECT last_name,salary from employees2 WHERE department_id=90;
4、然后用department_id=90的员工在employees2的salary去更新相应的在employees的salary,用两种语句实现:
语句A:
UPDATE (SELECT e1.salary,e2.salary new_sal
FROM employees e1,employees2 e2
WHERE e1.employee_id = e2.employee_id
AND e1.department_id=90)
SET salary = new_sal;
语句B:
UPDATE employees e1
SET salary = (SELECT e2.salary
FROM employees2 e2
WHERE e2.employee_id = e1.employee_id
)
WHERE e1.department_id=90;
执行过程:
SQL> set autotrace on;
SQL> UPDATE (SELECT e1.salary,e2.salary new_sal
2 FROM employees e1,employees2 e2
3 WHERE e1.employee_id = e2.employee_id
4 AND e1.department_id=90)
5 SET salary = new_sal;
3 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 3977979856
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | UPDATE STATEMENT | | 3 | 57 |
5 (0)| 00:00:01 |
| 1 | UPDATE | EMPLOYEES | | |
| |
| 2 | NESTED LOOPS | | | |
| |
| 3 | NESTED LOOPS | | 3 | 57 |
5 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 33 |
2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 3 | |
1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK2 | 1 | |
0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES2 | 1 | 8 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E1"."DEPARTMENT_ID"=90)
6 - access("E1"."EMPLOYEE_ID"="E2"."EMPLOYEE_ID")
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
7 consistent gets
0 physical reads
676 redo size
830 bytes sent via SQL*Net to client
951 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> select employee_id,salary from employees where department_id = 90;
EMPLOYEE_ID SALARY
----------- ----------
100 26400
101 18700
102 18700
SQL> rollback;
Rollback complete.
SQL> UPDATE employees e1
2 SET salary = (SELECT e2.salary
3 FROM employees2 e2
4 WHERE e2.employee_id = e1.employee_id
5 )
6 WHERE e1.department_id=90;
3 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 2110958032
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
------------------
| 0 | UPDATE STATEMENT | | 3 | 33 | 8
(38)| 00:00:01 |
| 1 | UPDATE | EMPLOYEES | | |
| |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 33 | 2
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 3 | | 1
(0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES2 | 1 | 8 | 1
(0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK2 | 1 | | 0
(0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E1"."DEPARTMENT_ID"=90)
5 - access("E2"."EMPLOYEE_ID"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
8 consistent gets
0 physical reads
636 redo size
832 bytes sent via SQL*Net to client
929 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> set autotrace off;
SQL> select employee_id,salary from employees where department_id = 90;
EMPLOYEE_ID SALARY
----------- ----------
100 26400
101 18700
102 18700
SQL> rollback;
Rollback complete.
由上可知,语句A的cost=5,语句B的cost=8,二者都走了该走的索引,除了嵌套循环那块和最终结果不同外没有任何其他差异。有些杠精可能会说这不是真正的执行计划,我们来用dbms_xplan.display_cursor查看执行计划:
#查看语句A的执行计划
SELECT p.* FROM v$sql t,TABLE(dbms_xplan.display_cursor(t.sql_id,t.CHILD_NUMBER)) p
WHERE t.SQL_TEXT LIKE '%UPDATE (%';
#查看语句B的执行计划
SELECT p.* FROM v$sql t,TABLE(dbms_xplan.display_cursor(t.sql_id,t.CHILD_NUMBER)) p
WHERE t.SQL_TEXT LIKE '%UPDATE employees e1%';
结果和上面的并无差异!
这说明了什么?
说明了在执行UPDATE tab_a set col_1 = (select col_1 from tab_b) 时我们可以进行优化,使用UPDATE 视图 set col_1=new_col_1(tab_b的col_1的别名)。
PS:我还发现在Server端的SQL*Plus里执行和PL/SQL Developer等第三方客户端工具相同SQL时,会产生两个SQL_ID,在V$SQL里存储两条不同的记录,二者的SQL_ID、Buffer_Gets、XXX_Wait_Time、Module等不同,但PLAN_HASH_VALUE相同,都为3977979856。
这也很好解释,我用的SQL*Plus是虚拟机RAC环境里的,而PL/SQL Developer是我本机的,自然在网络、等待时间等方面不同,产生的SQL_ID自然不同。但同样都是调用Oracle的OCI接口,在库高速缓存里的执行计划是相同的,只有一个。
上一篇: 输入框自动补全