ORACLE 数据库设计

Question

四张源数据表（每张表的字段不一样的），每张表有30万数据；一张检核表（包含25条检核规则（不是每个表都要满足这25条检核规则），以及四个数据来源字段：1代表检核，0代表不需要检核，错误号ID（每条规则对应着一个错误号）),还有一张检核结果表，将源数据的信息以及错误号ID插入到检核结果表中。
自己的设计思路是：用游标取出每条检核规则，然后针对每个表进行检核。虽然可行，但运行太慢了。oracle  ; 数据库设计;优化方案

Answer 1

用MERGE 符合检核规则的就插入到检核结果表

Answer 2

CREATE OR REPLACE PROCEDURE test_efrule1(p_sysdate in varchar2,p_data_source_cd in int) is
  V_SYSDATE DATE := TO_DATE(p_sysdate,'YYYY-MM-DD');
  V_INS_TXT VARCHAR2(3000);
  V_SEL_TXT VARCHAR2(3000);
  FUNCTION F_FILO_SCOM(F_TAB VARCHAR2)
    RETURN VARCHAR2 IS
    BEGIN
      IF F_TAB IN('PD_PP_FIXED','PD_PP_FLOAT') THEN
        RETURN ',OVERDUE_FLAG,OVERDUE_TERM,EARLY_REPAY,OVERDUE_TERM_UNIT';
      END IF;
      RETURN '';
    END F_FILO_SCOM;
  FUNCTION F_FILONIRS_SCOM(F_TAB VARCHAR2)
    RETURN VARCHAR2 IS
    BEGIN
      IF F_TAB IN('PD_PP_FIXED','PD_PP_FLOAT','PD_PP_NIRS') THEN
        RETURN ',ORG_TERM_UNIT,MAT_DATE,ORG_TERM';
      END IF;
      RETURN '';
    END F_FILONIRS_SCOM;
   FUNCTION F_FILONDD_SCOM(F_TAB VARCHAR2)
     RETURN VARCHAR2 IS
     BEGIN
       IF F_TAB IN('PD_PP_FIXED','PD_PP_FLOAT','PD_PP_NDD') THEN
         RETURN ',LAST_PAY_DATE,BUSINESS_LEVEL_CD,PAY_FREQ_UNIT,LAST_REPRICE_BAL,CHANNEL,PAY_BAL,'
             || 'PAY_FREQ,NEXT_PAY_DATE,INT_FLOAT_TYPE_CD,FLOAT_PROPORTION,BASE_FLT_VALUE,LAST_REPRICE_DATE,'
             || 'REPRICE_TYPE_CD,NEXT_REPRICE_DATE,PUBLISHER_CD,REPRICE_FREQ,REPRICE_FREQ_UNIT'
             ;
       END IF;
       RETURN '';
     END F_FILONDD_SCOM;
begin
   DELETE BP_INVALID_DATA T WHERE T.DATA_DATE = v_sysdate;
   COMMIT;
FOR J IN (SELECT TRIM(DATA_SYSTEM_SOURCE1) t1,TRIM(DATA_SYSTEM_SOURCE2) t2,TRIM(DATA_SYSTEM_SOURCE3) t3,TRIM(DATA_SYSTEM_SOURCE4) t4,CHECK_SQL,ERROR_ID
            FROM CP_VALID_CHECK_PARA 
           ) 
    LOOP 
      CASE J.t1 WHEN 1 THEN 
        V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILO_SCOM('PD_PP_FIXED') || F_FILONIRS_SCOM('PD_PP_FIXED') || F_FILONDD_SCOM('PD_PP_FIXED')
           || ')'
           ;
      V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILO_SCOM('PD_PP_FIXED') || F_FILONIRS_SCOM('PD_PP_FIXED') || F_FILONDD_SCOM('PD_PP_FIXED')
           || ' FROM PD_PP_FIXED'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
           EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      else null ;
           END CASE;
    CASE J.t2 WHEN 1 THEN
          V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILO_SCOM('PD_PP_FLOAT') || F_FILONIRS_SCOM('PD_PP_FLOAT') || F_FILONDD_SCOM('PD_PP_FLOAT')
           || ')'
           ;
         V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILO_SCOM('PD_PP_FLOAT') || F_FILONIRS_SCOM('PD_PP_FLOAT') || F_FILONDD_SCOM('PD_PP_FLOAT')
           || ' FROM PD_PP_FLOAT'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
           EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      ELSE Null ;
        END CASE;
        CASE J.t3 when 1 THEN
           V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILO_SCOM('PD_PP_NDD') || F_FILONIRS_SCOM('PD_PP_NDD') || F_FILONDD_SCOM('PD_PP_NDD')
           || ')'
           ;
         V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILO_SCOM('PD_PP_NDD') || F_FILONIRS_SCOM('PD_PP_NDD') || F_FILONDD_SCOM('PD_PP_NDD')
           || ' FROM PD_PP_NDD'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
      EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      ELSE null ;
        END CASE;
        CASE J.t4 WHEN 1 THEN
          V_INS_TXT := 'INSERT INTO BP_INVALID_DATA'
           || '(PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,ERROR_ID'
           || F_FILO_SCOM('PD_PP_NIRS') || F_FILONIRS_SCOM('PD_PP_NIRS') || F_FILONDD_SCOM('PD_PP_NIRS')
           || ')'
           ;
         V_SEL_TXT := 'SELECT PROJECT_ID,ACCOUNT_NUM,DATA_DATE,ORG_ID,INDUSTRY_TYPE_CD,CUST_ID,PRODUCT_ID,SUBJECT_ID,SUBJECT_TYPE_CD,'
           || 'DATA_SOURCE_CD,CURRENCY_ID,CUR_BAL,'
           || 'ORG_BAL,ORG_DATE,INT_RATE,ACCRU_BASIS_CD,INT_RATE_CD,PMT_FREQ,PMT_FREQ_UNIT,RATE_TYPE_CD,PAY_TYPE_CD,'
           || 'CUST_CATEGORY_CD,BUSINESS_NUM,DATA_SYSTEM_SOURCE,''' || J.ERROR_ID  || ''''
           || F_FILO_SCOM('PD_PP_NIRS') || F_FILONIRS_SCOM('PD_PP_NIRS') || F_FILONDD_SCOM('PD_PP_NIRS')
           || ' FROM PD_PP_NIRS'
           || ' ' || J.CHECK_SQL
           || ' AND DATA_SOURCE_CD = ''' || P_DATA_SOURCE_CD || ''''
           ;
      EXECUTE IMMEDIATE V_INS_TXT || V_SEL_TXT;
      COMMIT;
      ELSE Null ;
        END CASE;
    END LOOP;
end;