Oracle插入记录的顺序是否是读取的顺序?
通过一个简单的实验验证:
SQL> create table t ( x int, a char(2000) default 'x', b char(2000) default 'x', c char(2000) default 'x'); Table created. SQL> insert into t (x) values ( 1 ); 1 row created. SQL> insert into t (x) values ( 2); 1 row created. SQL> insert into t (x) values ( 3); 1 row created. SQL> commit; Commit complete. SQL> select x, rownum, rowid from t; X ROWNUM ROWID ---------- ---------- ------------------ 3 1 AAAOXNAAHAAAAasAAA 1 2 AAAOXNAAHAAAAavAAA 2 3 AAAOXNAAHAAAAawAAA
SQL> delete from t where x = 2; 1 row deleted. SQL> commit; Commit complete.
SQL> select x, rownum, rowid from t; X ROWNUM ROWID ---------- ---------- ------------------ 3 1 AAAOXNAAHAAAAasAAA 1 2 AAAOXNAAHAAAAavAAA SQL> insert into t (x) values ( 4 ); 1 row created.
SQL> select x, rownum, rowid from t; X ROWNUM ROWID ---------- ---------- ------------------ 3 1 AAAOXNAAHAAAAasAAA 1 2 AAAOXNAAHAAAAavAAA 4 3 AAAOXNAAHAAAAawAAA insert into t (x) values ( 5);
SQL> select x, rownum, rowid from t; X ROWNUM ROWID ---------- ---------- ------------------ 3 1 AAAOXNAAHAAAAasAAA 5 2 AAAOXNAAHAAAAatAAA 1 3 AAAOXNAAHAAAAavAAA 4 4 AAAOXNAAHAAAAawAAA SQL> insert into t (x) values ( 10); 1 row created. SQL> select x, rownum, rowid from t; X ROWNUM ROWID ---------- ---------- ------------------ 3 1 AAAOXNAAHAAAAasAAA 5 2 AAAOXNAAHAAAAatAAA 10 3 AAAOXNAAHAAAAauAAA 1 4 AAAOXNAAHAAAAavAAA 4 5 AAAOXNAAHAAAAawAAA
可见Oracle读取时按照记录的ROWID默认升序排列的,Oracle是一种堆表(默认),堆的意思就是杂乱无章的,插入数据时是根据内部算法,找到可用的数据块,一般出于效率的考虑,不采用原来的空间,用逻辑块的新空间,读取的顺序与COMMIT也没有直接关系,所以要排序,最好用ORDER BY。
扫码关注腾讯云开发者
领取腾讯云代金券
Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号:粤B2-20090059 深公网安备号 44030502008569
腾讯云计算(北京)有限责任公司 京ICP证150476号 | 京ICP备11018762号 | 京公网安备号11010802020287
Copyright © 2013 - 2025 Tencent Cloud.
All Rights Reserved. 腾讯云 版权所有