none
关于多张大表(按年份)历史数据查询的问题??? RRS feed

  • 问题

  • 关于大表历史数据查询的问题

    注:

    DB为原Oracle版本转换到SQL Server版本

    应用程序重新开发,原来C/S架构,现改成B/S架构


    情况说明
    由于每年数据量很大(每年在亿级别),并且每年以50-80%的速度在增长,
    所以把每年的数据按放在一张表内,类似testdata_2011,testdata_2012
    到了年末的时候,再手动增加下一年的表


    问题
    目前系统只支持查询近两年的数据,查询近两年数据是用视图来实现的
    比如
    create view dbo.query as
    select * form dbo.testdata_2012
    union all
    select * form dbo.testdata_2011
    但如果用户想要查询两年之前的数据,有什么比较好的办法呢??

    目前我想到的办法有
    方法一:
    创建另一个专门查询历史数据的架构,比如history
    再建这样的视图
    create view history.query as
    select * form dbo.testdata_2012
    union all
    select * form dbo.testdata_2010
    union all
    select * form dbo.testdata_2009
    union all
    select * form dbo.testdata_2008
    union all
    select * form dbo.testdata_2007

    缺点:
    1. 所有应用程序中需要增加架构名称参数来确认数据库对象,是历史数据查询还是近两年数据查询
    所有存储过程中也需要增加这个参数来确认要执行哪个架构下面的表
    2. 还有一点就是,动态的东西太多,很有可能导致没法重复执行计划导致效率性能低下

    方法二:
    创建另外一个数据库,用链接服务器来查询历史数据。在这个新的数据库中创建名称跟原来一样的对象,但关联历史数据的表。应用程序只需要根据情况来改变连接数据库的字符串即可,
    create view dbo.query as
    select * form 原来数据库.dbo.testdata_2012
    union all
    select * form 原来数据库.dbo.testdata_2010
    union all
    select * form 原来数据库.dbo.testdata_2009
    union all
    select * form 原来数据库.dbo.testdata_2008
    union all
    select * form 原来数据库.dbo.testdata_2007


    注:
    原来Oracle中是用不同的用户来实现的

    感觉这两种方法都非常的不好,不知道有没有更好的办法,业界类似的问题是如何解决的,

    在此小弟先谢过各位了

    2012年4月25日 2:42

答案

  • You may look at table partition. Moving big table to another schema is not easy work, partition switching costs much less.
    2012年4月25日 3:28
  • 参考下面这个测试示例

    USE tempdb;
    GO
    
    -- 针对两个测试用户的 Schema
    CREATE SCHEMA sch1;
    GO
    CREATE SCHEMA sch2;
    GO
    
    -- 针对两个测试用户的表
    CREATE TABLE sch1.tb(
    	t1 int
    );
    CREATE TABLE sch2.tb(
    	t2 int
    );
    GO
    
    
    -- 测试用户1
    CREATE USER u1
    	WITHOUT LOGIN
    	WITH DEFAULT_SCHEMA = sch1
    ;
    GRANT SELECT ON SCHEMA::sch1
    	TO u1
    ;
    GO
    
    -- 测试用户2
    CREATE USER u2
    	WITHOUT LOGIN
    	WITH DEFAULT_SCHEMA = sch2
    ;
    GRANT SELECT ON SCHEMA::sch2
    	TO u2
    ;
    GO
    
    -- 查询测试
    EXECUTE AS USER = 'u1';
    SELECT * FROM tb;
    REVERT;
    GO
    
    EXECUTE AS USER = 'u2';
    SELECT * FROM tb;
    REVERT;
    go
    
    -- 删除测试对象
    DROP USER u1;
    DROP USER u2;
    DROP TABLE sch1.tb, sch2.tb;
    DROP SCHEMA sch1;
    DROP SCHEMA sch2
    
    

    2012年4月25日 4:54

全部回复

  • 你也可以用 Schema + 用户来实现

    为用户指定不同的默认 Schema , 在查询的时候, 不指定 Schema, 从而变成直接去引用当前用户的默认 Schema 对应的那个对象

    2012年4月25日 2:49
  • 常规的方法还是分 HistoryQuery 和 CurentQuery 这样两个视图, 在程序中去控制是历史查询还是当前查询, 写在存储过程中的话, 一艉也是分历史存储过程和当前存储过程两套
    2012年4月25日 2:50
  • 邹建大哥的意思是

    用户A:查询近两年数据,默认为架构Sch_A,实际的查询视图为Sch_A.v_Query

    用户B:查询历史数据,默认为架构Sch_B,,实际的查询视图为Sch_B.v_Query

    用不同的数据库用户登录进来后,只需要查询Query即可,由DB自己根据登录的用户名

    去判断是Sch_A.v_Query还是Sch_B.v_Query

    是这个意思吗??

    用户是指数据库的登录名吗??

    2012年4月25日 3:00
  • You may look at table partition. Moving big table to another schema is not easy work, partition switching costs much less.
    2012年4月25日 3:28
  • 参考下面这个测试示例

    USE tempdb;
    GO
    
    -- 针对两个测试用户的 Schema
    CREATE SCHEMA sch1;
    GO
    CREATE SCHEMA sch2;
    GO
    
    -- 针对两个测试用户的表
    CREATE TABLE sch1.tb(
    	t1 int
    );
    CREATE TABLE sch2.tb(
    	t2 int
    );
    GO
    
    
    -- 测试用户1
    CREATE USER u1
    	WITHOUT LOGIN
    	WITH DEFAULT_SCHEMA = sch1
    ;
    GRANT SELECT ON SCHEMA::sch1
    	TO u1
    ;
    GO
    
    -- 测试用户2
    CREATE USER u2
    	WITHOUT LOGIN
    	WITH DEFAULT_SCHEMA = sch2
    ;
    GRANT SELECT ON SCHEMA::sch2
    	TO u2
    ;
    GO
    
    -- 查询测试
    EXECUTE AS USER = 'u1';
    SELECT * FROM tb;
    REVERT;
    GO
    
    EXECUTE AS USER = 'u2';
    SELECT * FROM tb;
    REVERT;
    go
    
    -- 删除测试对象
    DROP USER u1;
    DROP USER u2;
    DROP TABLE sch1.tb, sch2.tb;
    DROP SCHEMA sch1;
    DROP SCHEMA sch2
    
    

    2012年4月25日 4:54
  • 通过示例可以看到, 虽然都是执行 SELECT * FROM tb, 但是两个用户访问的是不同的表

    另外, 在测试示例中, 我直接创建的是无登录的数据库用户, 实际应用中, 应该是换成和 Login 对应的 DB User

    2012年4月25日 4:55
  • To rmiao

    你的意思是把所有的数据都放到一个表里,再把这个表进行分区吗??

    我现在是把每年的数据进行按月分进行分区

    2012年4月25日 12:30
  • 回去试一把先

    先谢谢各位了

    2012年4月25日 12:33
  • To rmiao

    你的意思是把所有的数据都放到一个表里,再把这个表进行分区吗??

    我现在是把每年的数据进行按月分进行分区

    That's right, much easier to handle.
    2012年4月25日 13:54