在scott用户下创建一张test表,create table test(id number,name varchar2(20));
插入如下数据:
insert into test values (1,'xiao');
insert into test values (1,'xiao');
insert into test values (2,'da');
insert into test values (2,'da');
insert into test values (3,'ping');
insert into test values (3,'ping');
会得到所有的行都重复的一张表,需要删除重复的行,可以用rowid来做标识。
下面简单介绍一下rowid:详情请参考,oracle rowid详解:http://www.2cto.com/database/201109/104961.html
SQL> select t.* ,rowid from test t;
ID NAME ROWID
---------- ---------- ------------------
1 xiao AAANV4AAEAAABtPAAA
2 da AAANV4AAEAAABtPAAB
2 da AAANV4AAEAAABtPAAC
3 ping AAANV4AAEAAABtPAAD
3 ping AAANV4AAEAAABtPAAE
1 xiao AAANV4AAEAAABtPAAI
oracle从8i开始引进object的概念后的rowid,即扩展(extended)的rowid,
ROWID的格式如下:
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
关于rowid的几个有用的查询:
1.显示64位编码的各个部分的值:
SQL> select rowid,substr(rowid,1,6) "OBJCT",substr(rowid,7,3) "FILE" ,
2 substr(rowid,10,6) "BLOCK", substr(rowid,16,3) "ROW" from test;
ROWID OBJCT FILE BLOCK ROW
------------------ ------------ ------ ------------ ------
AAANV4AAEAAABtPAAA AAANV4 AAE AAABtP AAA
AAANV4AAEAAABtPAAB AAANV4 AAE AAABtP AAB
AAANV4AAEAAABtPAAC AAANV4 AAE AAABtP AAC
AAANV4AAEAAABtPAAD AAANV4 AAE AAABtP AAD
AAANV4AAEAAABtPAAE AAANV4 AAE AAABtP AAE
AAANV4AAEAAABtPAAI AAANV4 AAE AAABtP AAI
2.通过dbms_rowid这个包可以得到具体rowid各个部分的信息:
SQL> select dbms_rowid.rowid_object(rowid) object_id,dbms_rowid.rowid_relative_fno(rowid)
2 file_id,dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_row_number(rowid)
3 num from test;
OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
54648 4 6991 0
54648 4 6991 1
54648 4 6991 2
54648 4 6991 3
54648 4 6991 4
54648 4 6991 8
每个表Oracle都存在一个伪列ROWID,这个伪列可以用SELECT查看,但是不可以用INSERT, UPDATE来修改。你也不可以用DELETE来删除
ROWID列,Oracle使用ROWID列来建立内部索引。
介绍rowid是一个插曲,言归正传,删除重复的行:
delete from test
where id in
(select id from test group by id having count(id) > 1)
and rowid not in (select min(rowid)
from test
group by id
having count(id) > 1)