📌 Oracle SQL 고급 개념 정리

🔍 인덱스(Index)

인덱스는 쿼리 속도 향상을 위한 데이터 접근 경로이며, Oracle에서 자동 관리함.

✅ 인덱스 특징

• 특정 컬럼에 대해 생성
• 정렬이 필요하므로 큰 데이터에선 역효과 가능
• 전체 데이터 중 2% 이하 추출 시 효과적
• PK는 자동으로 인덱스 생성됨

예)
select * from employees where employee_id=111;
→ 빠름 (숫자 기반, rowid로 접근)
select * from employees where last_name='Smith';
→ 느림 (문자 기반, 테이블 전체 스캔)

✅ 인덱스 생성법

• 단일 인덱스
  CREATE INDEX idx_emp_lastname ON employees(last_name);
• 복합 인덱스
  CREATE INDEX idx_emp_name ON employees(first_name, last_name);
• 유니크 인덱스
  CREATE UNIQUE INDEX idx_emp_email ON employees(email);
• 테이블 생성 시 자동 생성

CREATE TABLE emp5 (
  emp_id NUMBER PRIMARY KEY,          -- PK 인덱스 자동 생성
  email VARCHAR2(100) UNIQUE          -- UNIQUE 인덱스 자동 생성
);

✅ 인덱스 성능 확인

SET AUTOTRACE ON;
SET TIMING ON;
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

✅ 인덱스 없이도 속도 향상하는 기법

1. 범위 검색
   salary BETWEEN 8000 AND 10000 → 범위 좁히기
2. 숫자 검색 우선
   문자보다 숫자가 더 빠르게 처리됨

---

🔁 시퀀스(Sequence)

일련번호 자동 생성 객체로, 보통 PK용으로 사용

CREATE SEQUENCE task_id_seq
INCREMENT BY 3
START WITH 10
MINVALUE 10
MAXVALUE 100
CYCLE;

사용 시:

INSERT INTO tasks(id, title) VALUES(task_id_seq.NEXTVAL, 'IT PROG');

---

👤 사용자/롤(Role) & 권한

✅ 사용자 권한

• 일반: connect, resource, dba
• 테이블: create table, select any table 등
• 인덱스: create any index 등

✅ 롤 생성 및 권한 위임

CREATE ROLE dev_role IDENTIFIED BY pw123;
GRANT create table TO dev_role;
GRANT dev_role TO user1;

✅ 동의어(Synonym)

별칭 제공 기능

CREATE SYNONYM HR_EMP FOR hr.employees;

---

📖 데이터 딕셔너리

Oracle의 시스템 객체 정보 저장 공간

종류	설명
USER_	현재 사용자 소유 객체
ALL_	접근 가능한 모든 객체
DBA_	전체 DB 관리용 (관리자 전용)

SELECT username FROM dba_users;

---

🔀 MERGE (병합)

조건에 따라 UPDATE 또는 INSERT 수행

MERGE INTO emp USING dual ON (empno=100)
WHEN MATCHED THEN
  UPDATE SET deptno = 20
WHEN NOT MATCHED THEN
  INSERT (empno, ename, deptno)
  VALUES (100, 'Neena', 20);

✅ 보너스 예제

• SALES 부서: 급여의 10% + 100
• 다른 부서: 급여 3000 이하만 10% 지급
• 3000 초과자는 제외

---

🎯 트리거(Trigger)

특정 이벤트 발생 시 자동 실행되는 자동 쿼리 실행기

✅ 트리거 유형

기준	전	후
시점	SQL 실행 전	실행 후
대상	FOR EACH ROW	FOR EACH STATEMENT

✅ 트리거 예제

CREATE OR REPLACE TRIGGER print_message
AFTER INSERT ON departments
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('정상적으로 데이터가 추가되었습니다');
END;

---

🔁 커서(Cursor)

SELECT 결과를 순차적으로 처리하는 메모리 포인터

✅ 명시적 커서 4단계

1. DECLARE
2. OPEN
3. FETCH INTO
4. CLOSE

DECLARE
  CURSOR ex_cur IS SELECT product_id, product_name FROM products;
BEGIN
  OPEN ex_cur;
  LOOP
    FETCH ex_cur INTO v_id, v_name;
    EXIT WHEN ex_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
  END LOOP;
  CLOSE ex_cur;
END;

---