把近两个月存到备份表,然后TRUNCATE直接清空,然后把两个月的数据插入现在表

解决方案 »

  1.   

    自动执行就晚上设置个JOB定时运行吧
      

  2.   


    对你说的太对了。我忘记把一个背景条件说漏掉了:
    还有表 Table2, 表的结构跟Table1一模一样,是Table1的备份表,但是我发现这个Table2是空的,既然是备份表为啥是空的呢,一直感觉很奇怪。原来用你的方法, Table2 就派上用场了。
    那如何制定 作业,每天自动执行呢?
    我想过写SQL语句来: 每天删除 2个月以前的Table1表的数据。但是这么做,不知道如何在任务计划中部署??你有什么好的方法?
      

  3.   

    创建定时任务挺容易的
    http://www.cnblogs.com/peaceshow/archive/2012/07/16/2593934.html脚本可以类似这样
    TRUNCATE TABLE Table2INSERT INTO Table2
    SELECT * FROM Table1
    WHERE spyndate>=DATEADD(MONTH,-2,GETDATE())TRUNCATE TABLE Table1INSERT INTO Table1
    SELECT * FROM Table2TRUNCATE TABLE Table2
      

  4.   

    如果Table2是个普通表,那么当时设计者的初衷,应该是用来给Table1做切换分区用的。每次用Table2替换Table1中最老的分区,然后对Table2执行truncate操作,这样既不用停业务又可以避免DELETE的低效。不过,只能以分区为单位清理数据,即按月清理。详情参考ALTER TABLE ... SWITCH PARTITION命令。
      

  5.   

    从LZ的描述看,Table1中只保留2个月的数据? 那么是否需考虑原先的分区方案(按月分区)是否合理呢.
    例如, 当前是3月,只保留2个月内的数据,那么4->12这8个表分区都是空的? 以此类推,总是多数分区是空的,查询性能也不佳.
    请问是否真有必要?如果只想保留2个月内的数据,可以不必用分区表,并在时间字段上建索引即可.
      

  6.   

    每个分区都是百万级别的记录数,用insert 效率会不会太低了。
    用下面的语句效率才高吧
    ALTER TABLE [Table1] SWITCH PARTITION 1 TO [Table2] PARTITION 1ALTER TABLE [Table1] SWITCH PARTITION 2 TO [Table2] PARTITION 2
      

  7.   

    先说我当前用过的例子:
    表中只保存一年的数据,12个月,13个分区(包括主分区primary)
    编写一个作业,每个月1号定时创建最近一个分区,并删除最早一个分区
    (如到了2015-03-01 ,将删除2014-03-01的分区数据,并创建新的分区保存2015-03的数据)删除的办法和思路是:
    1. 增加新的分区,当前月份用(SPLIT RANGE)
    2. 切换该表最早的分区(primary)到另一个结构相同的表(SWITCH PARTITION 1 TO )
    3. 切换后,把最早的分区合并( MERGE RANGE)
    4. 把切换出来的表数据清空(truncate table 很快)
    5. 更新统计信息(或者重建索引)(其实我这表还处于同步复制中,就不写了,思路这样,希望有帮助)
      

  8.   


    谢谢你的回复!但是我们这边不能创建分区,也不能删除原始表。
    删除思路是这样,你可以改下。不用删除原来的表,是创建另一个表,表结构跟原表一样,这个表用来切换数据用。将最早的两个分区迁移到这个表,再truncate。切一个分区就truncate一次表。
      

  9.   

    现在的情况是这样。 有一张副表 Table2,表Table2跟表Table1结构一样,用到同样的分区构架/方案,用于作切换分区。
    前提是: 
    1  表 Table1 数据不能删除,所以不能使用该语句: truncate table Table1
    2   保留 2 个月以内的数据,每个月的第一天执行作业脚本。以4月1日为例,保留2月份和3月份的数据,其他分区数据全部删除。现在碰到的问题是:
    如何正确在SQL语句中把时间规划好?  尤其是在 2月1日时,要保留当年1月份的数据,和去年12月的数据。
    现在我把脚本写好了,如下效果:truncate table Table2-----首先确认系统时间是几月份
    DATEPART(month, GETDATE()) -----然后用当前的月份数字往前面减去1,减2,得到要保留的数据
    -------   这个应该写个PL/SQL,但是自己不知道该如何去实现,请大家帮帮忙。注意要跨越2年的数据(在2月1日开始执行作业,那么去年12月的数据必须保留)。
    -------
    -----假设要保留数据的月份是M,N,分别对应的分区是 partition M, partition N,那么除了M,N外,其他分区的数据必须通过切换分区到 Table2 上去,删除掉。假设剩下8个月的数据分区是A, B, C,D,过程如下:alter table Table1 switch partition A to Table2 partition A
    alter table Table1 switch partition B to Table2 partition B
    .........
    ........truncate table  Table2请大家帮帮忙,谢谢!
      

  10.   

    SQL2008能直接按月分区吗?你的是日期类型啊~
      

  11.   


    Table1里面的数据一直没有处理过,有从2003年到2015年 1月份到12月份的数据。如果下个月的第一天4月1日,开始执行任务---只保留最近2个月(2月份,3月份)的数据,那么会删除1月份,4月份,5月份,.......,12月的数据,对应删除分区1,分区4,分区5,........, 分区12 的数据。
    但是,从此以后,每个月的第一天(如5月1日)只需要删除3个月以前的那个月的数据。
    5月1日执行任务,只需要删除2月份的数据,因为当前只有2月份,3月份,4月份有数据
    6月1日执行任务,只需要删除3月份的数据,
    ........
    ........
    ........
    12月1日执行任务,只需要删除9月份的数据
    1月1日执行任务,只需要删除10月份的数据
    2月1日执行任务,只需要删除11月份的数据
    3月1日执行任务,只需要删除12月份的数据
    4月1日执行任务,只需要删除1月份的数据。
    所以在写脚本时,要特别注意 1月1日,2月1日,3月1日执行任务时涉及的月份。
    因此脚本如下:
    #######################################################################
    truncate table Table2
    ---这里首先要判断一下当前月份是不是 1,2,3 这三个数。如果是1,切换分区10;如果是2,切换分区11;如果是3,切换分区12;
    ---但是具体SQL 不知道该如何写,感觉这里不能纯粹写SQL就能行,应为还要判断月份--------》需要大家帮忙写下,谢谢
    ---  ......
    ---如果当前月份不是 1,2,3 的话,就执行下面的SQL
    alter table Table1 switch partition (DATEPART(month, GETDATE())-3) to Table2 partition (DATEPART(month, GETDATE())-3)
    truncate table  Table2
    #######################################################################
    请帮忙看下,这个脚本该如何写呢?
    谢谢!
      

  12.   

    你们的SQL版本,我猜应该是SQL2008
    据我了解,按月分区,如果是时间类型,也只是一段时间内而已,你说你们12个分区,也应该就是某一年的12个月而已,因此调动分区,还需要改分区范围
    因此我需要知道你们是否是真的按时间类型分区,还是说是按由时间列计算出来的月份的计算列进行的分区?
    还是有什么我没有了解的知识,也请科普一下
      

  13.   


    假设分区函数和分区方案如下:CREATE PARTITION FUNCTION PF_PartitionedByMonth_1 (date)
    AS RANGE LEFT
    FOR VALUES ('2014-12-01', '2015-01-01', '2015-02-01', '2015-03-01')CREATE PARTITION SCHEME PS_PartitionedByMonth_1
    AS PARTITION PF_PartitionedByMonth_1
    TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')假设表的定义如下CREATE TABLE Table1
    (
        id       int         IDENTITY(1, 1)
    ,   name     varchar(30)
    ,   gender   bit
    ,   spyndate date    CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (id, spyndate)) ON PS_PartitionedByMonth_1 (spyndate)查询保留两个月数据以外的最近的分区号,语句如下:SELECT
           CASE pf.boundary_value_on_right
               WHEN 0 THEN p.partition_number - 1
               ELSE        p.partition_number - 2
           END
    FROM      sys.objects                obj
    JOIN      sys.indexes                ind ON obj.object_id      = ind.object_id
    JOIN      sys.partition_schemes      ps  ON ind.data_space_id  = ps.data_space_id
    JOIN      sys.partition_functions    pf  ON ps.function_id     = pf.function_id
    JOIN      sys.partitions             p   ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
    LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
    WHERE prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
      

  14.   

    忘了加表名条件了,补充一下:SELECT
           CASE pf.boundary_value_on_right
               WHEN 0 THEN p.partition_number - 1
               ELSE        p.partition_number - 2
           END
    FROM      sys.objects                 obj
    JOIN      sys.indexes                 ind ON obj.object_id      = ind.object_id
    JOIN      sys.partition_schemes       ps  ON ind.data_space_id  = ps.data_space_id
    JOIN      sys.partition_functions     pf  ON ps.function_id     = pf.function_id
    JOIN      sys.partitions              p   ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
    LEFT JOIN sys.partition_range_values  prv ON p.partition_number = prv.boundary_id
    WHERE obj.name  = 'Table1'
    AND   prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
      

  15.   


    谢谢你的回复!你的分区方案是不是问题:
    CREATE PARTITION SCHEME PS_PartitionedByMonth_1
    AS PARTITION PF_PartitionedByMonth_1
    TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')你把所有的数据库都放在同一个文件组Primary,在切换分区时会不会把其他的分区数据也删除掉?
    还是说: 分区与文件组在做切换分区时不会相互影响?
      

  16.   

    SQL Server中的分区是指表或索引的分区,是一个逻辑上的概念,从大到小的分级是这样的:表或索引 -> 分区(partition) -> 分配单元(allocation unit) -> 区(extent) -> 页(page)。
    而文件组是一个物理上的概念,一个表的不同分区可以位于不同的文件组上,这样可以提高并发性能。分区方案就是用来指定,根据分区函数划分的若干个分区,分别位于哪些文件组上。我使用的测试环境,数据库test只有默认的PRIMARY文件组,所以就将分区表Table1的全部分区都放在PRIMARY上了。实际上,普通表也是有分区的,只不过只有一个。你用普通表Table2和Table1中的某一个分区进行交换,实际上只是把逻辑上的所属关系改变了,原来Table2的分区1“挂接”到了Table1上,而Table1的分区5(假设)“挂接”到了Table2上。但是交换完,两个分区的partition id是不变的,物理上的存储情况也不会改变。这也是为什么交换分区能在很短的时间内完成。另外,刚看了一下,昨天写的代码还是有点问题,月份错位了。SQL Server的分区概念比较复杂(相对于Oracle来说),分区方案有边界值靠左还是靠右的问题,特别容易搞错,呵呵。分区表不多的话,还是建议程序负责检查提醒,DBA手工来维护。你要是用程序来实现的话,一定要反复好好测试,这个要是错了事儿就大了~ 正确的查询语句如下:SELECT
           CASE pf.boundary_value_on_right
               WHEN 0 THEN p.partition_number
               ELSE        p.partition_number - 1
           END
    FROM      sys.objects                obj
    JOIN      sys.indexes                ind ON obj.object_id      = ind.object_id
    JOIN      sys.partition_schemes      ps  ON ind.data_space_id  = ps.data_space_id
    JOIN      sys.partition_functions    pf  ON ps.function_id     = pf.function_id
    JOIN      sys.partitions             p   ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
    LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
    WHERE obj.name  = 'Table1'
    AND   prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))