欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

使用子查询更新列值的UPDATE语句的一种优化方法

程序员文章站 2022-03-03 19:51:19
...

今天复习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。

使用子查询更新列值的UPDATE语句的一种优化方法

使用子查询更新列值的UPDATE语句的一种优化方法

使用子查询更新列值的UPDATE语句的一种优化方法

 

这也很好解释,我用的SQL*Plus是虚拟机RAC环境里的,而PL/SQL Developer是我本机的,自然在网络、等待时间等方面不同,产生的SQL_ID自然不同。但同样都是调用Oracle的OCI接口,在库高速缓存里的执行计划是相同的,只有一个。

相关标签: 数据库 Oracle