---创建测试环境:E1表,模拟大量重复值的表;
SQL> create table e1 as select * from emp;
SQL> insert into e1 select * from e1;
112 rows created.
SQL> commit;
--如何提取去重后的数据呢?
select distinct empno,sal from e1; OK
因为是一模一样的;
select * from e1
union
select * from e1;
还是可以;
---------------------------------
如果表很大(数据量大),列很多,使用distinct变为一个不现实的操作;
union<直接操作会影响性能;
----
比较合理的测试: 创建全局临时表:会话级别的;
create global temporary table la on commit preserve rows as select * from emp where 1=2;
insert into la (select * from e1 union select * from e1);
---随后,创建永久表,测试查询,是否OK
create table abc as select * from la;
select count(*) from abc;
COUNT(*)
----------
14
---删除垃圾表:
drop table e1;
create table e1 as select * from la; --重建,随后验证
select count(*) from e1;
COUNT(*)
----------
14