ORACLE/TIBERO SQL 튜닝(최적화) 학습을 위한 테스트 데이터 만들기

• SQL 튜닝 학습을 하고 싶은데, ORACLE 데모 데이터 외의 데이터로 테스트를 하고 싶은 경우 활용하면 좋습니다.
• 자체적으로 고민해서 만든 모델이며 정규화, SQL 튜닝 등등 다양한 케이스로 활용할 수 있습니다.

---

DDL

-- 20만
CREATE TABLE user_info(
	uuid number(38),
	user_id varchar(400),
	password varchar(4000),
	email varchar(1000),
	oauth_type varchar(1000),
	singup_date date
);

-- 100만
CREATE TABLE user_post_info(
	uuid varchar(5000),
	user_id varchar(1000),
	post_id number(38),
	post_path varchar(5000),
	post_view number(38),
	post_like number(38),
	post_dislike number(38),
	post_createdate date
);

-- 150만
CREATE TABLE user_comment_info(
	uuid number(38),
	user_id varchar(1000),
	post_id number(38),
	comment_id number(38),
	comment_content varchar(4000),
	comment_like number(38),
	comment_dislike number(38),
	comment_createdate date
);

• user_info: 20만개, user_post_info: 100만개, user_comment_info: 150만개
• 건수는 향후 조정할 수 있으며 일단 테스트 데이터 작성 건수는 위와 같습니다.
• VARCHAR를 VARCHAR2로 변경하면 오라클에서도 사용 가능합니다.

USER_INFO

