251103 TIL

SEQUENCE

• 자동 일련 번호(고유번호)를 생성하는 객체
• 응용 프로그램 코드를 대체
• SEQUENCE 객체를 생성하려면 CREATE SEQUENCE 시스템 권한 필요

SEQUENCE 생성

CREATE SEQUENCE id_seq;

사용자 소유 SEQUENCE 조회

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

SEQUENCE 사용

-- 테스트용 테이블 생성
CREATE TABLE hr.seq_test(id number, name varchar2(30), day timestamp) TABLESPACE users;

-- 데이터 입력
INSERT INTO hr.seq_test(id, name, day) VALUES(id_seq.nextval, 'liam', localtimestamp);
COMMIT;

SELECT * FROM hr.seq_test;

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

• nextval 슈도 컬럼을 통해 시퀀스 번호를 받음
• 처음 시퀀스 번호를 요청할 때 메모리에 CACHE_SIZE만큼 미리 시퀀스 번호들을 생성해둠
  • 매번 시퀀스 번호를 요청할 때마다 생성하려면 느리기 때문

pseudo column(의사컬럼, 가상컬럼)

• 실제 컬럼이 아닌데 컬럼처럼 사용하는 객체
• 시퀀스명.nextval : 현재 사용 가능한 번호를 리턴해주는 가상컬럼
• 시퀀스명.currval : 현재 사용한 번호를 리턴해주는 가상컬럼

SEQUENCE 사용 시 주의할 점

INSERT INTO hr.seq_test(id, name, day) VALUES(id_seq.nextval, 'james', localtimestamp);
SELECT * FROM hr.seq_test;

ROLLBACK;

INSERT INTO hr.seq_test(id, name, day) VALUES(id_seq.nextval, 'emma', localtimestamp);
SELECT * FROM hr.seq_test;

• 한 번 사용한 번호는 재사용 불가
• 결번이 생길 수 있음

SEQUENCE 삭제

• 소유자, DROP ANY SEQUENCE 시스템 권한을 가지고 있는 유저만 삭제 가능

DROP SEQUENCE hr.id_seq;

SEQUENCE 생성

CREATE SEQUENCE hr.id_seq
START WITH 1    -- 시작번호, 기본값 1
MAXVALUE 3      -- 최대값, 지정하지 않으면 기본값 10^27
INCREMENT BY 1  -- 증가분, 기본값 1
NOCYCLE         -- 최대값 도달 시 시퀀스를 계속 생성할지 여부 지정(순환 여부), 기본값 NOCYCLE
NOCACHE;        -- 시퀀스 값을 메모리에 미리 생성해서 사용할지 여부를 지정, 기본값 CACHE 20

INSERT INTO hr.seq_test(id, name, day) VALUES(id_seq.nextval, 'liam', localtimestamp);
SELECT * FROM hr.seq_test;
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

INSERT INTO hr.seq_test(id, name, day) VALUES(id_seq.nextval, 'noah', localtimestamp);
SELECT * FROM hr.seq_test;
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

INSERT INTO hr.seq_test(id, name, day) VALUES(id_seq.nextval, 'emma', localtimestamp);
SELECT * FROM hr.seq_test;
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

-- maxvalue까지 생성했기 때문에 오류 발생
INSERT INTO hr.seq_test(id, name, day) VALUES(id_seq.nextval, 'jack', localtimestamp);
-- ORA-08004: sequence ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
SELECT * FROM hr.seq_test;
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

SEQUENCE 수정

• 소유자, ALTER 객체 권한을 가지고 있는 유저만 수정 가능
• START WITH 절은 제외하고 나머지 절들은 수정 가능(11g)
• 12c 부터는 START WITH 절도 수정 가능

ALTER SEQUENCE hr.id_seq
INCREMENT BY 2
MAXVALUE 100;

SEQUENCE 권한 부여

GRANT SELECT, INSERT, UPDATE, DELETE ON hr.seq_test TO insa;
GRANT SELECT ON hr.id_seq TO insa;
SELECT * FROM user_tab_privs;

• sequence 객체에 대한 select 권한은 시퀀스에 대한 nextval, currval을 수행할 수 있는 권한을 부여

INSA

SELECT * FROM hr.seq_test;
SELECT * FROM all_objects WHERE object_name = 'ID_SEQ';
SELECT * FROM all_sequences WHERE sequence_name = 'ID_SEQ';

