Contents

1 Surrogate Key ?
2 OLTP ろ Surrogate Key( 襷螻 朱)
3 螳レ 曙 一(一危 豢豢 覃 譬)
4 蟆磯


1 Surrogate Key ? #

Surrogate Key(襴), Natural Key(壱)襯 豌危 るゼ 襷. Surrogate Key れ螻 螳 3螳讌 覈朱 .

  • 壱れ 螻糾 譴 焔レ レり鍵 .
  • 螻譬覯語 螳 譴 襭襯 豌危 覲伎 企 至鍵 .
  • 覲蟆曙 レ 覦蠍 .

企ゼ る蠍 れ 蠏碁殊 覲伎.

surrogate_key01.jpg

* 2009-11-23 ERD襯 れ覲企 れ 襷讌 ERD襯 豢 給. 螻螳ID襯 る蠍 蟆企襦..

壱れ 螻糾 譴 焔レ レり鍵
蠏碁殊 壱る '螻螳ID'企. 朱朱 12Byte ~ 20Byte 襦 . 企 螻褐 e覃 譯殊襯 螻螳 ID襦 螻葛 . 螻螳 1豌 襷 覈願, 蠏 譯朱瑚唄 10蟇(豐 10糾唄), 覦一♀唄 2豌襷蟇, 螻螳ID螳 12Byte 蟆曙一 Surrogate Key襯 4Byte 襯 蟆曙一 一危 蠍一 谿 れ螻 螳. (一危 レ 蟲磯蠍磯れ 螻壱讌 . 碁煙 蠍郁讌 一覃 襷 谿願 蟆企.)

蟲覿/螻螳ID 12Byte (varchar(12))4Byte (int)
螻螳(1豌襷蟇) 144MB38MB
譯朱(1糾唄) 1440MB380MB
覦一(2豌襷蟇) 228MB76MB
1812MB494MB

一危一 蠍磯ゼ 譴企 焔レ 譬讌 蟆 蟾讌 讌襴. 螳 蟆郁骸襯 視 蟆 暑る ろ 譴企. 蠏碁覃 一る所 覃覈襴 譴企 蟆願, CPU螳 豌襴伎 一危磯 伎蠍 覓語企. 譯 轟壱 襴. 螻螳 蟆曙磯 Key Entity(or 襷ろ 一危 or 蠍一覲)願鍵 覓語 螻螳ID 12Byte 蠏 ルレ 襷れ 襷螳. 螻螳 1豌 襷 覈 螻螳ID襯 Surrogate Key襦襷 覦蠑語企 5%伎 覿螳 譴企 蟆企.

螻譬覯語 螳 譴 襭襯 豌危 覲伎 企 至鍵
蠏碁殊 螻螳ID 3蟲磯一 . 3蟲磯 譴 企 螻褐 螳 螻螳ID 語 . 讀 螳 蠍一 ID襷 襷襷 襾轟朱 覲企ゼ 豢 螳レ煙 襷れ 襷. 襷 Surrogate Key襯 る 螻螳ID 1蟲磯磯 譟伎覩襦 覓企 語 煙 .

覲蟆曙 レ 覦蠍
Surrogate Key 朱 覓企 覩瑚 蠍 覓語, Surrogate Key 覓朱れ 螳豌願 覦り 企 蟆 覓企Μ螳 . 襯 れ, 一危 覈語 螻螳ID 覲企. 襷 螻螳 螻螳ID襯 覲蟆渚蠍磯ゼ る ル 螳豌企 覈 螳瑚? 襷, 螻螳ID襯 'databaser' -> 'dwa'襦 覲蟆曙 る 3螳 企 覲蟆曙 殊企 . 襷 譯朱瑚唄螳 100蟇伎企朱 企 200蟇 伎 螳煙 企伎語 . 襷 螻螳ID Surrogate Key襯 る 1蟇伎 螳煙朱 . 企 蟆曙 Surrogate Key襯 覃 企リ襴 襷れ 危伎. 襷 Surrogate Key襯 讌 る 企リ襴 企 3螳螳 伎 讌 覈襯碁. 谿瑚襦 DW 谿 企リ襴 覦 伎 ろ螻殊 襴曙 讌襯 伎 谿 蠍磯蓋る 襴るゼ 伎 .


