查詢與插入資料使用rownum與level

skyin_1603發表於2016-10-23

rownum與level在某些場景的用法是一樣的，但在某些場景就產生天淵之別的結果，提醒大家平時慎用level。
以下是兩者一些場景中使用的比較：

----rownum 與level的用法：
--例子：
題目：列印2016-06-15~2016-07-14之間的連續日期。

--使用rownum查詢：
SQL> select (to_date('2016-06-15','yyyy-mm-dd') + rownum-1) service_date
2 from dual
3 connect by rownum <= (to_date('2016-07-14','yyyy-mm-dd') -
4 to_date('2016-06-15','yyyy-mm-dd')+1);

SERVICE_DATE
-------------------
2016-06-15 00:00:00
2016-06-16 00:00:00
2016-06-17 00:00:00
2016-06-18 00:00:00
... ...
2016-07-12 00:00:00
2016-07-13 00:00:00
2016-07-14 00:00:00
30 rows selected.

--level查詢：
SQL> select (to_date('2016-06-15','yyyy-mm-dd') + level-1) service_date
2 from dual
3 connect by level <= (to_date('2016-07-14','yyyy-mm-dd') -
4 to_date('2016-06-15','yyyy-mm-dd')+1);

SERVICE_DATE
-------------------
2016-06-15 00:00:00
2016-06-16 00:00:00
2016-06-17 00:00:00
2016-06-18 00:00:00
2016-06-19 00:00:00
2016-06-20 00:00:00
2016-06-21 00:00:00
... ...
2016-07-12 00:00:00
2016-07-13 00:00:00
2016-07-14 00:00:00
30 rows selected.
以上兩種結果是一樣的。

--分解檢視：
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-10-21 18:44:06

SQL> select (sysdate + rownum) from dual;
(SYSDATE+ROWNUM)
-------------------
2016-10-22 18:46:11

SQL> select (sysdate + rownum-1) from dual
(SYSDATE+ROWNUM-1)
-------------------
2016-10-21 18:46:40

--使用level檢視：
SQL> select (sysdate + level-1) from dual connect by level<=5;
(SYSDATE+LEVEL-1)
-------------------
2016-10-21 18:50:46
2016-10-22 18:50:46
2016-10-23 18:50:46
2016-10-24 18:50:46
2016-10-25 18:50:46

SQL> select (sysdate +level) from dual connect by level<=5;

(SYSDATE+LEVEL)
-------------------
2016-10-22 18:51:10
2016-10-23 18:51:10
2016-10-24 18:51:10
2016-10-25 18:51:10
2016-10-26 18:51:10

SQL> select (sysdate + rownum) from dual connect by rownum <=5;
(SYSDATE+ROWNUM)
-------------------
2016-10-22 18:52:02
2016-10-23 18:52:02
2016-10-24 18:52:02
2016-10-25 18:52:02
2016-10-26 18:52:02

--使用兩個分別查詢emp表（表中有14條資料）：
--使用level查詢emp表：
SQL> col rownum for 99;
SQL> select rownum,empno,ename,job from emp
2 where rownum <=10;

ROWNUM EMPNO ENAME JOB
------ ---------- ---------- ---------
1 7369 SMITH CLERK
2 7499 ALLEN SALESMAN
3 7521 WARD SALESMAN
4 7566 JONES MANAGER
... ...
9 7839 KING PRESIDENT
10 7844 TURNER SALESMAN
10 rows selected.

SQL> select empno,ename,job from emp
2 connect by level<=2;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
... ...
EMPNO ENAME JOB
---------- ---------- ---------
7934 MILLER CLERK
210 rows selected.
可以看到兩者的產生的結果是不一樣的。

--使用兩者往測試空表中插入資料：
--使用rownum：
SQL> create table t7(id number(2),created date);
Table created.

--使用rownum進行插入：
SQL> insert into t7 select * from t6 where rownum <=5;
5 rows created.

--檢視錶t7的資料：
SQL> select * from t7;
ID CREATED
---------- ---------
1 21-OCT-16
2 21-OCT-16
3 21-OCT-16
4 21-OCT-16
5 21-OCT-16

--使用level：
SQL> insert into t7 select * from t6
2 connect by level <=2;
156 rows created.

--檢視錶t7的資料：
SQL> select * from t7 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
... ...
SQL> insert into t7 select * from t6
2 connect by level <=2;
156 rows created.

--檢視錶t7的資料：
SQL> select * from t7 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
... ...
ID CREATED
---------- -------------------
12 2016-10-21 19:02:56
12 2016-10-21 19:02:56
156 rows selected.