• all_obejcts : 접근 가능한 오브젝트 정보
• all_sequences : 접근 가능한 시퀀스 정보

SEQUENCE 권한 회수

REVOKE SELECT ON hr.id_seq FROM insa;

insa

-- 권한 회수 후 실행시 오류 발생
INSERT INTO hr.seq_test(id, name, day) VALUES(hr.id_seq.nextval, 'james', localtimestamp);
-- ORA-00942: table or view does not exist

SYNONYM(동의어)

• 긴 객체 이름을 짧은 이름으로 사용하는 객체
• SYNONYM을 생성하려면 CREATE SYNONYM 시스템 권한 필요

SYNONYM 생성

CREATE SYNONYM ec FOR hr.emp_copy_2025;

SYNONYM으로 조회

SELECT * FROM ec;

사용자 소유 SYNONYM 조회

SELECT * FROM user_synonyms WHERE table_name = 'EMP_COPY_2025';

SYNONYM 권한 부여

GRANT SELECT ON hr.emp_copy_2025 TO insa;

• 테이블 조회 권한 부여하면 됨

insa에서 synonym으로 테이블 조회

SELECT * FROM hr.ec;

• 소유자명.synonym명으로 조회 가능

SYNONYM 삭제

DROP SYNONYM ec;

PUBLIC SYNONYM 생성

• 모든 유저들이 사용할 수 있는 SYNONYM을 생성하려면 CREATE PUBLIC SYNONYM 시스템 권한 필요

sys → hr CREATE PUBLIC SYNOYM 권한 부여

GRANT CREATE PUBLIC SYNONYM TO hr;

PUBLIC SYNONYM 생성

CREATE PUBLIC SYNONYM ec FOR hr.emp_copy_2025;

PUBLIC SYNONYM 조회

SELECT * FROM all_synonyms WHERE synonym_name = 'EC';

• PUBLIC SYNONYM은 user_synonyms에서 조회 불가
• owner가 PUBLIC으로 생성됨

insa에서 public synonym으로 테이블 조회

SELECT * FROM ec;

SYNONYM 권한 회수

REVOKE SELECT ON hr.emp_copy_2025 FROM insa;

• 테이블 조회 권한을 회수하면 PUBLIC SYNONYM이 있더라도 조회 불가

PUBLIC SYNONYM 삭제

• DROP PUBLIC SYNONYM 시스템 권한 필요

sys → hr DROP PUBLIC SYNONYM 권한 부여

GRANT DROP PUBLIC SYNONYM TO hr;

PUBLIC SYNONYM 삭제

DROP PUBLIC SYNONYM ec;

OPTIMIZER

• SQL 문을 수행하기 위한 실행계획 생성

1. DATA ACCESS METHOD
2. JOIN METHOD
3. JOIN 순서

DATA ACCESS METHOD

FULL TABLE SCAN

• 첫 번째 block부터 마지막 block에 있는 행까지 access 하는 방식

SELECT * FROM hr.emp WHERE employee_id = 100;

ROWID SCAN

• 행의 물리적 주소를 가지고 찾는 방식
• 데이터 액세스 방법 중에 가장 빠른 방법

1. by user rowid scan

SELECT * FROM hr.emp WHERE rowid = 'AAAE/uAAEAAAAFTAAA';

SELECT rowid, employee_id FROM hr.emp ORDER BY employee_id;

SELECT rowid, e.*
FROM hr.emp e
WHERE rowid = (SELECT rowid FROM hr.emp WHERE employee_id = 100);

2. by index rowid scan

ROWID

• 가상컬럼, 행의 물리적 주소

SELECT rowid, e.* FROM hr.emp e;

ROWID 구성 요소 : AAAE/uAAEAAAAFTAAA

• AAAE/u(6자리) : data object id

SELECT * FROM dba_objects WHERE owner = 'HR' AND object_name = 'EMP';

• AAE(3자리) : file id

SELECT * FROM dba_tables WHERE owner = 'HR' AND table_name = 'EMP';
SELECT * FROM dba_data_files WHERE tablespace_name = 'USERS';

• AAAAFT(6자리) : block id

SELECT * FROM dba_segments WHERE owner = 'HR' AND segment_name = 'EMP';
SELECT * FROM dba_extents WHERE owner = 'HR' AND segment_name = 'EMP';

