如何删除数据库中的冗余数据(2)

它将冗余数据选择到一个游标中 并根据（LastName FirstName）来分组（在我们这个方案中） 然后打开游标然后循环地取出每一行 然后用与先前的取出的键值进行比较 如果这是第一次取出这个值 或者这个值不是冗余键 那么跳过这个记录然后取下一个 不然的话 这就是这个组中的冗余记录 所以删掉它．

让我们运行一下这个存储过程

BEGIN

DeleteDuplicates

END

/

SELECT LastName FirstName COUNT(*)

FROM Customers

GROUP BY LastName FirstName

HAVING COUNT(*) >

最后一个查询语句没有返回值 所以冗余数据没有了从表中取冗余数据的过程完全是由定义在csr_Duplicates 这个游标中的SQL语句来实现的 PL／SQl只是用来实现删除冗余数 那么能不能完全用SQL语句来实现呢？

二．SQL解决方案 使用RANK（）删除冗余数据Oracle i分析函数RANK（）来枚举每一个组中的元素 在我们的方案中 　我们应用这个方案 我们使用这个函数动态的把冗余数据连续的排列起来加上编号 组由Partintion　by　这个语句来分开 然后用Order　by　进行分组SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName

FirstName ORDER BY ID) SeqNumber

FROM Customers

ORDER BY LastName FirstName

SQL

Listing Output of single SQL statement that uses RANK()

显示的是根据记录的条数的个数来显示尤其对于冗余数据

ID LASTNAME FIRSTNAME SEQNUMBER

Blake Becky

Blue Don

Bradley Tom

Chang Jim

Griffith David

Hill Larry

King Chuck

Krieger Jeff

Krieger Jeff

Krieger Jeff

Loney Julie

Lord Don

Mason Paul

Monroe John

Simon Michael

Simon Michael

Stone Tony

Stone Tony

Stone Tony

Stone Tony

Stone Tony

我们可以看一到 SeqNumber这一列中的数值 冗余数据是根据ID号由小到大进行的排序 所有的冗余数据的SqlNumber都大于一 所有的非冗余数据都等于一 所以我们取自己所需 删除那么没用的SELECT ID LastName FirstName

FROM

(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName

FirstName ORDER BY ID) AS SeqNumber

FROM Customers)

WHERE SeqNumber >

SQL

Listing 冗余键的键值

有七行必须被删除

ID LASTNAME FIRSTNAME

Krieger Jeff

Krieger Jeff

Simon Michael

Stone Tony

Stone Tony

Stone Tony

Stone Tony

rows selected 这显示有七行需要删除 还是用上一个表我测试了一下这个代码 它用了77秒种就删除了所有的数据准备好了用Sql语句来删除冗余数据 版本一它执行了 秒

DELETE

FROM CUSTOMERS

WHERE ID IN

(SELECT ID

FROM

(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName

FirstName ORDER BY ID) AS SeqNumber

FROM Customers)

WHERE SeqNumber >)

我们可以看到最后的两行语句对表中的数据进行了排序 这不是有效的 所以我们来优化一下最后一个查询语句 把Rank（）函数应用到只含有冗余数据的组 而不是所有的列下面这个语句是比较有效率的 虽然它不像上一个查询那样精简SELECT ID LastName FirstName

FROM

(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName

FirstName ORDER BY ID) AS SeqNumber

FROM

(SELECT ID LastName FirstName

FROM Customers

WHERE (LastName FirstName) IN (SELECT LastName FirstName

FROM Customers

GROUP BY LastName FirstName

HAVING COUNT(*) >)))

WHERE SeqNumber >

选择冗余数据只用了26秒钟 这样就提高了 ％的性能 这样就提高了将这个作为子查询的删除查询的效率

DELETE

FROM Customers

WHERE ID IN

(SELECT ID

FROM

(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName

FirstName ORDER BY ID) AS SeqNumber

FROM

(SELECT ID LastName FirstName

FROM Customers

WHERE (LastName FirstName) IN (SELECT LastName FirstName

FROM Customers

GROUP BY LastName FirstName

HAVING COUNT(*) >)))

WHERE SeqNumber >)

现在只用了 秒钟的就完成的上面的任务 比起上一个 秒 这是一个很大的进步 相比之下 存储过程用了 秒 这样存储过程有些慢了使用PL／SQL语句我们和我们以上的代码 会得到更好的更精确的代码 和提高你代码的执行效率 虽然对于从数据库中枚举数据PL／SQL对于Sql两者没有什么差别 但是对于数据的比较上 PL／SQL就比SQL要快很多 但是如果冗余数据量比较小的话 我们尽量使用SQL而不使用PL／SQL如果你的数据表没有主键的话 那么你可以参考其它技术

