实验中的现象关于rownum

最新推荐文章于 2022-11-13 10:47:09 发布

coin2016

最新推荐文章于 2022-11-13 10:47:09 发布

阅读量124

点赞数

实验的目的：查看in和not in的效率差别：

父表存储父亲，子表存储孩子，然后通过pid和父表关联，查询需要的结果是找到尚未有孩子的父亲。

not in的写法：

　　select * from parent where id not in (select pid　from children)

in的写法：

　　select * from parent where id in

　　( select id from parent minus select pid　from children )

在网上看到有人说有差别想自己做做看，但是实验发现了下面关于rownum的问题：

创建两个表

drop table parent;
　　create table parent(id varchar(10),name varchar(100), primary key (id) );
　　
　　drop table children;
　　create table childen(id varchar(10),pid varchar(10), name varchar(100), primary key (id) );
　　
　　alter table CHILDREN
　　add constraint fk_123 foreign key (PID)
　　references parent (ID);
　　

往父表中添加数据一千行可以作为表连接的驱动表使用
declare
i integer;
begin
i := 0;
delete from parent;
loop
i := i + 1;
dbms_output.put_line(i);
insert into parent(id, name) values(i, 'name ' || i);
if (i mod 100=0) then
commit;
end if;
exit when i > 1000;
end loop;
commit;
end;

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: 在 "SYS.DBMS_OUTPUT", line 32
ORA-06512: 在 "SYS.DBMS_OUTPUT", line 97
ORA-06512: 在 "SYS.DBMS_OUTPUT", line 112
ORA-06512: 在 line 9

SQL> select count(*) from parents;

select count(*) from parents

ORA-00942: 表或视图不存在

SQL> select count(*) from parent;

COUNT(*)
----------
700

Executed in 0.015 seconds

SQL>

往子表中添加数据添加50万行的数据
declare
i integer;
j integer;
begin
i := 0;
delete from children ;
loop
j := 0;
loop
i := i + 1;
j := j + 1;
insert into children(id, pid, name) values(i, j, 'name ' || j);
if (i mod 100=0) then
commit;
end if;
exit when j>= 50;
end loop;
exit when i >= 10000 * 50;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed

Executed in 119.61 seconds

发现了点有趣的事情：
当做全部选择的时候按照rownum的顺序id的顺序就是存储的顺序没错，但是按照id选择的时候就会出现以下的情况，并未按照id的正常存入顺序，我猜想是由于建立了主键的关系而自动产生的索引，单搜索id的时候走了索引全扫描。但是难道索引的排序方法和我们order by id的时候排序的方法是不一样的么？还是说rownum在索引中和在表中的形式不一样？

SQL> select * from children where rownum<10;

ID         PID        NAME
---------- ---------- --------------------------------------------------------------------------------
1          1          name 1
2          2          name 2
3          3          name 3
4          4          name 4
5          5          name 5
6          6          name 6
7          7          name 7
8          8          name 8
9          9          name 9

9 rows selected

Executed in 0.078 seconds

截图看执行计划

SQL> select id from children where rownum<10;

ID
----------
1
10
100
1000
10000
100000
100001
100002
100003

9 rows selected

Executed in 0.063 seconds

SQL>