• AAA(3자리) : row slot id, block 안에 있는 행의 위치
• ROWID 디코딩

SELECT rowid,
       dbms_rowid.rowid_object(rowid)   AS data_object_id,
       dbms_rowid.rowid_relative_fno(rowid) AS file_id,
       dbms_rowid.rowid_block_number(rowid) AS block_id,
       dbms_rowid.rowid_row_number(rowid)   AS slot_id
FROM hr.emp;

INDEX

• by index rowid scan 방식을 사용해서 검색 속도를 높이기 위해 사용되는 객체
• 인덱스를 이용해서 행을 검색하면 I/O 개선 가능
• 인덱스는 테이블과 독립적으로 생성
• 인덱스는 오라클이 자동으로 유지 관리
• primary key, unique 제약 조건을 생성하면 unique index가 자동으로 생성됨
• 수동으로 인덱스 생성 가능

PK 생성 → UNIQUE INDEX 생성

ALTER TABLE hr.emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(employee_id);

SELECT * FROM hr.emp WHERE employee_id = 100;

PK 삭제 → UNIQUE INDEX 제거

ALTER TABLE hr.emp DROP CONSTRAINT emp_id_pk;
ALTER TABLE hr.emp DROP PRIMARY KEY;

수동으로 UNIQUE 인덱스 생성

CREATE UNIQUE INDEX hr.emp_idx ON hr.emp(employee_id) TABLESPACE users;

• 테이블 소유자, CREATE ANY INDEX 시스템 권한이 있는 유저만 생성 가능

인덱스 삭제

DROP INDEX hr.emp_idx;

• 소유자, DROP ANY INDEX 시스템 권한이 있는 유저만 삭제 가능

수동으로 NON UNIQUE 인덱스 생성

CREATE INDEX hr.emp_idx ON hr.emp(employee_id) TABLESPACE users;

SELECT * FROM hr.emp WHERE employee_id = 100;

UNIQUE INDEX 생성 후 PK 제약 조건 생성

CREATE UNIQUE INDEX hr.emp_idx ON hr.emp(employee_id) TABLESPACE users;

ALTER TABLE hr.emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(employee_id);

• 이미 employee_id에 대한 유니크 인덱스가 있기 때문에 PK 제약조건을 생성하더라도 기존 유니크 인덱스를 사용

ALTER TABLE hr.emp DROP PRIMARY KEY;
DROP INDEX hr.emp_idx;

• PK에 사용 중인 유니크 인덱스일 경우 삭제 불가
• PK 제약 조건을 삭제 후 인덱스 삭제해야 함

WITH문(9i)

• 두 번 이상 반복되는 SELECT 문을 QUERY BLOCK(가상 테이블)을 만들어서 사용
• 성능 향상 가능

WITH
        가상테이블1(QUERY BLOCK) AS (SUBQUERY),
        가상테이블2(QUERY BLOCK) AS (SUBQUERY),
        가상테이블3(QUERY BLOCK) AS (SELECT ... FROM 가상테이블1),
        ...
        가상테이블100(QUERY BLOCK) AS (SELECT ... FROM 가상테이블2)
        SELECT ...
        FROM 가상테이블3, 가상테이블4, ...
        WHERE 조인절, 조건절;

-- 사원 수가 가장 많은 부서 정보 조회
WITH
    dept_cnt AS (SELECT department_id, count(*) cnt
                FROM hr.employees
                GROUP BY department_id)            
    SELECT d2.* 
    FROM dept_cnt d1, hr.departments d2
    WHERE d1.department_id = d2.department_id
    AND d1.cnt = (SELECT max(cnt) FROM dept_cnt);

-- 부서별 총급여의 평균보다 총급여가 많은 부서 조회
WITH
    dept_cost AS (SELECT d.department_name, e.sumsal
                    FROM (SELECT department_id, sum(salary) sumsal
                            FROM hr.employees
                            GROUP BY department_id) e, hr.departments d
                    WHERE e.department_id = d.department_id),
    avg_cost AS (SELECT sum(sumsal) / count(*) dept_avg
                FROM dept_cost)
    SELECT *
    FROM dept_cost
    WHERE sumsal > (SELECT dept_avg FROM avg_cost);