ORACLE HINT/Tuning 실습을 위한 테이블, 데이터 생성하기

본 강좌에서 앞으로 사용될 테이블 및 데이터 입니다.

create table myemp1

(empno number not null primary key,

 ename varchar2(100),

 deptno number,

 addr   varchar2(100),

 sal    number

 )

 

-- 실습을 위해 myemp1을 1000만건 만들자.

DECLARE

          v_c NUMBER := 1;

BEGIN

 

          WHILE (v_c <= 10000000) LOOP

                insert into myemp1 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c, 2000000));

                v_c := v_c + 1;

                insert into myemp1 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c, 2000000));

                v_c := v_c + 1;

                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c, 2000000));

                v_c := v_c + 1;

                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c, 2000000));

                v_c := v_c + 1;

          END LOOP;

          commit;

END;

create table myemp1_old

(empno number not null primary key,

 ename varchar2(100),

 deptno number,

 addr   varchar2(100),

 sal    number

 )

 

 -- 100만건 입력

 insert into myemp1_old select * from myemp1 where rownum < 1000000

 commit 

 

 

create table mydept1

 (deptno number,

  dname  varchar2(100)

)

 

 

insert into mydept1 values (0, '인사팀');

 insert into mydept1 values (1, '회계팀');

 insert into mydept1 values (2, '영업팀');

 insert into mydept1 values (3, '기획팀');

 insert into mydept1 values (4, '교육팀');

 

 

analyze table myemp1 compute statistics

analyze table mydept1 compute statistics

analyze table mydept1_old compute statistics

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecOrccleTun&wr_id=53