2013年9月5日 星期四

產生數值序列(1~N)

對於數值序列的取得,MSSQL可由master.dbo.spt_values 系統資料表直接取得,不同版本有些許差異,MSSQL 2000值域為0~255,以上版本則為0~2047ORACLE並未提供類似的系統資料表,不過可使用CONNECT BY指令的方式產生,為避免耗用不必要資源建議建立1~10000的數值序列資料表,以供各系統使用。指令及說明如下:
功能
SQL (1~N)
說明
MSSQL
SELECT number
FROM master.dbo.spt_values
WHERE name IS NULL
ORDER BY 1
20000~255
200520080~2047
ORACLE
SELECT LEVEL N
FROM DUAL
CONNECT BY LEVEL<=100
利用CONNECT BY遞迴運算式產生。

以下建立Tally數值序列資料表並儲存10,000筆資料, MSSQL雖可直接使用master.dbo.spt_values系系資料表,但以CROSS JOIN語法方式建立,兩種SQL語法及說明如下:

MSSQL
ORACEL
建立資料表
CREATE TABLE Tally
(
N INT NOT NULL,
CONSTRAINT PK_Tally PRIMARY KEY(N)
)
CREATE TABLE TALLY
(
N NUMBER(10) NOT NULL,
CONSTRAINT PK_TALLY PRIMARY KEY (N)
) ORGANIZATION INDEX
儲存序列
INSERT INTO Tally
SELECT K.number * 1000
 + N.number +1
--FROM master.dbo.spt_values K
--   , master.dbo.spt_values N
FROM master.dbo.spt_values K
     CROSS JOIN master.dbo.spt_values N
WHERE 1=1
     AND K.name IS NULL
     AND N.name is NULL
     AND K.number <10
     AND N.number <1000
ORDER BY 1
INSERT INTO TALLY
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL<=10000

數值序列資料表僅有一個整數型態的欄位,欄位名稱為NMSSQL是以一般資料表方式建立;ORACLE則建議採用索引組織資料表(Index-organized TableIOT)方式,一般資料表的搜尋模式,索引空間中只儲存索引欄位資料,先由索引中搜尋定位資訊後,再藉此於實體資料表中快速找出(定位)、取得資料,而索引組織資料表(IOT)是將整個資料表的資料儲存於索引中,因此可大幅減少I/O次數並提昇效能。

沒有留言:

張貼留言