Rank（）其它的方法

使用Rank（）函数你可以对选择你所保留的数据 （或者是小ID的或者是大ID　的 就由RECDate这个列来决定这种情况下 你可以把REcdate加入到（Orderby　）子句中 倒序或者正序

这是一种保留最大Id的一种解决方案

DELETE

FROM Customers

WHERE ID IN

(SELECT ID

FROM

(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName FirstName ORDER BY RecDate DESC ID) AS SeqNumber

FROM

(SELECT ID LastName FirstName RecDate

FROM Customers

WHERE (LastName FirstName) IN (SELECT LastName FirstName

FROM Customers

GROUP BY LastName FirstName

HAVING COUNT(*) >)))

WHERE SeqNumber >)

这种技术保证了你可以控制每一个表中的保留的组 假设你有一个数据库 有一个促销或者有一个折扣信息 比如一个团体可以使用这种促销5次 或者个人可以使用这个折扣三次 为了指出要保留的组的个数 你可以在where 和having子句中进行设置 那么你将删除所有大于你

设置有数的冗余组

DELETE

FROM Customers

WHERE ID IN

(SELECT ID

FROM

(SELECT ID LastName FirstName RANK() OVER (PARTITION BY LastName

FirstName ORDER BY ID) AS SeqNumber

FROM

(SELECT ID LastName FirstName

FROM Customers

WHERE (LastName FirstName) IN (SELECT LastName FirstName

FROM Customers

GROUP BY LastName FirstName

HAVING COUNT(*) >)))

WHERE SeqNumber >)

As you can see using the RANK() function allows you to eliminate duplicates in a

single SQL statement and gives you more capabilities by extending the power of

your

queries

lishixinzhi/Article/program/Oracle/201311/18979

数据应该尽可能少地冗余，这意味着重复数据应该减少到最少。比如说，一个部门雇员的电话不应该被存储在不同的表中， 因为这里的电话号码是雇员的一个属性。如果存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题，当这个员工的电话号码变化时，冗余数据会导致对多个表的更新动作，如果有一个表不幸被忽略了，那么就可能导致数据的不一致性。 从一范式转化到二范式根据第二范式的定义，转化为二范式就是消除部分依赖。考察表1-1，我们可以发现，非主属性<Project Name>部分依赖于主键中的<Project Number>非主属性<Employee Name>，<Salary Category>和<Salary package>都部分依赖于主键中的<Employee Number>；表1-1的形式，存在着以下潜在问题：1． 数据冗余：每一个字段都有值重复；2． 更新异常：比如<Project Name>字段的值，比如对值"TPMS"了修改，那么就要一次更新该字段的多个值；3． 插入异常：如果新建了一个Project，名字为TPT, 但是还没有Employee加入，那么<Employee Number>将会空缺，而该字段是主键的一部分，因此将无法插入记录；Insert into SAMPLE(PRJNUM, PRJNAME, EMYNUM, EMYNAME, SALCATEGORY, SALPACKAGE) values(100003, 'TPT', NULL, NULL, NULL, NULL)

4． 删除异常：如果一个员工 200003, Kevin 离职了，要将该员工的记录从表中删除，而此时相关的Salary信息 C 也将丢失, 因为再没有别的行纪录下 Salary C的信息。Delete from sample where EMYNUM = 200003

Select distinct SALCATEGORY, SALPACKAGE from SAMPLE因此，我们需要将存在部分依赖关系的主属性和非主属性从满足第一范式的表中分离出来，形成一张新的表，而新表和旧表之间是一对多的关系。由此，我们得到：

CREATE TABLE "PROJECT" ( "PRJNUM" INTEGER NOT NULL, "PRJNAME" VARCHAR(200)) IN "USERSPACE1"ALTER TABLE "PROJECT" ADD PRIMARY KEY ("PRJNUM")Insert into PROJECT(PRJNUM, PRJNAME) values(100001, 'TPMS'), (100002, 'TCT')

表1-2

表 1-3

