-- svc : 1,000¸¸°Ç(active_yn = 'Y' ±âÁØ) Àüü 2000¸¸°Ç
-- svc_hist : 10,000¸¸°Ç
-- acct_hist : 3,000¸¸°Ç
-- cust_hist : 5,000¸¸°Ç

--DROP TABLE CUST_HIST PURGE;

CREATE TABLE CUST_HIST NOLOGGING AS    --°í°´ÀÌ·Â
SELECT A.CUST_NO, B.* 
  FROM ( SELECT LEVEL AS  CUST_NO
          FROM DUAL
          CONNECT BY LEVEL <= 833) A,
       ( SELECT SYSDATE - level AS start_dt, --½ÃÀÛÀϽà 
                'Á¾·áÀÏÀÚÀÇ Çʿ伺 Å×½ºÆ®' as txt 
           FROM DUAL 
        CONNECT BY LEVEL <= 60000) B;
        
--ALTER TABLE CUST_HIST ADD CONSTRAINT PK_CUST_HIST PRIMARY KEY (CUST_NO, START_DT) USING INDEX NOLOGGING;        
        
--DROP TABLE CUST_HIST1 PURGE;        
        
CREATE TABLE CUST_HIST1 NOLOGGING AS    --°í°´ÀÌ·Â
SELECT A.CUST_NO, B.* 
  FROM ( SELECT LEVEL AS  CUST_NO
          FROM DUAL
          CONNECT BY LEVEL <= 833) A,
       ( SELECT SYSDATE - level AS start_dt, --½ÃÀÛÀϽà 
                SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --Á¾·áÀϽà 
                'Á¾·áÀÏÀÚÀÇ Çʿ伺 Å×½ºÆ®' as txt 
           FROM DUAL 
        CONNECT BY LEVEL <= 60000) B;       
        
--DROP TABLE ACCT_HIST PURGE;
                
CREATE TABLE ACCT_HIST NOLOGGING AS     -- °èÁ¤ÀÌ·Â
SELECT A.*, B.* 
  FROM ( SELECT LEVEL AS  ACCT_NO
          FROM DUAL
          CONNECT BY LEVEL <= 500) A,
       ( SELECT SYSDATE - level AS start_dt, --½ÃÀÛÀϽà  
                'Á¾·áÀÏÀÚÀÇ Çʿ伺 Å×½ºÆ®' as txt 
           FROM DUAL 
        CONNECT BY LEVEL <= 60000) B ;
        
--ALTER TABLE ACCT_HIST ADD CONSTRAINT PK_ACCT_HIST PRIMARY KEY (ACCT_NO, START_DT) USING INDEX NOLOGGING;          
        
--DROP TABLE ACCT_HIST1 PURGE;         
        
CREATE TABLE ACCT_HIST1 NOLOGGING AS     -- °èÁ¤ÀÌ·Â
SELECT A.*, B.* 
  FROM ( SELECT LEVEL AS  ACCT_NO
          FROM DUAL
          CONNECT BY LEVEL <= 500) A,
       ( SELECT SYSDATE - level AS start_dt, --½ÃÀÛÀϽà 
                SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --Á¾·áÀϽà 
                'Á¾·áÀÏÀÚÀÇ Çʿ伺 Å×½ºÆ®' as txt 
           FROM DUAL 
        CONNECT BY LEVEL <= 60000) B ;  
        
--ALTER TABLE ACCT_HIST1 ADD CONSTRAINT PK_ACCT_HIST PRIMARY KEY (ACCT_NO, END_DT, START_DT) USING INDEX NOLOGGING;              
        
--DROP TABLE SVC PURGE;
        
CREATE TABLE SVC NOLOGGING AS         --¼­ºñ½º
SELECT ROWNUM AS SVC_NO,
       A.*
  FROM ( SELECT TRUNC(dbms_random.value(1,834)) AS CUST_NO,
                TRUNC(dbms_random.value(1,501))  AS ACCT_NO, 
                SYSDATE - level + 1 - 0.5 AS SVC_DATE, --¼­ºñ½º °¡ÀÔÀϽÃ
                MOD(LEVEL, 2) AS ACTIVE_YN,                
                'Á¾·áÀÏÀÚÀÇ Çʿ伺 Å×½ºÆ®' as txt 
           FROM DUAL CONNECT BY LEVEL <= 60000) A,
       ( SELECT LEVEL AS LV
           FROM DUAL
        CONNECT BY LEVEL <= 333 ) B; 

--ALTER TABLE SVC ADD CONSTRAINT PK_SVC PRIMARY KEY (SVC_NO) USING INDEX NOLOGGING; 

--DROP TABLE SVC_HIST PURGE;

CREATE TABLE SVC_HIST NOLOGGING AS   --¼­ºñ½ºÀÌ·Â
SELECT A.svc_no, B.* 
  FROM ( SELECT svc_no
           FROM SVC) A,
       ( SELECT SYSDATE - level AS start_dt, --½ÃÀÛÀϽà 
                'Á¾·áÀÏÀÚÀÇ Çʿ伺 Å×½ºÆ®' as txt 
           FROM DUAL 
        CONNECT BY LEVEL <= 5) B;   
       
--ALTER TABLE SVC_HIST ADD CONSTRAINT PK_SVC_HIST PRIMARY KEY (SVC_NO, START_DT) USING INDEX NOLOGGING;        

--DROP TABLE SVC_HIST1 PURGE;        
        
CREATE TABLE SVC_HIST1 NOLOGGING AS   --¼­ºñ½ºÀÌ·Â
SELECT A.svc_no, B.* 
  FROM ( SELECT svc_no
           FROM SVC) A,
       ( SELECT SYSDATE - level AS start_dt, --½ÃÀÛÀϽà  
                SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --Á¾·áÀϽà 
                'Á¾·áÀÏÀÚÀÇ Çʿ伺 Å×½ºÆ®' as txt 
           FROM DUAL 
        CONNECT BY LEVEL <= 5) B;              

begin 
    dbms_stats.gather_table_stats(user, 'SVC_HIST', ESTIMATE_PERCENT => 5);
    dbms_stats.gather_table_stats(user, 'CUST_HIST', ESTIMATE_PERCENT => 10);
    dbms_stats.gather_table_stats(user, 'ACCT_HIST', ESTIMATE_PERCENT => 10);
    dbms_stats.gather_table_stats(user, 'SVC_HIST1', ESTIMATE_PERCENT => 5);
    dbms_stats.gather_table_stats(user, 'CUST_HIST1', ESTIMATE_PERCENT => 10);
    dbms_stats.gather_table_stats(user, 'ACCT_HIST1', ESTIMATE_PERCENT => 10);    
    dbms_stats.gather_table_stats(user, 'SVC', ESTIMATE_PERCENT => 10);
end;