_覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
FrontPage › Partition
|
|
[edit]
1.3 #drop table p_test; drop partition scheme ps_test; drop partition function pf_test; go create partition function pf_test(date) as range right for values ( '20101101' , '20101102' , '20101103' ); create partition scheme ps_test as partition pf_test --to ([primary],[primary],[primary],[primary]); all to ([primary]); create table p_test(dt date, id varchar(20)) on ps_test(dt); insert p_test values('20101101', 'a'); insert p_test values('20101102', 'b'); insert p_test values('20101103', 'c'); drop index p_test.nix_id create index nix_id on p_test(id,dt)--覿 dt螳 伎朱 on ps_test(dt) select *, $partition.pf_test(dt) from p_test go /* 1. 狩 一危磯伎れ 企 一危磯ゼ 2. SPLIT覈轟朱 螳 襦 觜 一 3. SWITCH覈轟朱 企螻 一 */ --1. 狩 一危磯伎れ 企 一危磯ゼ drop table dbo.p_switch; create table dbo.p_switch(dt date, id varchar(20)) on [primary] insert p_switch values('20101104', 'd'); drop index p_switch.nix_id create index nix_id on p_switch(id, dt) --覿 dt螳 伎朱 --on ps_test(dt) alter table dbo.p_switch add constraint ck1 check(dt >= '20101104' and dt is not null); --2. SPLIT覈轟朱 螳 襦 觜 一 alter partition scheme ps_test next used [primary]; alter partition function pf_test() split range('20101104'); --3. SWITCH覈轟朱 企螻 一 alter table p_switch switch to p_test partition 5 select * from sys.partition_functions a inner join sys.partition_range_values b on a.function_id = b.function_id where name = 'pf_test' /* 1. 狩 一危磯伎れ 觜 企 2. SWITCH覈轟 伎 螳 る 一螻 觜 企 蟲豌 3. MERGE 覈轟 伎 る 一螻 伎 一 覲 */ --1. 狩 一危磯伎れ 觜 企 drop table dbo.p_drop; create table dbo.p_drop(dt date, id varchar(20)); --2. SWITCH覈轟 伎 螳 る 一螻 觜 企 蟲豌 alter table p_test switch partition 2 to p_drop; --3. MERGE 覈轟 伎 る 一螻 伎 一 覲 alter partition function pf_test() merge range('20101102') select * from sys.partition_functions a inner join sys.partition_range_values b on a.function_id = b.function_id where name = 'pf_test' [edit]
2 meta data #http://blogs.solidq.com/rdyess/Post.aspx?ID=4&title=Table+Partitioning+and+Indexes+(Part+2)
http://blogs.solidq.com/rdyess/Post.aspx?ID=5&title=Table+Partitioning+and+Indexes+(Part+1) --review the indexes just created select object_name(i.[object_id]) 'table name' , i.[name] as 'index name' , i.index_id as 'index id' , i.type_desc as 'index type' , ic.index_column_id as 'column' , c.[name] as 'column name' , ic.key_ordinal as 'key column' , ic.is_included_column as 'include column' , ic.partition_ordinal as 'partitioning column'--partitioning column added to index or part of key from sys.indexes i inner join sys.index_columns ic on i.[object_id] = ic.[object_id] and i.index_id = ic.index_id inner join sys.columns c on ic.[object_id] = c.[object_id] and ic.column_id = c.column_id order by i.name, i.index_id,ic.index_column_id select pf.[name] , pf.[type] --what type of partitioning function , pf.type_desc --what type of partitioning function , pf.fanout --how many partitions do we have , pf.boundary_value_on_right --1 for right, 0 for left , pf.create_date , pf.modify_date , rv.boundary_id , rv.[value] , t.[name] , s.[name] , pp.max_length , pp.[precision] , pp.scale , pp.collation_name , t.is_nullable , t.is_user_defined , t.is_assembly_type from sys.partition_functions pf inner join sys.partition_parameters pp on pf.function_id = pp.function_id inner join sys.types t on pp.system_type_id = t.system_type_id inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.partition_range_values rv on pp.function_id = rv.function_id and pp.parameter_id = rv.parameter_id order by pf.[name], rv.boundary_id [edit]
3 #
select @@version /* Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor) */ exec sp_db_increased_partitions @dbname='admin' , @increased_partitions='true' --true/on || false/off [edit]
5 一 譯殊 #一 企 蟆曙一 所 譯殊襯 . SQL覓語 燕 '一' 螳伎朱 磯Μ螳 襦 牛磯伎襯 讌願 . 襯 れ, れ螻 螳 蟆曙磯.
select max(col_a) from partition_table_a
れ襦 企慨覃 れ螻 螳. (伎 一 襷 企 所 覲蟆渚伎 .)--一牛 企 襷り鍵 drop table p_test; drop partition scheme ps_test; drop partition function pf_test; create partition function pf_test(date) as range right for values ( '20101101' , '20101102' , '20101103' ); create partition scheme ps_test as partition pf_test to ([primary],[primary],[primary],[primary]); create table p_test(dt date, cola char(500)) on ps_test(dt); insert p_test values('20101101', 'aa') go 100 insert p_test values('20101102', 'aa') go 100 insert p_test values('20101103', 'aa') go 100 れ sql覓語 ろ 覲願 ろ螻 危エ覲伎
select max(dt) from p_test Rows Executes StmtText -------------------- -------------------- --------------------------------------------------------------------------- 1 1 select max(dt) from p_test 1 1 |--Stream Aggregate(DEFINE:([Expr1005]=MAX([work].[dbo].[p_test].[dt]))) 300 1 |--Table Scan(OBJECT:([work].[dbo].[p_test])) Rows貉殊 覲企 300蟇(れ) 所螻 蟆郁骸襯 詞. 願 覦 企 1蟇企 暑讌 3蟇企 曙伎 max(dt)螳 譟壱 蟆伎. 蠏碁 企至 願屋蟾? 螳. metadata襯 伎 覦覯企. 覃 partition 蟆所 ロ dummy企 伎 覦覯企. 蠍一 metadata襯 伎企慨.
select max(b.dt) from sys.partitions a cross apply ( select top 1 dt from p_test where $partition.pf_test(dt) = a.partition_number ) b where object_id = object_id('dbo.p_test') Rows Executes StmtText -------------------- -------------------- --------------------------------------------------------------------------------- 1 1 |--Stream Aggregate(DEFINE:([Expr1010]=MAX([work].[dbo].[p_test].[dt]))) 3 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([rs].[numpart])) 4 1 |--Clustered Index Scan(OBJECT:([work].[sys].[sysrowsets].[clust] AS 3 4 |--Top(TOP EXPRESSION:((1))) 3 4 |--Table Scan(OBJECT:([work].[dbo].[p_test]), SEEK:([PtnId1006]=sys.partitions 4蟇伎 曙螻, p_test 企 3蟇企 曙 蟆 覲伎 蟆企.
鏤
|
覈 殊 襷れ企 蟆覲企 覿 蟆 蟇語 蟆企. |