CREATE TABLE "EMPLOYEE" ( "EMYNUM" INTEGER NOT NULL, "EMYNAME" VARCHAR(200), "SALCATEGORY" CHAR(1), "SALPACKAGE" INTEGER) IN "USERSPACE1"ALTER TABLE "EMPLOYEE" ADD PRIMARY KEY ("EMYNUM")Insert into EMPLOYEE(EMYNUM, EMYNAME, SALCATEGORY, SALPACKAGE) values(200001,'Johnson', 'A', 2000), (200002, 'Christine', 'B', 3000), (200003, 'Kevin', 'C',4000), (200004, 'Apple', 'B', 3000)Employee Number Employee Name Salary Category Salary Package200001 Johnson A 2000200002 Christine B 3000200003 Kevin C 4000200004 Apple B 3000

CREATE TABLE "PRJ_EMY" ( "PRJNUM" INTEGER NOT NULL, "EMYNUM" INTEGER NOT NULL) IN "USERSPACE1"ALTER TABLE "PRJ_EMY" ADD PRIMARY KEY ("PRJNUM", "EMYNUM")Insert into PRJ_EMY(PRJNUM, EMYNUM) values(100001, 200001), (100001, 200002),(100001, 200003), (100002, 200001), (100002, 200004)

同时，我们把表1-1的主键，也就是表1-2和表1-3的各自的主键提取出来，单独形成一张表，来表明表1-2和表1-3之间的关联关系：

表 1-4

这时候我们仔细观察一下表1-2, 1-3, 1-4, 我们发现插入异常已经不存在了，当我们引入一个新的项目 TPT 的时候，我们只需要向表1-2 中插入一条数据就可以了， 当有新人加入项目 TPT 的时候，我们需要向表1-3, 1-4 中各插入一条数据就可以了。虽然我们解决了一个大问题，但是仔细观察我们还是发现有问题存在。

回页首

从二范式转化到三范式考察表前面生成的三张表，我们发现，表1-3存在传递依赖关系，即：关键字段<Employee Number >-->非关键字段<Salary Category >-->非关键字段<Salary Package >。而这是不满足三范式的规则的，存在以下的不足：1、 数据冗余：<Salary Category>和<Salary Package>的值有重复；2、 更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况；3、 删除异常：同样的，如果员工 200003 Kevin 离开了公司，会直接导致 Salary C 的信息的丢失。Delete from EMPLOYEE where EMYNUM = 200003

Select distinct SALCATEGORY, SALPACKAGE from EMPLOYEE因此，我们需要继续进行规范化的过程，把表1-3拆开，我们得到：

表 1-5

和

表 1-6

这时候如果 200003 Kevin 离开公司，我们只需要从表 1-5 中删除他就可以了， 存在于表1-6中的Salary C信息并不会丢失。但是我们要注意到除了表 1-5 中存在 Kevin 的信息之外， 表1-4中也存在 Kevin 的信息， 这很容易理解， 因为 Kevin 参与了项目 100001， TPMS， 所以当然也要从中删除。 至此，我们将表1-1经过规范化步骤，得到四张表，满足了三范式的约束要求，数据冗余、更新异常、插入异常和删除异常。在三范式之上，还存在着更为严格约束的BC范式和四范式，但是这两种形式在商业应用中很少用到，在绝大多数情况下，三范式已经满足了数据库表规范化的要求，有效地解决了数据冗余和维护 *** 作的异常问题。

数据冗余指数据之间的重复，也可以说是同一数据存储在不同数据文件中的现象。可以说增加数据的独立性和减少数据冗余为企业范围信息资源管理和大规模信息系统获得成功的前提条件。

数据冗余会妨碍数据库中数据的完整性(integrality)，也会造成存贮空间的浪费。尽可能地降低数据冗余度，是数据库设计的主要目标之一。关系模式的规范化理沦(以下称NF理论)的主要思想之一就是最小冗余原则，即规范化的关系模式在某种意义上应该冗余度最小。

但是，NF理论没有标准的概念可用，按等价原则，在有或没有泛关系假设(universal relation assumption)等不同前提下，冗余的定义可能有好几种。

数据的应用中为了某种目的采取数据冗余方式。

1、重复存储或传输数据以防止数据的丢失。

2、对数据进行冗余性的编码来防止数据的丢失、错误，并提供对错误数据进行反变换得到原始数据的功能。

3、为简化流程所造成额数据冗余。

4、为加快处理过程而将同一数据在不同地点存放。

5、为方便处理而使同一信息在不同地点有不同的表现形式。

6、大量数据的索引，一般在数据库中经常使用。

7、方法类的信息冗余。

8、为了完备性而配备的冗余数据。

9、规则性的冗余。根据法律、制度、规则等约束进行的。

10、为达到其他目的所进行的冗余。

---