-- PL/SQL 스크립트로 랜덤 데이터 삽입
DECLARE
    max_records CONSTANT NUMBER := 200000; -- 생성할 데이터 건수
    commit_interval CONSTANT NUMBER := 10000; -- 커밋 주기
    min_year CONSTANT NUMBER := 2021;     -- 가입 날짜 최소 연도
    max_year CONSTANT NUMBER := 2023;     -- 가입 날짜 최대 연도
    date_range CONSTANT NUMBER := max_year - min_year + 1; -- 가입 날짜 범위 (연도 차이)
    base_date CONSTANT DATE := TO_DATE('2021-01-01', 'YYYY-MM-DD'); -- 시작 날짜

    -- 랜덤 문자열 생성 함수
    FUNCTION random_string(p_length IN NUMBER) RETURN VARCHAR2 IS
        random_str VARCHAR2(4000);
    BEGIN
        SELECT DBMS_RANDOM.STRING('A', p_length) INTO random_str FROM DUAL;
        RETURN random_str;
    END random_string;

    -- 랜덤 소셜 타입 생성 함수
    FUNCTION random_oauth_type RETURN VARCHAR2 IS
        oauth_types CONSTANT sys.odcivarchar2list := sys.odcivarchar2list('Facebook', 'Twitter', 'Google', 'Kakao', 'Naver');
    BEGIN
        RETURN oauth_types(DBMS_RANDOM.VALUE(1, oauth_types.COUNT));
    END random_oauth_type;

    -- 이메일 도메인 생성 함수
    FUNCTION generate_email_domain(p_oauth_type IN VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        CASE p_oauth_type
            WHEN 'Facebook' THEN
                RETURN '@facebook.com';
            WHEN 'Twitter' THEN
                RETURN '@twitter.com';
            WHEN 'Google' THEN
                RETURN '@google.com';
            WHEN 'Kakao' THEN
                RETURN '@kakao.com';
            WHEN 'Naver' THEN
                RETURN '@naver.com';
            ELSE
                RETURN '@example.com';
        END CASE;
    END generate_email_domain;

    -- 랜덤 날짜 생성 함수
    FUNCTION random_date RETURN DATE IS
        random_days NUMBER;
    BEGIN
        random_days := DBMS_RANDOM.VALUE(0, date_range * 365);
        RETURN base_date + random_days;
    END random_date;
BEGIN
    FOR i IN 1..max_records LOOP
        DECLARE
            v_oauth_type VARCHAR2(1000);
            v_email_domain VARCHAR2(1000);
        BEGIN
            v_oauth_type := random_oauth_type;
            v_email_domain := generate_email_domain(v_oauth_type);
        
            INSERT INTO user_info (uuid, user_id, password, email, oauth_type, singup_date)
            VALUES (
                i,
                random_string(10),  -- user_id는 10자리 랜덤 문자열로 생성
                random_string(20),  -- password는 20자리 랜덤 문자열로 생성
                random_string(15) || v_email_domain, -- 랜덤 이메일 생성
                v_oauth_type,  -- 랜덤 소셜 타입 생성
                random_date         -- 가입 날짜는 랜덤으로 생성
            );
        
            -- 주기적으로 COMMIT을 수행하여 성능을 개선
            IF MOD(i, commit_interval) = 0 THEN
                COMMIT;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                -- 예외 처리
                DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
                ROLLBACK; -- 롤백 처리
        END;
    END LOOP;
    
    COMMIT; -- 마지막으로 COMMIT 수행
EXCEPTION
    WHEN OTHERS THEN
        -- 예외 처리
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
        ROLLBACK; -- 롤백 처리
END;
/

• 프로시저로 테스트 데이터를 생성합니다.

USER_POST_INFO

create index idx_user_info_uuid on user_info(uuid); -- uuid 칼럼에 INDEX 생성이 이루어져야만 데이터 생성이 빨리 됩니다.
create sequence seq_user_post_info_uuid; -- SEQUENCE를 통해 uuid 순번을 생성 합니다.

INSERT INTO user_post_info(uuid,user_id,post_id,post_path,post_view,post_like,post_dislike,post_createdate)
 SELECT
  seq_user_post_info_uuid.nextval uuid,
  (SELECT user_id FROM user_info WHERE uuid =b.rand) user_id,
  seq_user_post_info_uuid.nextval post_id,
  'path:/contents/post/'||seq_user_post_info_uuid.nextval post_path,
  CEIL(dbms_random.VALUE(0,50000)) post_view,
  CEIL(dbms_random.VALUE(0,4000)) post_like, 
  CEIL(dbms_random.VALUE(0,3999)) post_dislike,
  (SELECT singup_date + b.trand FROM user_info WHERE uuid =b.rand) post_createdate
 FROM(
	SELECT CEIL(dbms_random.VALUE(1,200000)) rand, --user_info 테이블 건수만큼 지정
	CEIL(dbms_random.VALUE(1,730)) trand FROM dual CONNECT BY LEVEL <=1000000 -- 원하는 user_post_info 테이블 건수
) b;

• "CEIL(dbms_random.VALUE(1,200000))" user_info 테이블과 연결을 위해 동일하게 건수 설정

USER_COMMENT_INFO

create index idx_user_post_info_uuid on user_post_info(uuid); -- uuid 칼럼에 INDEX 생성이 이루어져야만 데이터 생성이 빨리 됩니다.
create index idx_user_post_info_post_id on user_post_info(post_id);
create sequence seq_user_comment_info_uuid; -- SEQUENCE를 통해 uuid 순번을 생성 합니다.

INSERT INTO user_comment_info (uuid,user_id,post_id,comment_id,comment_content,comment_like,comment_dislike,comment_createdate)
SELECT
 seq_user_comment_info_uuid.nextval uuid,
 (SELECT user_id FROM user_info WHERE uuid =b.rand) user_id,
 (SELECT post_id FROM user_post_info WHERE post_id = b.prand) post_id,
 seq_user_comment_info_uuid.nextval comment_id,
 dbms_random.string('p',b.trand) comment_content,	
 dbms_random.VALUE(0,1000) comment_like,
 dbms_random.VALUE(0,1001) comment_dislike,
 (SELECT POST_CREATEDATE + b.trand AS post_createdate FROM user_post_info WHERE post_id = b.prand) comment_createdate
FROM
 (
 	SELECT 
 		CEIL(dbms_random.value(1,200000)) rand, -- user_info 테이블 건수와 동일
 		CEIL(dbms_random.value(1,765)) trand,
 		CEIL(dbms_random.value(1,1000000)) prand  -- user_post_info 테이블 건수와 동일
 	FROM
    	dual CONNECT BY LEVEL <=1500000
  ) b;

• "CEIL(dbms_random.value(1,200000)) rand" user_info 테이블과 연결을 위해 user_info 건수와 동일하게 설정
• "CEIL(dbms_random.value(1,1000000)) prand" user_post_info 테이블과 연결을 위해 user_post_info 건수와 동일하게 설정

---

---