[测试相关] 创建主从表测试数据

sys@DEMO> column mname format a30;
sys@DEMO> column did format a30;
sys@DEMO> column dname format a30;

sys@DEMO> create table master (mid number(38),mname varchar2(30),constraint pk_master primary key (mid)) tablespace users;

Table created.

sys@DEMO> desc master
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 MID                                                                                 NOT NULL NUMBER(38)
 MNAME                                                                                        VARCHAR2(30)

 sys@DEMO> create table detail (mid number(38),did varchar2(30) constraint uniq_detail unique,dname varchar2(50),constraint pk_detail primary key (mid,did),constraint fk_detail foreign key (mid) references master(mid)) tablespace users;

Table created.

20:37:56 sys@DEMO> desc detail
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 MID                                                                                 NOT NULL NUMBER(38)
 DID                                                                                 NOT NULL VARCHAR2(30)
 DNAME                                                                                        VARCHAR2(30)

sys@DEMO> create table history (did varchar2(30),ym number(6),cnt number(38),constraint fk_history foreign key (did) references detail(did)) tablespace users;

Table created.

sys@XE> desc history;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 DID                                                                                          VARCHAR2(30)
 YM                                                                                           NUMBER(6)
 CNT                                                                                          NUMBER(38)

sys@DEMO>
begin
for i in 1 .. 10000
  loop
    insert /*+ append */ into master (mid,mname) values (i,i || '_name');
  end loop;
  commit;
end;
/

PL/SQL procedure successfully completed.

sys@DEMO>
begin
for i in 1 .. 10000
  loop
      for j in 1 .. 100
          loop
              insert /*+ append */ into detail (mid,did,dname) values (i,i || '_' || j,i || '_' || j || '_name');
        end loop;
  end loop;
  commit;
end;
/

PL/SQL procedure successfully completed.

sys@DEMO>
begin
for i in 1 .. 10000
    loop
        for j in 1 .. 100
            loop
                for y in 2011 .. 2011
                    loop
                        for m in 1 .. 12
                            loop
                                insert /*+ append */ into history (did,ym,cnt) values (i || '_' || j, y || lpad(m , 2 , 0),round(dbms_random.value*100));
                            end loop;
                    end loop;
            end loop;
            commit;
    end loop;
end;
/

PL/SQL procedure successfully completed.

sys@DEMO> create index idx_history on history(did,ym) tablespace users nologging;

Index created.

sys@DEMO>
begin
    dbms_stats.gather_table_stats(user,'MASTER',cascade=>true);
    dbms_stats.gather_table_stats(user,'DETAIL',cascade=>true);
    dbms_stats.gather_table_stats(user,'HISTORY',cascade=>true);
end;
/

PL/SQL procedure successfully completed.