`
nlslzf
  • 浏览: 1046405 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

分析总结:关于两个表关联进行更新

阅读更多
http://www.oracle.com.cn/viewthread.php?tid=112596&highlight=%2Bwait4me
关于两个表关联进行更新,在论坛上经常有人问这个问题。尤其是不少刚刚从sql server转到oracle的
朋友,会把两种数据库的语法混淆。今天正好做了个测试,纪录下来
OS: RedHat Linus AS4
DB: Oracle 10gR2
案例:经典的detp/emp表,现在希望把emp.ename更新成ename+loc的形式。其中loc是通过关联emp和dept
取得的数据。

Last login: Mon Jan 22 15:40:57 2007 from 192.168.10.100
[oracle@testsrv1 ~]$ sqlplus /nolog
[uniread] Loaded history (3738 lines)

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 22 16:19:30 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

idle> conn test/test
Connected.

准备数据的sql如下
复制内容到剪贴板
代码:
test@DW> !cat /tmp/pre.sql
drop table emp;
drop table dept;
CREATE TABLE dept (
      deptno        NUMBER(4),
      dname         VARCHAR2(14),
      loc           VARCHAR2(13));

CREATE TABLE emp (
      empno        NUMBER(4) ,
      ename        VARCHAR2(100),
      job          VARCHAR2(9),
      mgr          NUMBER(4),
      sal          NUMBER(7,2),
      comm         NUMBER(7,2),
      deptno       NUMBER(2)
      );

insert into dept  select * from scott.dept;

insert into emp select empno,ename,job,mgr,sal,comm,deptno  from scott.emp;

commit;

exec dbms_stats.gather_table_stats(user,'DEPT',cascade=>true);
exec dbms_stats.gather_table_stats(user,'EMP',cascade=>true);
原始数据如下
test@DW> select * from emp;

     EMPNO ENAME      JOB              MGR        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902        800                    20
      7499 ALLEN      SALESMAN        7698       1600        300         30
      7521 WARD       SALESMAN        7698       1250        500         30
      7566 JONES      MANAGER         7839       2975                    20
      7654 MARTIN     SALESMAN        7698       1250       1400         30
      7698 BLAKE      MANAGER         7839       2850                    30
      7782 CLARK      MANAGER         7839       2450                    10
      7788 SCOTT      ANALYST         7566       3000                    20
      7839 KING       PRESIDENT                  5000                    10
      7844 TURNER     SALESMAN        7698       1500          0         30
      7876 ADAMS      CLERK           7788       1100                    20
      7900 JAMES      CLERK           7698        950                    30
      7902 FORD       ANALYST         7566       3000                    20
      7934 MILLER     CLERK           7782       1300                    10

14 rows selected.

test@DW> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

使用下面的语法(语法1)达到目的(注意,该语法有潜在的错误)
test@DW> update emp e set e.ename=e.ename ||'.'||(select d.loc from dept d where e.deptno=d.deptno);

test@DW> select * from emp;

     EMPNO ENAME                          JOB              MGR        SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ---------- ---------- ----------
      7369 SMITH.DALLAS                   CLERK           7902        800                    20
      7499 ALLEN.CHICAGO                  SALESMAN        7698       1600        300         30
      7521 WARD.CHICAGO                   SALESMAN        7698       1250        500         30
      7566 JONES.DALLAS                   MANAGER         7839       2975                    20
      7654 MARTIN.CHICAGO                 SALESMAN        7698       1250       1400         30
      7698 BLAKE.CHICAGO                  MANAGER         7839       2850                    30
      7782 CLARK.NEW YORK                 MANAGER         7839       2450                    10
      7788 SCOTT.DALLAS                   ANALYST         7566       3000                    20
      7839 KING.NEW YORK                  PRESIDENT                  5000                    10
      7844 TURNER.CHICAGO                 SALESMAN        7698       1500          0         30
      7876 ADAMS.DALLAS                   CLERK           7788       1100                    20
      7900 JAMES.CHICAGO                  CLERK           7698        950                    30
      7902 FORD.DALLAS                    ANALYST         7566       3000                    20
      7934 MILLER.NEW YORK                CLERK           7782       1300                    10

14 rows selected.

test@DW> rollback;

Rollback complete.

为什么刚才说语法1有潜在的错误?因为在上面的例子中,两个表的关联部分没有unique属性,那么就可能出现一个
emp.deptno对应多个dept.deptno的情况。这里仅仅是可能出现,因为当这个情况出现的时候,大多是因为原始数据
中存在重复的,多余的数据。

那么,为了简单的避免上面的问题,我们使用语法2,加上一个max函数达到唯一的目的
test@DW> update emp e set e.ename=e.ename ||'.'||(select max(d.loc) from dept d where e.deptno=d.dep
tno);

14 rows updated.

test@DW> rollback;
test@DW> select * from emp;

     EMPNO ENAME                          JOB              MGR        SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ---------- ---------- ----------
      7369 SMITH.DALLAS                   CLERK           7902        800                    20
      7499 ALLEN.CHICAGO                  SALESMAN        7698       1600        300         30
      7521 WARD.CHICAGO                   SALESMAN        7698       1250        500         30
      7566 JONES.DALLAS                   MANAGER         7839       2975                    20
      7654 MARTIN.CHICAGO                 SALESMAN        7698       1250       1400         30
      7698 BLAKE.CHICAGO                  MANAGER         7839       2850                    30
      7782 CLARK.NEW YORK                 MANAGER         7839       2450                    10
      7788 SCOTT.DALLAS                   ANALYST         7566       3000                    20
      7839 KING.NEW YORK                  PRESIDENT                  5000                    10
      7844 TURNER.CHICAGO                 SALESMAN        7698       1500          0         30
      7876 ADAMS.DALLAS                   CLERK           7788       1100                    20
      7900 JAMES.CHICAGO                  CLERK           7698        950                    30
      7902 FORD.DALLAS                    ANALYST         7566       3000                    20
      7934 MILLER.NEW YORK                CLERK           7782       1300                    10

14 rows selected.

test@DW> rollback;

Rollback complete.

在重复数据比较多的情况下,我们不想使用了聚合函数max,那么可以使用语法3,加入rownum=1条件
test@DW> update emp e set e.ename=e.ename ||'.'||(select d.loc from dept d where e.deptno=d.deptno a
nd rownum=1);

14 rows updated.

test@DW> select * from emp;

     EMPNO ENAME                          JOB              MGR        SAL       COMM     DEPTNO
---------- ------------------------------ --------- ---------- ---------- ---------- ----------
      7369 SMITH.DALLAS                   CLERK           7902        800                    20
      7499 ALLEN.CHICAGO                  SALESMAN        7698       1600        300         30
      7521 WARD.CHICAGO                   SALESMAN        7698       1250        500         30
      7566 JONES.DALLAS                   MANAGER         7839       2975                    20
      7654 MARTIN.CHICAGO                 SALESMAN        7698       1250       1400         30
      7698 BLAKE.CHICAGO                  MANAGER         7839       2850                    30
      7782 CLARK.NEW YORK                 MANAGER         7839       2450                    10
      7788 SCOTT.DALLAS                   ANALYST         7566       3000                    20
      7839 KING.NEW YORK                  PRESIDENT                  5000                    10
      7844 TURNER.CHICAGO                 SALESMAN        7698       1500          0         30
      7876 ADAMS.DALLAS                   CLERK           7788       1100                    20
      7900 JAMES.CHICAGO                  CLERK           7698        950                    30
      7902 FORD.DALLAS                    ANALYST         7566       3000                    20
      7934 MILLER.NEW YORK                CLERK           7782       1300                    10

14 rows selected.

语法2和语法3有什么区别?我们来看看他们的执行计划

先看语法2
test@DW> explain plan for update emp e set e.ename=e.ename ||'.'||(select max(d.loc) from dept d whe
re e.deptno=d.deptno);

Explained.

test@DW> @/Oracle/product/10.2.1/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------
Plan hash value: 1667745622

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |    14 |   126 |     3   (0)| 00:00:01 |
|   1 |  UPDATE             | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |    11 |            |          |
|*  4 |    TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("D"."DEPTNO"=:B1)

16 rows selected.

test@DW> rollback;

再来看看语法3
test@DW> explain plan for update emp e set e.ename=e.ename ||'.'||(select d.loc from dept d where e.
deptno=d.deptno and rownum=1);

Explained.

test@DW> @/Oracle/product/10.2.1/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------
Plan hash value: 764578432

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |      |    14 |   126 |     3   (0)| 00:00:01 |
|   1 |  UPDATE             | EMP  |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   126 |     3   (0)| 00:00:01 |
|*  3 |   COUNT STOPKEY     |      |       |       |            |          |
|*  4 |    TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM=1)
   4 - filter("D"."DEPTNO"=:B1)

17 rows selected.

仔细对比2和3的执行计划,他们唯一的区别在于:2使用了sort aggregate,而3使用了count stopkey

如果我们熟悉oracle分页算法,就知道count stopkey虽然名义上是全表扫描,但是在重复数据多的时候,会
根据指定的rownum=n在得到第n条纪录后停止扫描。而sort aggregate取得max,一定是执行全表扫描的。

通过这个细微的差别,我们可以判断出:当重复数据相对多的时候,使用rownum的语法3比语法2效率高(具体
高多少,这个需要实际测试)


===============================================================================
上面的讨论都是针对普通的表进行的,既没有任何约束条件的表关联。在实际操作中,我们更多情况面对的是
有主外键关系的表。那么,对我们的需求,又有了不同的解决方案

我们先给dept加上一个主键(实际上仅仅用唯一性约束就可以了)
test@DW> alter table dept add constraint pk_dept primary key (deptno);

或者直接使用唯一索引
test@DW> create unique index idx_dept on dept(deptno);

Table altered.

在现在的条件下,我们可以对视图(online view)直接进行更新操作。我们叫他语法4
test@DW> update (select ename,loc from emp,dept where emp.deptno=dept.deptno) v set v.ename=v.ename||'.'||v.loc;

14 rows updated.

在语法4中,我们先建立了一个online view,然后对她进行更新。一个包含多表的视图,是否可以更新,哪些字段
可以更新,涉及到一个叫 kep-preserved table的概念。简单的说,如果一个表的所有key同时也是视图的key,那么
这个表就是kep-preserved table。当视图中仅有一个kpt的时候,可以对kpt的数据进行更新操作。关于key-preseved
table和upatable columns的具体信息,请参考手册。

在我们的例子中,emp是key-preserved table,所以我们可以更新它的字段。
因为唯一索引,所以语法4的执行计划选择了使用索引,避免全表扫描
Execution Plan
----------------------------------------------------------
Plan hash value: 1686319074

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |         |    14 |   280 |     4   (0)| 00:00:01 |
|   1 |  UPDATE                       | EMP     |       |       |            |          |
|   2 |   NESTED LOOPS                |         |    14 |   280 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


总结:根据上面的分析,我个人给出的建议是:
        1。如果满足updatable view的条件,优先使用语法4,更新onlien view
        2。如果没有必要的约束条件,优先使用rownum


如果出现null之类的问题,请确保两张表之间的数据不存在a中有而b没有的
分享到:
评论

相关推荐

    SQL实现两张无关联表的数据列合并在一张结果集中

    - **使用JOIN代替子查询**:如果两个表中的数据量都不大,可以尝试使用INNER JOIN等连接方式来代替子查询,这样可以显著提升查询效率。 - **索引优化**:对于涉及的表和字段,合理建立索引可以极大提高查询速度。 - ...

    数据开发基础知识点-2(多表关联).docx

    通过对比单表设计和多表关联设计的方式,分析这两种设计模式在实际应用中的特点,并详细讲解了在多表关联场景下如何处理数据更新、删除等问题。 #### 单表设计 **定义:** 单表设计是指在一个表中存储所有相关的...

    灰色关联度等_关联度python_python关联分析_灰色关联度等_

    灰色关联度分析主要用于评估两个或多组序列之间的相似程度,即使这些序列可能存在显著的差异或者噪声。在实际应用中,这种方法常用于预测、决策支持和模式识别等领域。 描述中的"软件学习 编程 灰色关联度分析 ...

    ACCESS数据库多表关联查询例程

    3. 多对多关联:两个表的记录都可以与其他表的多个记录相关联。例如,员工表与项目表,一个员工可以参与多个项目,一个项目也可以有多个员工参与。 三、关联查询操作 在Access中,我们可以通过以下几种查询类型...

    运用R做关联度分析

    例如,我们可以找出那些具有高置信度和高提升度的规则,这些规则表明了两个商品之间的强相关性。商家可以据此调整货架布局或推出捆绑销售策略,以提高销售额。 #### 规则评估指标 - **支持度**(Support):表示...

    关联度分析

    在实际应用中,灰关联分析被广泛应用于经济、环境科学、社会科学等多个领域。 #### 知识点二:灰关联分析的基本步骤 灰关联分析的基本步骤主要包括以下几个方面: 1. **确定参考序列和比较序列**:选择一个参考...

    工业大数据分析综述:模型与算法.docx

    模型和算法是大数据分析理论和技术中的两个核心问题。本文将对工业大数据分析模型和算法的研究现状进行综述,总结了大数据分析模型在工业场景中的应用,并对工业大数据分析模型的常用求解算法进行概述和评价。 ...

    三表关联查询

    在SQL语言中,三表关联查询是数据查询和分析的核心技能之一,特别是在处理复杂的数据关系时。这种查询方法允许我们从三个或更多表格中提取相关信息,通过连接(JOIN)操作来整合这些表格的数据,以便获得更全面的...

    lua程序实现对两个文件的表的比较

    在进行两个文件的表比较时,我们通常关心的是表的键(key)和对应的值(value)是否相同。 假设我们有两个文件,分别命名为`file1.lua`和`file2.lua`,这两个文件都包含一个或多个表。我们首先需要读取这两个文件,...

    算法源码-相关性分析:灰色关联分析(matlab).zip

    灰色关联度是通过计算两个序列在某一区间内的相对变化率来衡量它们之间的关联程度。其核心思想是寻找一个参考序列,然后比较其他序列与参考序列之间的相似程度。 2. 步骤: - (1) 数据预处理:将所有数据序列进行...

    ACCESS数据库多表关联查询例程.rar

    在Access中,可以通过以下步骤建立表关联: 1. 打开数据库,选择“关系”视图。 2. 在空白区域中,点击“新建”按钮,创建一个新的关系。 3. 选择要关联的两个表,并设置关联字段。确保选择的字段在两个表中具有...

    灰色关联分析软件

    6. **灰色关联度计算公式**:通常采用的是灰色关联度生成函数,如ρ(x,y) = (1 - max|Δ(x,y)| / min|Δ(x,y)|)^γ,其中Δ(x,y)为两个序列的差序列,γ是调整参数,用于控制关联度的敏感性。 在这款名为“灰色关联...

    灰色关联度分析MATLAB代码

    灰色关联度分析是基于灰色系统理论的一种分析方法,它主要用于评估两个或多个数据序列之间的相似性。在实际应用中,我们通常会有一个参考序列(基准序列)和其他一个或多个比较序列。通过计算这些序列间的灰色关联度...

    ArcGIS表关联方式介绍

    **使用场景:** 关系类是ArcGIS中一种更高级别的表关联方式,用于定义两个或多于两个要素类之间的复杂关系。 **优点:** - 支持多对多的关系类型。 - 可以灵活地定义和管理复杂的业务规则。 **缺点:** - 设置过程...

    SPSS数据分析的统计方法选择.pdf

    * 2 × C表或R × 2表资料的统计分析:用于分析两个分类变量之间的关联性。 * R × C表资料的统计分析:用于分析两个分类变量之间的关联性。 * 配对分类资料的统计分析:用于分析两个分类变量之间的关联性。 选择...

    学生超市的关联性分析

    总结来说,通过关联分析,学生超市可以发现商品之间的购买关联,优化商品布局,提升购物体验,并可能增加销售。例如,将常一起购买的商品放在一起,可以方便学生快速找到所需商品,同时也可能触发冲动购买,提高销售...

    数据挖掘之关联分析-数据集

    关联规则的强度由两个主要指标衡量:支持度和支持频率。支持度是指A和B同时出现的概率,而频率则是A和B在所有可能项集中出现的比例。另一个关键指标是置信度,它表示在知道A发生的条件下,B发生的概率。 关联分析中...

    L5关联性研究的设计与数据分析.pdf

    "L5关联性研究的设计与数据分析" ...L5关联性研究的设计与数据分析是指分析两个随机变量之间的关系,包括两个连续型随机变量的线性相关分析、两个分类变量的关联分析和两个连续型随机变量的线性回归分析。

    人工智能-机器学习-关联规则分析-Apriori算法实例-挖掘电影导演的关联规则

    在这个关于电影导演的关联规则分析实例中,我们可能会有以下步骤: 1. 数据预处理:收集电影数据,包括导演、电影类型、票房、评分等,将这些信息转化为适合Apriori算法的事务数据库格式。 2. 应用Apriori算法:...

Global site tag (gtag.js) - Google Analytics