begin for x in 1..1000000 loop select id into mm from biao where id=x; when mm is null then insert into biao values(x); end loop;
end;
with ta as( select 1 id from dual union all select 2 from dual union all select 3 from dual union all select 4 from dual union all select 5 from dual union all select 6 from dual union all select 7 from dual union all select 8 from dual union all select 9 from dual) ,tb as( select 1 id from dual union all select 2 from dual union all select 4 from dual union all select 5 from dual union all select 7 from dual union all select 9 from dual) select id from ta where not exists( select id from tb where ta.id=tb.id) / ID ---------- 8 3 6
SQL> with ta as( 2 select level id from dual connect by level<=9 3 ) 4 ,tb as( 5 select 1 id from dual union all 6 select 2 from dual union all 7 select 4 from dual union all 8 select 5 from dual union all 9 select 7 from dual union all 10 select 9 from dual) 11 select id 12 from ta 13 where not exists( 14 select id 15 from tb 16 where ta.id=tb.id) 17 / ID ---------- 8 3 6
--1.MINUS SELECT LEVEL id FROM dual CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt) MINUS SELECT id FROM tt;--2.NOT EXISTS SELECT * FROM (SELECT LEVEL id FROM dual CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id) ORDER BY 1;-3.NOT IN SELECT * FROM (SELECT LEVEL id FROM dual CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id);-4.LAG WITH t1 AS (SELECT s, e FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt) WHERE e - s >= 0) SELECT a.s + b.rn - 1 h FROM t1 a, (SELECT rownum rn FROM (SELECT MAX(e - s + 1) gap FROM t1) CONNECT BY rownum < gap) b WHERE a.s + b.rn - 1 <= a.e ORDER BY 1;
--下面是上面的执行时间测试 SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining optionsSQL> select * from tt; ID ---------- 1 2 4 5 7 9 3000 100000已选择8行。SQL> set timing on;SQL> --1.MINUS SQL> select count(*) from ( 2 SELECT LEVEL id 3 FROM dual 4 CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt) 5 MINUS 6 SELECT id FROM tt 7 ); COUNT(*) ---------- 99992已用时间: 00: 00: 00.18SQL> --2.NOT EXISTS SQL> select count(*) from ( 2 SELECT * 3 FROM (SELECT LEVEL id 4 FROM dual 5 CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a 6 WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id) 7 ORDER BY 1 8 ); COUNT(*) ---------- 99992已用时间: 00: 00: 00.21SQL> --3.NOT IN SQL> select count(*) from ( 2 SELECT * 3 FROM (SELECT LEVEL id 4 FROM dual 5 CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a 6 WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id) 7 ); COUNT(*) ---------- 99992已用时间: 00: 00: 02.67SQL> --4.LAG SQL> select count(*) from ( 2 WITH t1 AS 3 (SELECT s, e 4 FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt) 5 WHERE e - s >= 0) 6 SELECT a.s + b.rn - 1 h 7 FROM t1 a, 8 (SELECT rownum rn 9 FROM (SELECT MAX(e - s + 1) gap FROM t1) 10 CONNECT BY rownum < gap) b 11 WHERE a.s + b.rn - 1 <= a.e 12 ORDER BY 1 13 ); COUNT(*) ---------- 99991已用时间: 00: 00: 01.96
更正:第四种CONNECT BY rownum < gap 应为 CONNECT BY rownum <= gap
--1.MINUS SELECT LEVEL id FROM dual CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt) MINUS SELECT id FROM tt; 用connect by level时是不是行数大于100了只会取到100行啊
for x in 1..1000000 loop
select id into mm from biao where id=x;
when mm is null
then insert into biao values(x);
end loop;
end;
with ta as(
select 1 id from dual union all
select 2 from dual union all
select 3 from dual union all
select 4 from dual union all
select 5 from dual union all
select 6 from dual union all
select 7 from dual union all
select 8 from dual union all
select 9 from dual)
,tb as(
select 1 id from dual union all
select 2 from dual union all
select 4 from dual union all
select 5 from dual union all
select 7 from dual union all
select 9 from dual)
select id
from ta
where not exists(
select id
from tb
where ta.id=tb.id)
/
ID
----------
8
3
6
SQL> with ta as(
2 select level id from dual connect by level<=9
3 )
4 ,tb as(
5 select 1 id from dual union all
6 select 2 from dual union all
7 select 4 from dual union all
8 select 5 from dual union all
9 select 7 from dual union all
10 select 9 from dual)
11 select id
12 from ta
13 where not exists(
14 select id
15 from tb
16 where ta.id=tb.id)
17 / ID
----------
8
3
6
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;--2.NOT EXISTS
SELECT *
FROM (SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id)
ORDER BY 1;-3.NOT IN
SELECT *
FROM (SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id);-4.LAG
WITH t1 AS
(SELECT s, e
FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt)
WHERE e - s >= 0)
SELECT a.s + b.rn - 1 h
FROM t1 a,
(SELECT rownum rn
FROM (SELECT MAX(e - s + 1) gap FROM t1)
CONNECT BY rownum < gap) b
WHERE a.s + b.rn - 1 <= a.e
ORDER BY 1;
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> select * from tt; ID
----------
1
2
4
5
7
9
3000
100000已选择8行。SQL> set timing on;SQL> --1.MINUS
SQL> select count(*) from (
2 SELECT LEVEL id
3 FROM dual
4 CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
5 MINUS
6 SELECT id FROM tt
7 ); COUNT(*)
----------
99992已用时间: 00: 00: 00.18SQL> --2.NOT EXISTS
SQL> select count(*) from (
2 SELECT *
3 FROM (SELECT LEVEL id
4 FROM dual
5 CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
6 WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id)
7 ORDER BY 1
8 ); COUNT(*)
----------
99992已用时间: 00: 00: 00.21SQL> --3.NOT IN
SQL> select count(*) from (
2 SELECT *
3 FROM (SELECT LEVEL id
4 FROM dual
5 CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
6 WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id)
7 ); COUNT(*)
----------
99992已用时间: 00: 00: 02.67SQL> --4.LAG
SQL> select count(*) from (
2 WITH t1 AS
3 (SELECT s, e
4 FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt)
5 WHERE e - s >= 0)
6 SELECT a.s + b.rn - 1 h
7 FROM t1 a,
8 (SELECT rownum rn
9 FROM (SELECT MAX(e - s + 1) gap FROM t1)
10 CONNECT BY rownum < gap) b
11 WHERE a.s + b.rn - 1 <= a.e
12 ORDER BY 1
13 ); COUNT(*)
----------
99991已用时间: 00: 00: 01.96
oarcle lag()分析函数
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;
用connect by level时是不是行数大于100了只会取到100行啊