2 OLTP ろ Surrogate Key( 襷螻 朱) #

一危磯伎れ り手 覃 覓朱Μ り襯 覩誤. 讀, 碁煙, 企, 曙^蟇 煙 螳 . 讀 一危磯伎る朱 貊企 豁れ 蟆 轟壱蟆 蟆讌蠍磯 . 襯 る, (讌貊, 讌覈)螻 螳 企. ERP(Enterprise Resource Planning) 螳 ろ 蟆曙 貊襷ろ謂 朱 覈豺朱 貊企 覿覿 譟伎. 企蟆 貊企 伎 覘蟾? 覦磯れ 蠏碁蟆 伎蠍 覓語企 れ 蠏碁蟆 蠍 覓語 豈 蠏碁蟆 讌蠍 覓語企手 牛 殊企. 貊 企 伎 讌覲伎 ク襴炎骸 焔 レ . 襾殊 讌覲伎 ク襴煙 伎手鍵 企慨. 貊 企 蟲 一危磯伎れ 蟆曙 螻殊 覈豺 覲蟆暑る 貊 企 企 螻殊 貊覈 螻豺覃 . 襷, 貊 企 る 蟲 一危磯伎 蟯襴 螻殊 蟯 覈 企 谿場 螳煙伎 蟆願 螻殊 豺 企 覦 語 蠍磯 . 蠏碁 焔 レ 企至 蟆手?

  • 讌覲伎 ク襴: 螻殊 覈豺 覲蟆暑る 貊企 企 螻殊 貊覈襷 螻豎譯朱 . 襷 貊 企 る 覈 蟯 企 螳煙伎 .
  • 焔: 貊 INT願, 貊覈 螳覲蠍語 覓語(豕 20) 企朱 INT ロ 蟆 リ概螳 蟆 ′襾濠, 襷 碁煙 煙 螳豌願 焔る 碁煙れ 蠍磯 譴企 蟆企 . 100 襷 蟇伎 一危郁 り 螳. INT企朱 4*1000000 覦危語企, 螳覲蠍語 覓語企朱 20 * 1000000 覦危瑚 . 襯 る, 500伎讌襴 豈 暑 蟆螻 1000伎讌襴 豈 暑 蟆螻殊 谿伎企.

一危磯伎 蟯 豈 覲企 覈碁 覈碁 譯手 螳 れ願る 襷 所 . 襷 襷伎襷 譯手企朱 覩碁 譬 覩碁 伎 讌襷 覩碁 伎 . れ 襷 譯手 れ瑚襯 覦覯 谿 襦 伎 讌襷, 覩碁 伎覃 覈碁 ルレ企手 覲 蠍 覓語企( 襦語 語 譴 覈語企 り覓語襯 蟆 覓語 讌 蟇一 蠍 覓語 蟆讀 碁豌伎 蟆襯 襤壱 蟆 譬). り 磯殊 企至 るジ 蟲 企伎讌 焔レ 谿願 讌 襯 牛 覲伎.

dim02.jpg

蠏碁手骸 螳 螻 襦 るジ 螳讌 覈語 豢り 螳. A, B 覈語 谿企 蠏碁殊 覲企 覦 螳 A覈語 觜覲 蟯螻襯 襷崎 螻, B覈語 覲 蟯螻(碁 り 蠍磯蓋 れ 朱 豌願 )襯 襷崎 . 企 蟆 譬 覈語手? 襦 . 讌 A覈語 襷 A豌 襷れ讌 B豌 襷れ讌 ろ蟆 螻 る 覈語 覓語 り 覺 覓企逢. 讌襷 蠏碁蟆 讌 覈襯碁る 蠏瑚 覦襦 覓語企. ERD(entity Relationship Diagram)豌 蠍 覓語襯 語手 螳覦 讌覃伎 螳覦 螳 豐る 蠏 豌願 覈詞 蟆螻 螳. 蠏碁殊 覈語 れ 覓朱Μ朱 螳 蟲 螻, 覈語 豕 企 A襯 貊企企手 螳. 襷 G 企 A 企 轟 貉殊 一危磯ゼ 螳語 る 覈語 企 谿願 蟾? れ SQL襦 襷れ 覲碁る 貊 螳 蟆企.
--A 蟆曙
SELECT
	A.UID_A
