Oracle ROWID学习

rowid是一个用来唯一标记表中行的伪列。它是物理表中行数据的内部地址，包含两个地址，其一为指向数据表中包含该行的块所存放数据文件的地址，另一个是可以直接定位到数据行自身的这一行在数据块中的地址。
除了在同一聚簇中可能不唯一外，每条记录的rowid是唯一的。可以理解成rowid就是唯一的。

https://www.cndba.cn/hbhe0316/article/4941

https://www.cndba.cn/hbhe0316/article/4941

1.创建测试表https://www.cndba.cn/hbhe0316/article/4941

https://www.cndba.cn/hbhe0316/article/4941

SQL> create table t1 (id int,name varchar(10));
SQL> insert into t1 values (1,'a');
SQL> insert into t1 values (1,'a');
SQL> insert into t1 values (3,'c');
SQL> insert into t1 values (3,'c');
SQL> insert into t1 values (5,'e');
SQL> commit;

2.查询到的rowid

https://www.cndba.cn/hbhe0316/article/4941

https://www.cndba.cn/hbhe0316/article/4941 https://www.cndba.cn/hbhe0316/article/4941

SQL> SELECT ROWID FROM T1 ORDER BY ROWID;

ROWID
------------------
AAAR37AABAAAbZBAAA
AAAR37AABAAAbZBAAB
AAAR37AABAAAbZBAAC
AAAR37AABAAAbZBAAD
AAAR37AABAAAbZBAAE

3.如果需要删除重复的数据
⑴ 通过创建临时表
可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：

SQL> create table t1_tmp as select distinct * from t1;

Table created.

SQL> truncate table t1;

Table truncated.

SQL> insert into t1 select * from t1_tmp;

3 rows created.

SQL> select * from t1;

        ID NAME
---------- --------------------
         1 a
         3 c
         5 e

⑵ 利用rowid结合max或min函数https://www.cndba.cn/hbhe0316/article/4941

SQL> delete from t1 a where rowid not in (select max(b.rowid) from t1 b where a.id=b.id and a.name = b.name); 

2 rows deleted.

或者

https://www.cndba.cn/hbhe0316/article/4941

https://www.cndba.cn/hbhe0316/article/4941

SQL> delete from t1 a where rowid < (select max(b.rowid) from t1 b where a.id=b.id and a.name = b.name); 

2 rows deleted.

SQL> select * from t1;

        ID NAME
---------- --------------------
         5 e
         1 a
         3 c

或者

SQL> delete from t1 where rowid not in (select max(rowid) from t1 t group by t.id, t.name);

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID NAME
---------- --------------------
         5 e
         3 c
         1 a

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle