Oracle如何按月分表

以你之姓@ 2022-08-05 01:15 190阅读 0赞

当数据库的一张表的数据达到几百万时，对该表的查询所花的时间会变的很多。如果有联合查询的话，将会更加耗费资源和时间。如何解决这一问题？答案就是创建数据库分表，定期（如每月一次）将没用的数据挪到分表中，保证主表的数据量不会非常庞大。这样在查询时，所花费的时间就在合理范围内了。

那么如何创建定期创建数据库分表？我的做法是在ORACLE中创建一个存储过程，然后用Java定时器定期触发它。下面就是创建存储过程的SQL语句。

该表的总体业务流程如下：

1.  获得上月时间，并以“表名\_yyMM”的格式得到新表的表名；
2.  创建新表，表名是刚刚生成的表名，表结构与主表表结构一模一样；
3.  用INSERT INTO table (SELECT xx FROM yy WHERE z=z)的SQL语句，将上月数据从主表中复制到分表中；
4.  将已经复制出去的数据从主表中删除；

一些注意事项：

1.  该存储过程是按月分表的，你可以按自己的间隔进行分表；
2.  return\_code、return\_msg是用来将错误信息、错误码返回给调用者的，方便在正常系统运行时，记录错误原因的；
3.  EXCEPTION就是用来处理异常时，记录错误原因的。这部分代码我暂未将所有可能性纳入进来，今后有待扩展空间；
4.  本例列出的SQL语句，在我这里是能成功执行，并且也可以实现预计的业务逻辑。但是在粘贴到本文中时，为了避免一些不必要的麻烦，我把变量命名和SQL语句都进行了简化和修改，所以并不能保证修改以后可以直接运行通过。而且借鉴本例的读者需要根据自己的实际情况编写建表语句、INSERT语句和DELETE语句。

CREATE OR REPLACE
    PROCEDURE PROC_CREATE_SUB_TABLE (
       return_code   OUT VARCHAR2,
       return_msg   OUT VARCHAR2)
    IS
      err_index            NUMBER;
        table_name              VARCHAR2(50);
        create_table_cursor     NUMBER(10);
        create_table_sql         VARCHAR2(1000);
      insert_data_sql      VARCHAR2(1000);
      delete_data_sql      VARCHAR2(1000);
    BEGIN
      return_msg := '过程[PROC_CREATE_SUB_TABLE]成功!';
      --return_code为错误编码。1代表成功，0代表失败。
      return_code := '1';
    
      err_index := 1;
    
      --生成分表的表名。在本例中，主表表名为FATHER_TABLE。
      SELECT 'FATHER_TABLE_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYMM') INTO table_name FROM DUAL;
    
      create_table_cursor := DBMS_SQL.OPEN_CURSOR;--打开游标
      --拼出创建表的SQL语句，并执行。
      create_table_sql := 'create table ' || table_name || ' (' ||
                          'T_ID NUMERIC(10) PRIMARY KEY, ' ||
                          'T_NAME VARCHAR2(50) not null)';
        DBMS_SQL.PARSE(create_table_cursor, create_table_sql, DBMS_SQL.V7);
        DBMS_SQL.CLOSE_CURSOR(create_table_cursor);
    
      err_index := 2;
    
        --将FATHER_TABLE表中取上月记录 添加到新创建的分表中。
      insert_data_sql := 'INSERT INTO ' || table_name || ' (SELECT * ' ||
                         'FROM FATHER_TABLE WHERE 条件)';
      EXECUTE IMMEDIATE insert_data_sql;
    
      err_index := 3;
    
      --删除FATHER_TABLE表中时间在上个月范围内的所有数据
      delete_data_sql := 'DELETE FROM FATHER_TABLE WHERE 条件';
      EXECUTE IMMEDIATE delete_data_sql;
    
      err_index := 4;
    
        COMMIT;
    
      EXCEPTION
        WHEN OTHERS THEN
          return_msg := '过程[PROC_CREATE_SUB_TABLE]出错,' ||'第['||err_index||']块语句出错';
          return_code := '0';
          ROLLBACK;
    
    END PROC_CREATE_SUB_TABLE;

在Java代码中，是按以下方法来调用存储过程的。为了不必要的麻烦，一些import语句和类声明等，我都省略了。只留下最主要的部分。代码中的getTemplate().getConnection()方法是我们系统中自己封装的，你可以根据自己的实际情况写自己的代码，只要返回值的类型是java.sql.Connection就可以了。

import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.SQLException;
    
    ……
    
    public String moveDisposeHisData(){
    		String retCode = new String() , retMsg = new String();
    		try {
    			Connection con = getTemplate().getConnection();
    			String procedure = "{ call PROC_CREATE_SUB_TABLE(?, ?) }";
    			CallableStatement cstmt;
    			//存储过程
    			cstmt = con.prepareCall(procedure);
    			cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);  
    			cstmt.registerOutParameter(2, java.sql.Types.VARCHAR); 
    			// 输出参数（如果是多个的话，可以返回一个ARRAY,即java.sql.Types.ARRAY（后面的类型是可以快捷键点出来的）） 
    			cstmt.executeQuery();
    			retCode = cstmt.getString(1);
    			retMsg = cstmt.getString(2);
    			cstmt.close();
    			con.close();
    			if(retCode.equals("1")){
    				logger.info(retMsg);
    			}else{
    				logger.error(retMsg);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return retMsg;
    	}