,	G.UID_G
FROM G 
	INNER JOIN F ON G.UID_F = F.UID_F
	INNER JOIN C ON F.UID_C = C.UID_C
	INNER JOIN A ON C.UID_A = A.UID_A

--B 蟆曙
SELECT
	A.UID_A
,	G.UID_G
FROM G INNER JOIN A ON G.UID_A = A.UID_A

蟆曙 覈語 SQL ろ 蟆郁骸 螳. 蠏碁 A 覈語 覈 覯 譟一語 蟇一朱 觜襦 A 企 一危磯ゼ 譟壱 . B 蟆曙磯 覯 譟一語朱 一危磯ゼ 譟壱 . A 覈語 譟一誤 襷殊 一危磯ゼ 豌襴伎 螻 一危磯ゼ 谿剰鍵 襷 蠍語 蟇一 . 一危一 豢豌 覿覈伎. れ襦 覈語 A螳 覲旧″ . る螳? り螳 企至 讌 磯 焔レ 襷 谿願 螻, 一危一 豢豌螳 譯 覈 讌螻 SQL 伎.

蠏碁る B 覈語 譬 覈語企手 襷 蟾? B 覈語 蟯襴伎 貉 螳 企螻 蠏 襷殊 豢螳 螻糾 . り 蠍一 一危郁 企蟾讌 蠏狩 煙 讌 蟲 伎 . 襷 F 企 A 企 蠏狩伎 螻, G 企 A, C, F 覈 蠏狩伎 螻, 襾語 蠏殊 煙 る 覈語 蠏碁手骸 螳 A, B 覈語 狩 螳 蟆企.

dim03.jpg

企 蟆 襴讌 蟆 覈碁 り ルレ 磯 殊. 譯殊伎讌 伎 企 覈語 譬り 襷 . 譯殊伎 蟆 豕 企 A螳 襷れ 伎 る 蟆企. 覓朱Μ朱 覲企 UID_A螳 INT 蟆曙一 CHAR(50) 蟆曙磯 蟯螻襯 襷碕 碁煙 蠍一 レ 殊蟆 . 讀, 豕 企 螳覯殊磯 螳覯殊語襦 譬る 蟆企. UID_A螳 CHAR(50) 蟆曙 C, F, G 企 覲企 螳 (Row)襷 CHAR(50) 襷殊 螻糾 る 蟆 . 蠏碁 朱朱 貊 企 螳 蟆曙磯 襴(り)襯 企 螻糾螻 碁煙れ 蠍磯ゼ 譴 焔リ骸 蟷 覲伎 讌 碁.

3 螳レ 曙 一(一危 豢豢 覃 譬) #

襷 蟆曙 OLTP ろ 麹 伎 朱覯(Sequence, Surrogate Key)襯 . 覃, 企蟆 覃 蠏碁 ろ 豢 覯郁鍵 覓語企. 願 譬讌 伎 覈. 伎 'Surrogate Key 覦' 伎 り 危襴貅伎 れ JOIN 朱 覲旧′煙 螳譴. 企 蟆曙 螳覦 轟螳 讌覲伎襯 る 讌覲伎 觜 豌蟆 企.

