Oracle数据库高效sql语句的整理

业务需求说明：由于之前公司后台APP端有一个document表，该表中包含了所有的信息，新的需求就是通过该表创建出一个新的用户表（usertable）和一个档案表（document，该表只保留原document的部分字段）。

ps：原document表的数据有25万条的数据，因此在处理的时候，需要考虑到性能的问题。

首先当然是对两个表进行字段的设计，然后是再将原document表中的数据导入到这两个表中。

方案1：第一次采用的是最简单，最直接的方法，但是忽视了效率的问题。

方法是通过查询原document表，依次处理每一条记录，并将处理后的结果再插入到需要新建的两个表。这个方法效率极其的低下，无法接受。于是寻找优化的方法。

方案2：采用oracle数据库自身的sql语句来实现。

1、由于在导入到usertable和document表中需要用到一个字段birthyear，而原document表中没有birthyear字段，因此首先需要对原document表进行添加一个字段birthyear，为了能够添加一个字段，并且不修改原documen表中的信息，我们重新将原document表的数据全部导入一个临时的表document_temp中。因此，这里涉及到两步操作：

　　（1）将原document导入到一个临时的document_temp表中

　　create table document_temp as  select * from document;

　　（2）将document_temp表的数据增加一个字段birthyear

　　alter  table document_temp add birthyear；

　　update document_temp set birthyear=to_char(createtime,'yyyy')-age;

　　update document_test_temp set birthyear =-1 where birthyear is null；

2、根据document_temp表创建新的usertable。

　　这里需要说明一下：由于一个用户可能对应的多个document记录，并且会存在同名同姓的情况，因此我们做了一步初步处理，处理的方式是：如果一个人的姓名（name）和出生年份（birthyear）都相同的话，我们认为他们就是同一个人。由于usertable中还有包含该用户拥有的档案总数（record_num）的字段。因此这一步设计到三步。

（1）将document_temp的表数据部分字段导入到usertable中，此时是有重复的。

　　insert into USERTABLE_TEST(ID ,ANONAME ,APPID ,BIRTHYEAR ,SEX,HOSPITALNAME ,HOSPITALCODE  )

　　select ID ,NAME ,BOOKPOP , BIRTHYEAR  ,SEX  ,HOSPITALNAME ,HOSPITALCODE 

　　from document_temp
（2）去除重复（在这一步中，由于上面产生的usertable是有25万条记录，对该表进行去除重复是需要考虑性能的问题。）
　　1）//删除重复，这种方法对于大批量的数据来说效率非常的慢，这当时不可取的方法。
　　　　delete from usertable_test a where (a.anoname,a.birthyear) in (select anoname,birthyear from usertable_test group by anoname,birthyear having count(*) > 1)
　　　　and rowid not in (select min(rowid) from usertable_test group by Id,seq having count(*)>1)

　　2）这种方法跟第一种方法本质上是一样的
　　　　delete usertable_test where rowid not in
　　　　(select min(rowid) from usertable_test group by anoname,birthyear);

　　3）后来，受我同学的启发，说查出重复后，不能删除，已删除效率就非常低，于是有下面的方法，但是下面的方式只能产生某些字段，因为我重复的记录是根据这个字段来决定的。
　　　　create table usertable_test_temp select distinct anoname ,birthyear from usertable_test

　　4）//针对大量的数据，通过这个可以高效的去除重复
　　　　create table usertable_test_temp as select * from  usertable_test where rowid in
　　　　(select min(rowid) from usertable_test group by anoname,birthyear);
（3）统计字段recordnum的值。
　　　　select count(*) as recordnum ,anoname,birthyear from usertable_test group by anoname,birthyear;

（4）为了简化步骤，可以将第（2）和（3）步进行合并成一步：

　　create table usertable_test_temp_temp as select X.*,Y.recordnum from  (select * from  usertable_test_temp where rowid in
　　(select min(rowid) from usertable_test_temp group by name,birthyear) ) X left join (select count(*) as recordnum ,name,birthyear from usertable_test_temp group by 　　　 　　　　name,birthyear) Y  on X.anoname=Y.anoname and X.birthyear=Y.birthyear;

　　此时可以得到一个已经去除重复的usertable表。

3、根据document_temp创建一个新的document的表。

　　业务要求是：在创建新的document表的时候，通过外键的形式关联userid，即新的document中添加一个字段userid的方式来关联USERTABLE表。所以在导入到新的document中需要关联usertable的id。

　　实现的方法：由于usertable也是根据document_temp表导入而来，因此，去重后的usertable与document_temp是有一个对应关系 ,也就是一个用户对应多个document记录。即每一个document_temp记录都有一个用户与之对应。因此可在导入新的document的时候，就将这个对应关系关联起来即可。

 insert into document_test (ID,DOCID,USERID,HOSPITALNAME,HOSPITALCODE,IMAGENUM,ANONAME,SEX,AGE,DEPARTMENT,DOCUMENTDATE,DOCUMENTURL,CREATETIME,CHECKPART,CHECKPRO,DOCUMENTPATH,birthyear)
select a.ID,a.ID,b.ID,a.HOSPITALNAME,a.HOSPITALCODE,a.IMAGENUM,a.NAME,a.SEX,a.AGE,a.DEPARTMENT,
a.DOCUMENTDATE,a.DOCUMENTURL,a.CREATETIME,a.CHECKPART,a.CHECKPRO,a.DOCUMENTPATH, a.BIRTHYEAR
 from  document_temp  a , usertable  b   where a.anoname = b.anoname and a.birthyear = b.birthyear

 

转载于:https://www.cnblogs.com/ljy2013/p/4756767.html