--建立表t8測試表：(t7中有4條記錄）
SQL> insert into t8 select * from t7
2 connect by level <=3;
84 rows created.

--檢視錶t8的資料：
SQL> select * from t8 order by 1;
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
ID CREATED
---------- -------------------
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
1 2016-10-21 19:02:56
2 2016-10-21 19:02:56
ID CREATED
---------- -------------------
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
ID CREATED
---------- -------------------
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
2 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
ID CREATED
---------- -------------------
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
ID CREATED
---------- -------------------
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
3 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
ID CREATED
---------- -------------------
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
ID CREATED
---------- -------------------
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
4 2016-10-21 19:02:56
84 rows selected.

從以上的測試過程中發現level其實就是層級、乘方的意思。就拿往空表t8中插入資料來說，表t7中有4條記錄，
我們利用表t7的資料查到表t8中，我們執行SQL> insert into t8 select * from t7 connect by level <=3;
最後發現出入了84行：
以下是模擬插入的過程：
----------第一：0+4*1=4；
----------第二：4+4*4=20；
----------第三：4+4*4+4*4*4=84。
總和就是84行，就是這樣理解level就可以了。

也即：connect by level<=N, (level一定要與connect by一起用，但rownum可以獨立用）
假設原表有X條資料，這樣查詢使用就是：connect by level<=N就是：
X+X*X+X*X*X*X+X*X*X*X*X+X...N個X相乘的結果，資料量大的話，查詢就花銷很多記憶體。
所以沒必要是，寧願使用rownum都不要使用level用來給測試表插入資料。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31392094/viewspace-2126956/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

Java ——MongDB 插入資料、模糊查詢、in查詢
2018-08-10
Java
查詢與排序04,插入排序
2014-07-29
排序
【CONNECT BY】使用connect by level/rownum實現連續數字的插入
2011-05-10
插入查詢資料的操作
2017-04-01
jpa 聯合查詢資料，查詢使用者資訊與部門資訊
2019-05-25
二分插入與二分查詢
2013-10-08
資料庫的查詢與檢視
2012-05-28
資料庫
.NET 資料庫大資料方案（插入、更新、刪除、查詢、插入或更新）
2023-10-08
資料庫大資料
【oracle rowid與rownum的使用與區別】
2016-11-15
Oracle
50個查詢系列-建表和插入資料
2016-11-29
GIS資料的查詢，插入，刪除，更新（ArcEngine）
2011-04-14
使用ROWNUM將導致查詢結果集的固化
2010-05-17
hive將查詢資料插入表中某欄位無資料
2020-10-25
Hive
資料塊的插入與高水位
2008-02-14
離線查詢與線上查詢
2018-12-11
查詢與排序02,折半查詢
2014-07-28
排序
使用rownum分頁排序奇怪現象一則：前後幾次查詢資料不一樣
2017-08-22
排序
ORACLE 資料庫查詢語句與DML語句
2019-10-16
Oracle資料庫
MYSQL查詢和插入資料的流程是怎樣的
2021-02-18
MySql
Milvus 2.0 資料插入與持久化
2022-03-31
持久化
DB2 Event Monitor使用與查詢
2017-04-20
DB2
圖解大資料 | 海量資料庫查詢-Hive與HBase詳解
2022-03-08
圖解大資料資料庫Hive
MySQL與PHP的基礎與應用專題之資料查詢SRBF
2022-03-01
MySqlPHP
在資料庫的查詢與更新中，CHARINDEX與instr的區別？
2024-04-27
資料庫Index
資料結構與演算法-二分查詢
2020-10-26
資料結構演算法
【資料結構與演算法】—— 二分查詢
2021-10-27
資料結構演算法
資料結構與演算法：查詢演算法
2020-09-29
資料結構演算法
資料結構與演算法——單詞查詢樹
2017-11-30
資料結構演算法
資料庫設計與查詢語句的優化
2013-11-06
資料庫優化
oracle中基於ROWNUM的查詢的返回
2009-08-09
Oracle
Django查詢特定條件的資料並插入其他表格模型
2024-04-24
Django模型
Mysql資料庫使用from與join兩表查詢的方法區別總結
2019-02-25
MySql資料庫
第二章：查詢與排序-------遞迴形式進行插入排序
2019-03-06
排序遞迴
MySQL 隨機查詢資料與隨機更新資料實現程式碼
2021-09-09
MySql隨機
如何實現MySQL隨機查詢資料與MySQL隨機更新資料？
2014-09-04
MySql隨機
Oracle中rownum與rowid使用上的問題
2009-08-04
Oracle
【Oracle】-【ROWNUM與索引】-索引對ROWNUM檢索的影響
2013-08-02
Oracle索引
SQL server資料庫with as子句與遞迴查詢的實現
2018-08-31
SQLServer資料庫遞迴