朱覯碁ゼ Surrogate Key襦 覦伎 蟆曙 覿覿 Primary Key襦 れ. 蠏碁襷 Oracle螻 螳 DBMS Dense Index 企襦 SQL Server 螳 1谿, 2谿 碁煙 蟲譟磯ゼ 螳讌 DBMS覲企る レ . SQL Server 蟆曙 覓企 伎 朱覯語 Primary Key襯 れ覃 Clustered Index(Sparse Index)螳 焔. 蠏碁覩襦 危 焔 碁煙る 2谿 碁煙る Dense Index螳 . , Dense Index Sparse Index 讌覲伎 讌朱 レ 覦.

伎 襷蠍磯 讌襷 DW レ レ朱 譟伎 . ETL伎 伎覃 れ螳朱 覲願襦 一危磯ゼ 伎 . 襯 れ れ螻 螳 蟆曙磯.
if object_id('test') is not null
	drop table test;
go

with temp1(num)
as
(
        select 1 num
        union all
        select num + 1 from temp1
        where num + 1 <= 5000
),
temp2(num)
as
(
        select 1 num
        union all
        select num + 1 from temp2
        where num + 1 <= 600
)
select 
	identity(int,1,1) seq
,	convert(char(8), getdate() + b.num, 112) dt
,	left(newid(), 12) dumy1-- 一危磯ゼ 豈j鍵 
,	newid() dumy2-- 一危磯ゼ 豈j鍵 
into test
from temp1 a, temp2 b
option (maxrecursion 0);

create clustered index cix_seq
on test(seq);
--企 'test'. 蟆  1, 朱Μ 所鍵  19738, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  906

create index nix_dt
on test(dt);
--企 'test'. 蟆  1, 朱Μ 所鍵  20443, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  3953

碁煙れ れ螻 螳 襴.

  • seq -> clustered index
  • dt -> non-clustered index

襷 轟 讌 一危磯ゼ 螳語り る 朱朱 れ螻 螳 SQL 燕 蟆企.

set statistics io on
set nocount on

select *
from test 
where dt = '20080115'
--企 'test'. 蟆  1, 朱Μ 所鍵  15589, 覓朱Μ 所鍵  32, 覩碁Μ 所鍵  4

豕 手? . 一一 蟆 Sequence螳 . Sequence ル 襯 覩誤覩襦 螳 轟煙 螳讌. 蠏碁覩襦 讌 郁襯 覃 Non-Clustered Index螳 Clustered Index襯 伎 . 螳 れ螻 螳.

plan01.jpg

--狩企ろ磯 碁煙る 蟲譟一 れ′語り 覦螻, 覯蟆 襴讌 
--企ろ磯 碁煙るゼ 襦 . 

set statistics io on
set nocount on
declare 
	@dt char(8)
,	@min_seq int
,	@max_seq int
set @dt = '20080115'

select 
	@min_seq = min(seq)
,	@max_seq = max(seq)
from test 
where dt = @dt

select * from test
where seq between @min_seq and @max_seq
and left(dt,8) = @dt --碁煙 覈詩蟆..

--企 'test'. 蟆  2, 朱Μ 所鍵  6, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0
--企 'test'. 蟆  1, 朱Μ 所鍵  38, 覓朱Μ 所鍵  0, 覩碁Μ 所鍵  0
--select 15589/(6+38) --I/O螳 354覦 谿 !!

4 蟆磯 #

伎 OLTP蟆曙 覈 襷れ伎 蟆 DW蟲豢 ETL 蟒 蟆 る 蟆 覲伎. DW Surrogate Key襯 襷 殊 焔レ 襷れ レ 殊 殊企. 一煙 伎 襷れ 譴 殊企. 覓朱 OLTP蟆曙 Surrogate Key襯 襷 蟆 譴. 覓伎覲企る 覦襯 企Μ蠍 伎 碁ロ 蟆 譴.

譯殊伎 蟆曙 螳 蟆 襷れ 譬 殊企. 讌襷 ろ企 危襴貅伎れ 螻褐 一 蟆曙磯 譬 譟一姶 100% 一蠍郁 襷れ 蟆曙郁 襷 . 襷 る 襯 螳讌 蠍一企朱 蠏碁. 蟆曙 螳蠍 企給る 蟆曙 伎 覦覯 螳企慨.