SQL 14일차

한희수·2023년 4월 4일
sql
0

빅데이터 분석 SQL

목록 보기
14/17

20230331 SQL
<차례>
■ VIEW의 두 가지 제약조건
1) VIEW에 체크 제약조건을 생성하는 방법
2) VIEW를 통해서 DML을 불허하는 방법(SELECT만 가능하게 하도록 하는 제약조건)
■ VIEW에 표현식 있는 경우
● 체크 제약조건을 확인하는 방법
■ SEQUENCE
❍ SEQUENCE 생성
❍ SEQUENCE이름.NEXTVAL : 사용가능한 번호를 리턴하는 가상 컬럼(INSERT)
❍ SEQUENCE이름.CURRVAL: 현재 사용 가능한 번호를 리턴하는 가상컬럼(INSERT)
❍ SEQUENCE 삭제하는 방법
❍ SEQUENCE 설정부터 생성
❍ SEQUENCE 수정
❍ sequence이름.nextval 은 UPDATE에서도 사용 가능함 (위에서 INSERT 함)
■ SYNONYM (동의어)
❍ SYNONYM 생성하는 방법
● SYNONYM 이름은 고유한 명칭이어야 함
❍ SYNONYM 삭제하는 방법
● 다른 사람이 만든 synonym 중 내가 사용할 수 있는 것 확인
❍ PUBLIC SYNONYM 생성하는 방법
● 긴 이름에 대한 PUBLIC SYNONYM 유무 파악 후 생성하는 방법
● 객체 권한이 있어야 PUBLIC SYNONYM을 이용할 수 있음
❍ PUBLIC SYNONYM 삭제하는 방법

  • DROP PUBLIC SYNONYM 시스템 권한이 있어야 함
    ■ OPTIMIZER
  • SQL문을 수행하기 위한 실행계획을 만듦
    ❍ DATA ACCESS METHOD(데이터 처리 방법)
    1) BY USER ROWID SCAN방법
    2) BY INDEX ROWID SCAN 방법
    ■ INDEX
    ● 인덱스 생성시에 아래 쿼리문이 작동함
    ● DECISION TREE 의사결정나무
    ❍ INDEX 삭제하는 방법
    ❍ UNIQUE INDEX 생성하는 방법
    ● PRIMARY KEY 제약조건 생성(UNIQUE INDEX 자동 생성)
    ❍ 조합 인덱스 생성하는 방법(현장에서 꽤 많이 씀)
    ● 조합 인덱스 생성시 선행 컬럼 중요함
((hr))

CREATE OR REPLACE VIEW test_view
AS
SELECT*FROM hr.test;

SELECT*FROM user_tab_privs;

((insa))

SELECT*FROM user_tab_privs;

desc hr.test_view;

SELECT*FROM hr.test_view;

INSERT INTO hr.test_view(id,name,sal) VALUES(500,'나얼',50000);

SELECT*FROM hr.test_view;

commit;

((hr))

SELECT*FROM hr.test_view;

■ VIEW에 체크 제약조건을 생성하는 방법

  • WHERE 절 & WITH CHECK OPTION CONSTRAINT 제약조건 이름
  • CHECK 제약조건은 INSERT, UPDATE 시에 수행됨(DELETE 시엔 수행X)

CREATE OR REPLACE VIEW test_view
AS
SELECT*FROM hr.test
WHERE sal BETWEEN 1000 AND 10000 --> WHERE절 조건식 = 체크 제약조건의 조건식
WITH CHECK OPTION CONSTRAINT test_view_ck;
((test_view_ck : 제약조건 이름))

((hr))

SELECT*FROM user_constraints WHERE table_name = 'TEST_VIEW';

▽▽▽(결과창)

	 CONSTRAINT_TYPE = V(VIEW를 뜻함)

HR TEST_VIEW_CK V TEST_VIEW
● 권한 부여한 운영 중인 VIEW는 DROP 하지 말고(권한 재부여 필요) CREATE OR REPLACE문을 사용해서 수정하자! (관리의 편리성)

  • VIEW를 삭제하게 되면 VIEW에 대해서 객체 권한들이 다 회수가 됨

  • CREATE OR REPLACE를 통해서 VIEW를 수정하면 그 VIEW에 대한 객체 권한은 유지됨

    ((insa, hr))

    INSERT INTO hr.test_view(id,name,sal) VALUES(600,'이문세',20000);
    오류 보고 -
    ORA-01402: view WITH CHECK OPTION where-clause violation
    --> VIEW에 CHECK제약조건에 위반되었기 때문에 오류 발생

    ((hr))

    INSERT INTO hr.test(id,name,sal) VALUES(600,'이문세',20000);
    1 행 이(가) 삽입되었습니다.
    --> 원본 테이블에는 제약조건이 없으니 insert 수행됨

rollback;

((insa))

UPDATE hr.test_view
SET sal = 30000 --> 체크제약조건 위반으로 오류 발생
WHERE id = 202;

DELETE FROM hr.test_view WHERE id = 202;
1 행 이(가) 삭제되었습니다.

rollback’

■ VIEW를 통해서 DML을 불허하는 방법(SELECT만 가능하게 하도록 하는 제약조건)

  • 권한 그대로 두고 조회만 가능하게 하고 싶음

  • WITH READ ONLY

    ((hr))

    CREATE OR REPLACE VIEW test_view
    AS
    SELECT*FROM hr.test
    WITH READ ONLY;

    ((insa))

    SELECT*FROM user_tab_privs; --> 객체권한 그대로 있음!

=== 오류 파티 ===
DELETE FROM hr.test_view WHERE id = 202;

INSERT INTO hr.test_view(id,name,sal) VALUES(600,'이문세',20000);

UPDATE hr.test_view
SET sal = 30000
WHERE id = 202;

DELETE, INSERT, UPDATE 세 개 다 오류남
오류 보고 -
SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view

42399.0000 - "cannot perform a DML operation on a read-only view“

SELECT*FROM hr.test_view; --> 조회는 잘 됨!

((hr))

SELECT*FROM user_constraints WHERE table_name = 'TEST_VIEW';

▽▽▽(결과창)

	 CONSTRAINT_TYPE = O(아마 read Only를 뜻함)

HR SYS_C007102 O TEST_VIEW

■ VIEW에 표현식 있는 경우

  • 열 별칭 필수

  • 해당 컬럼 입력값 INSERT시 오류남

    ((hr)) -- 열 별칭 필수

    CREATE OR REPLACE VIEW test_view
    AS
    SELECT id,name,sal*1.1
    FROM hr.test
    WITH READ ONLY;
    오류 보고 -
    ORA-00998: must name this expression with a column alias

  1. 00000 - "must name this expression with a column alias“

    ((hr)) -- 표현식 있는 컬럼 입력값 insert시 오류

    CREATE OR REPLACE VIEW test_view
    AS
    SELECT id,name,sal*1.1 ann_sal --> 표현식있는데 단순VIEW임
    FROM hr.test; 이 경우 INSERT하면 오류남

INSERT INTO hr.test_view(id,name,ann_sal) VALUES(600,'이문세',5000);
오류 보고 -
SQL 오류: ORA-01733: virtual column not allowed here
01733. 00000 - "virtual column not allowed here“

INSERT INTO hr.test_view(id,name) VALUES(600,'이문세');
1 행 이(가) 삽입되었습니다.
--> 표현식 입력값 제외한 데이터는 INSERT가 가능함 

[정리]
  • 단순VIEW이지만 표현식으로 되어 있는 컬럼은 INSERT 할 때 오류가 발생함
  • 하지만 그 컬럼을 제외하고 입력하면 입력이 됨

ROLLBACK;

((hr)) -- 표현식 있는 컬럼 입력값 update시 오류

UPDATE hr.test_view
SET name = 'JAMES'
WHERE id = 400;
1 행 이(가) 업데이트되었습니다.

UPDATE hr.test_view
SET ann_sal = 50000
WHERE id = 400;
오류 보고 -
SQL 오류: ORA-01733: virtual column not allowed here
01733. 00000 - "virtual column not allowed here“

((hr)) -- 표현식 있는 컬럼 입력값 delete시 오류 안 남

DELETE FROM hr.test_view WHERE id = 400; -- 삭제 가능!!!
1 행 이(가) 삭제되었습니다.

SELECT*FROM hr.test_view;

● 체크 제약조건을 확인하는 방법
SELECT*FROM user_views WHERE view_name = 'TEST_VIEW';

▽▽▽(결과창)

TEST_VIEW 87 "SELECT"ID","NAME","SAL"FROM hr.test
WHERE sal BETWEEN 1000 AND 10000 --> WHERE절이 체크 제약조건 식
WITH CHECK OPTION " --> 체크 제약조건 걸려있구나~!

SELECT*FROM user_constraints WHERE table_name = 'TEST_VIEW';
--> 결과창에서 SEARCH_CONDITION에 체크 제약조건 식을 적어 놓지 않음

■ SEQUENCE

  • 자동일련번호를 생성하는 객체(예: 주문할 때 주문번호, 게시글 게시번호)
  • SEQUENCE 객체를 생성하려면 CREATE SEQUENCE 시스템 권한이 필요함

❍ SEQUENCE 생성

((hr))

SELECT*FROM session_privs; --> ((hr))에 있음

CREATE SEQUENCE id_seq;
Sequence ID_SEQ이(가) 생성되었습니다.

SELECT*FROM user_sequences WHERE sequence_name = 'ID_SEQ';
--> 시퀀스 확인하는 문
-->> 시퀀스 명 대문자로

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 9999999999999999999999999999 1 N N 20 1

--> CACHE_SIZE : 성능 효율 위해 20개는 메모리에 만들어 놓고 씀
--> LAST_NUMBER : 다음 번호 21 (번부터 메모리에 만듦)

((hr))

CREATE TABLE hr.seq_test(id number, name varchar2(30), day date);
Table HR.SEQ_TEST이(가) 생성되었습니다.

❍ SEQUENCE이름.NEXTVAL : 사용가능한 번호를 리턴하는 가상 컬럼(INSERT)
INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'홍길동',sysdate);
1 행 이(가) 삽입되었습니다.

SELECT*FROM hr.seq_test;

▽▽▽(결과창)

ID NAME DAY
1 홍길동 23/03/31

INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'윤건',sysdate);
1 행 이(가) 삽입되었습니다.

SELECT*FROM hr.seq_test;

▽▽▽(결과창)

ID NAME DAY
1 홍길동 23/03/31
2 윤건 23/03/31

SELECT*FROM user_sequences WHERE sequence_name = 'ID_SEQ';

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 9999999999999999999999999999 1 N N 20 21

❍ SEQUENCE이름.CURRVAL: 현재 사용 가능한 번호를 리턴하는 가상컬럼(INSERT)
SELECT id_seq.currval FROM dual;

SELECT id_seq.nextval FROM dual; --> 다음 사용 가능한 번호(3) 사용한 것

▽▽▽(결과창)

nextval
3

SELECT id_seq.currval FROM dual;

▽▽▽(결과창)

currval
3

INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'나얼',sysdate);

SELECT*FROM hr.seq_test;

▽▽▽(결과창)

ID NAME DAY
1 홍길동 23/03/31
2 윤건 23/03/31
4 나얼 23/03/31 --> 아까 nextval에서 써서 순번 gap 생김

rollback;
SELECT*FROM hr.seq_test;

INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'홍길동',sysdate);
INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'나얼',sysdate);
INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'윤건',sysdate);
SELECT*FROM hr.seq_test;

▽▽▽(결과창)

ID NAME DAY
5 홍길동 23/03/31 --> 5번부터 시작(이전 숫자 결번됨, 재사용X)
6 나얼 23/03/31
7 윤건 23/03/31

ROLLBACK;

❍ SEQUENCE 삭제하는 방법

  • DROP SEQUENCE id_seq;
    SELECT*FROM user_sequences WHERE sequence_name = 'ID_SEQ';

❍ SEQUENCE 설정부터 생성

CREATE SEQUENCE id_seq
START WITH 1 ---기본값
MAXVALUE 3 --- 10**27(오라클 기본값)
INCREMENT BY 1 --- 기본값
NOCYCLE --- 기본값, CYCLE(1-3 계속 반복, 고유번호X)
NOCACHE; --- CACHE 20

Sequence ID_SEQ이(가) 생성되었습니다.

SELECT*FROM user_sequences WHERE sequence_name = 'ID_SEQ';

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 3 1 N N 0 1

SELECT*FROM hr.seq_test;

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

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 3 1 N N 0 2

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

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 3 1 N N 0 3

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

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 3 1 N N 0 4

INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'이문세',sysdate); - 오류남
오류 보고 -
ORA-08004: sequence ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
--->> 오류 발생, MAXVALUE(3)까지 수행되어서 더 이상 SEQUENCE 값을 생성할 수 없음

SELECT*FROM hr.seq_test;

▽▽▽(결과창)

ID NAME DAY
1 홍길동 23/03/31
2 윤건 23/03/31
3 나얼 23/03/31

COMMIT;

❍ SEQUENCE 수정

  • SEQUENCE 옵션 중에 START WITH 옵션은 수정할 수 없음

ALTER SEQUENCE id_seq
MAXVALUE 10;

Sequence ID_SEQ이(가) 변경되었습니다.

SELECT*FROM user_sequences WHERE sequence_name = 'ID_SEQ';

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 10 1 N N 0 4

INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'이문세',sysdate);
1 행 이(가) 삽입되었습니다.

SELECT*FROM hr.seq_test;

▽▽▽(결과창)

ID NAME DAY
1 홍길동 23/03/31
2 윤건 23/03/31
3 나얼 23/03/31
4 이문세 23/03/31

ALTER SEQUENCE id_seq
MAXVALUE 5
CYCLE;
Sequence ID_SEQ이(가) 변경되었습니다.

SELECT*FROM user_sequences WHERE sequence_name = 'ID_SEQ';

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER

ID_SEQ 1 5 1 Y N 0 5

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

▽▽▽(결과창)

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
ID_SEQ 1 5 1 Y N 0 6
---> MAXVALUE 5이기 때문에 결과창에 6이라 떠도 CYCLE(Y)로 인해 1로 다시 돌아감

INSERT INTO hr.seq_test(id,name,day) VALUES(id_seq.nextval,'제임스',sysdate);
SELECT*FROM hr.seq_test;

▽▽▽(결과창)

ID NAME DAY
1 홍길동 23/03/31
2 윤건 23/03/31
3 나얼 23/03/31
4 이문세 23/03/31
5 손흥민 23/03/31
1 제임스 23/03/31

으아아

❍ sequence이름.nextval 은 UPDATE에서도 사용 가능함(위에서 INSERT 함)

SELECT*FROM hr.seq_test;
UPDATE hr.seq_test
SET id = id_seq.nextval
WHERE id = 5;
1 행 이(가) 업데이트되었습니다.

ROLLBACK;

--- 시퀀스 끝

■ SYNONYM 동의어

  • 긴 객체 이름을 짧은 이름으로 사용하는 객체
    (user이름.테이블명 --> 이게 너무 많을 경우 사용하면 좋음! 내 스스로 만들어 쓰는 것임)
  • synonym을 생성하려면 CREATE SYNONYM 시스템 권한이 필요함
  • 중복성 없는 고유한 이름으로 만들어야 함
  • 사용자의 편리성을 위해 있음

SELECT*FROM session_privs; --> create synonym 권한 있음

CREATE TABLE emp_copy_2023
AS SELECTFROM hr.employees;
Table EMP_COPY_2023이(가) 생성되었습니다.
SELECTFROM hr.emp_copy_2023;

❍ SYNONYM 생성하는 방법
CREATE SYNONYM ec2 FOR emp_copy_2023;
Synonym EC2이(가) 생성되었습니다.

SELECTFROM ec2; --> SELECTFROM hr.emp_copy_2023;와 동일한 결과

SELECT*FROM user_synonyms WHERE table_name = 'EMP_COPY_2023';

▽▽▽(결과창)

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
EC2 HR EMP_COPY_2023 (NULL)

● SYNONYM 이름은 고유한 명칭이어야 함

CREATE SYNONYM emp FOR emp_copy_2023;
오류 보고 -
ORA-00955: name is already used by an existing object
00955. 00000 - "name is already used by an existing object“

❍ SYNONYM 삭제하는 방법
DROP SYNONYM ec2;
Synonym EC2이(가) 삭제되었습니다.

SELECT*FROM user_synonyms WHERE table_name = 'EMP_COPY_2023';
--> 암것도 없음

● 다른 사람이 만든 synonym 중 내가 사용할 수 있는 것 확인

  • OWNER가 PUBLIC인 경우 접속자는 모두 사용 가능함
  • 내가 만든 것: user, 남이 만든 것: all, dba가 만든 것: dba_

SELECT*FROM all_synonyms;

점심시간~.~

((hr))

DROP TABLE emp PURGE;

DROP TABLE dept PURGE;
GRANT SELECT ON hr.employees TO insa;
GRANT SELECT ON hr.departments TO insa;

SELECT*FROM user_tab_privs;

((insa))

SELECT*FROM user_tab_privs;

SELECTFROM hr.employees;
SELECTFROM hr.departments;

SELECT e.,d.
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id;

❍ PUBLIC SYNONYM 생성하는 방법
● 모든 유저들이 사용할 수 있는 public synonym을 생성하려면 CREATE PUBLIC SYNONYM 시스템 권한이 있어야 함

((hr))

SELECT*FROM session_privs;
--> CREATE PUBLIC SYNONYM 시스템권한 없음(dba만 가진 권한임)

((dba))

SHOW USER; --> ”SYS“입니다.
GRANT CREATE PUBLIC SYNONYM TO hr; (sys 계정 수행)

((hr))

SELECT*FROM session_privs;
--> CREATE PUBLIC SYNONYM 시스템권한 있음

CREATE PUBLIC SYNONYM emp FOR hr.employees;

SELECTFROM user_synonyms; ---PUBLIC SYNONYM은 이곳에서 안보임
SELECTFROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';

▽▽▽(결과창)

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
PUBLIC EMP HR EMPLOYEES (NULL)

((insa))

SELECTFROM user_synonyms; ---PUBLIC SYNONYM은 이곳에서 안보임
SELECTFROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR'; --> 확인됨

SELECT*FROM emp;

● 긴 이름에 대한 PUBLIC SYNONYM 유무 파악 후 생성하는 방법
((insa))
SELECTFROM user_tab_privs; --> 이거 확인하고 public synonym 없는지 확인
1) SELECTFROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';
2) SELECT*FROM all_synonyms WHERE table_name = 'DEPARTMENTS' AND table_owner = 'HR';

((hr))

CREATE PUBLIC SYNONYM dept FOR hr.departments;
SELECT*FROM all_synonyms WHERE table_name = 'DEPARTMENTS' AND table_owner = 'HR';

((insa))

SELECT*FROM dept;

SELECT e., d.
FROM emp e JOIN dept d
ON e.department_id = d.department_id;

● 객체 권한이 있어야 PUBLIC SYNONYM을 이용할 수 있음

  • PUBLIC SYNONYM으로 생성된 SYNONYM을 사용하려면 그 SYNONYM에 연결되어 있는 객체애 대한 권한이 있어야지만 SYNONYM을 사용할 수 있음

    ((hr))

    REVOKE SELECT ON hr.employees FROM insa;
    REVOKE SELECT ON hr.departments FROM insa;
    SELECT*FROM user_tab_privs;

    ((insa))

    SELECTFROM user_tab_privs;
    SELECT    FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner = 'HR';
    SELECT*FROM all_synonyms WHERE table_name = 'DEPARTMENTS' AND table_owner = 'HR';

--->> 둘 다 synonym(emp, dept)을 확인할 수 있음

but,

SELECT e., d.
FROM emp e JOIN dept d
ON e.department_id = d.department_id;
오류보고 -
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist“

--->>> synonym을 사용할 수는 없음

❍ PUBLIC SYNONYM 삭제하는 방법

  • DROP PUBLIC SYNONYM 시스템 권한이 있어야 함

    ((hr))

    SELECT*FROM all_synonyms WHERE table_owner = 'HR';

    ▽▽▽(결과창)

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
PUBLIC DEPT HR DEPARTMENTS (NULL)
PUBLIC EMP HR EMPLOYEES (NULL)

DROP SYNONYM emp;
오류 보고 -
ORA-01434: private synonym to be dropped does not exist
01434. 00000 - "private synonym to be dropped does not exist“
--> PUBLIC SYNONYM은 이렇게 삭제할 수 없음

DROP PUBLIC SYNONYM emp;
오류 보고 -
ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges“
-->> 필요한 시스템 권한 : DROP PUBLIC SYNONYM

((dba))

GRANT DROP PUBLIC SYNONYM TO hr;
Grant을(를) 성공했습니다.

((hr))

SELECT*FROM session_privs;
--->> DROP PUBLIC SYNONYM 시스템 권한 받음

DROP PUBLIC SYNONYM emp;
SYNONYM EMP이(가) 삭제되었습니다.

DROP PUBLIC SYNONYM dept;
SYNONYM DEPT이(가) 삭제되었습니다.

SELECT*FROM all_synonyms WHERE table_owner = 'HR';
--->>> 암것도 없다.

■ OPTIMIZER

  • 핵심, 오라클이 비싼 이유임, SQL 튜닝에서 배우는 내용
  • SELECT 문장을 수행할 수 있는 실행계획이 만들어져야 함(모든 dbms가 수행함)
    -- F10 확인
  • SQL문을 수행하기 위한 실행계획을 만듦

SELECT *
FROM hr.employees
WHERE employee_id = 100; --> 이런 SQL문!

❍ DATA ACCESS METHOD(데이터 처리 방법)

  • 데이터 ACCESS 방법을 결정

  • 예시: employee_id = 100를 찾는 가장 좋은 처리 방법

  • 비유해보자면?
    책 = 테이블
    페이지(문단, 장) = BLOCK (2K, 4K(기본값-보편적), 8K(기본값-보편적), 16K, 32K)
    문장 = 행(ROW)

  • 특정한 단어가 있는 문장을 찾아야 한다면?
    1) FULL TABLE SCAN
    --- 첫 번째 페이지(BLOCK)에 있는 문장(행)부터 마지막 페이지(BLOCK) 문장(행)까지 ACCESS하는 방식

2) ROWID SCAN
--- 물리적인 주소를 가지고 찾아가는 방식(INSERT하는 시점에 결정이 됨)
--- 행의 물리적 주소를 가지고 찾는 방식, 데이터 ACCESS 방법 중 가장 빠름
--- ROWID : employee_id에는 없는 가상의 컬럼, 행의 물리적 주소를 나타냄

((예시))

SELECT ROWID, employee_id
FROM hr.employees;

▽▽▽(결과창)

ROWID EMPLOYEE_ID
AAAEAbAAEAAAADNAAA 100
AAAEAbAAEAAAADNAAB 101
AAAEAbAAEAAAADNAAC 102
AAAEAbAAEAAAADNAAD 103
AAAEAbAAEAAAADNAAE 104
AAAEAbAAEAAAADNAAF 105
AAAEAbAAEAAAADNAAG 106

--- ROWID
AAAEAb (6자리) : DATA OBJECT ID

  • SELECT*FROM user_objects WHERE object_name = 'EMPLOYEES'; 여기서 object_id에 해당
    AAE (3자리) : FILE ID
  • SELECT*FROM user_tables WHERE table_name = 'EMPLOYEES';
    --> 이 OBJECT가 어느 파일에 있는지 확인
  • SELECT*FROM dba_data_files WHERE tablespace_name = 'USERS';
    --> ((dba)) 에서만 확인 가능. 어느 디스크(데이터 파일)에 있는지 확인
    AAAADN (6자리) : BLOCK ID
  • SELECT*FROM user_segments WHERE segment_name = 'EMPLOYEES';
  • SELECT*FROM user_extents WHERE segment_name = 'EMPLOYEES';
  • ((dba) SELECT*FROM dba_segments WHERE segment_name = 'EMPLOYEES';
    --> HEADER_BLOCK ‘202’, ‘346’
    AAA (3자리) : ROW SLOT ID
  • BLOCK 안에 ROW가 있는 위치

AAAEAbAAEAAAADNABh 197
AAAEAbAAEAAAADOAAA 198 --> BLOCK ID 바뀜, ROW SLOT ID 다시 시작

★ 가장 빠른 방법:
1) BY USER ROWID SCAN방법
SELECT *
FROM hr.employees
WHERE rowid = 'AAAEAbAAEAAAADNAAN';
--> BUT, ROWID를 외울 수 없음

2) BY INDEX ROWID SCAN 방법
SELECT*
FROM hr.employees
WHERE employee_id = 100;
-->> F10 누르면 ‘BY INDEX ROWID’ 확인 가능
‘EMP_EMP_ID_PK’ -> 이것이 INDEX
UNIQUE SCAN(고유값이므로 100번 찾으면 검색 종료), NONUNIQUE는 계속 검색

▽▽▽(결과창)

ROWID EMPLOYEE_ID
AAAEAbAAEAAAADNAAA 100
AAAEAbAAEAAAADNAAB 101
AAAEAbAAEAAAADNAAC 102
AAAEAbAAEAAAADNAAD 103
AAAEAbAAEAAAADNAAE 104
AAAEAbAAEAAAADNAAF 105
AAAEAbAAEAAAADNAAG 106
--->> 이걸 메모장에 저장해놓는 원리

((범위))

DB > USERS > EMP > SEGMENTS > EXTENTS > ROWS
--> 엔지니어들은 이런걸 면밀히 공부함

■ INDEX

  • BY INDEX ROWID SCAN 방식을 사용해서 행의 검색 속도를 높이기 위한 객체
  • 인덱스를 이용해서 행을 검색하면 I/O(INPUT 입력값, OUTPUT 출력값)을 개선할 수 있음
  • 인덱스는 테이블과 독립적으로 생성됨
  • 인덱스는 자동으로 유지, 관리함
  • PRIMARY KEY 제약조건과 UNIQUE 제약조건을 생성하면 UNIQUE INDEX가 자동 생성됨
  • 그 외는 수동으로 인덱스를 생성함

CREATE TABLE emp
AS
SELECT *
FROM hr.employees;
Table EMP이(가) 생성되었습니다.

SELECT*FROM user_constraints WHERE table_name = 'EMP';
--->> 데이터 그대로, 제약조건 중 NOT NULL 제약조건 복제됨

SELECT*FROM user_indexes WHERE table_name = 'EMP';
--->> 암것도 없음

SELECT*FROM user_ind_columns WHERE table_name = 'EMP';
--->> 암것도 없음

SELECT *
FROM hr.emp
WHERE employee_id = 100;
---->>> F10 : Filter Predicates 필터 술어로 보임. 이것은 FULL TABLE SCAN한 것임
찾아도 계속 찾음(처음부터 끝까지) 시간이 더딜 수밖에 없음(성능 떨어짐)
----->>>> 그러므로 인덱스를 생성하자!!

CREATE INDEX emp_id_idx ---> 고유한 이름으로 생성
ON hr.emp(employee_id)
Index EMP_ID_IDX이(가) 생성되었습니다.

SELECTFROM user_indexes WHERE table_name = 'EMP';
---> 결과창 UNIQUENESS : NONUNIQUE로 생성됨
SELECTFROM user_ind_columns WHERE table_name = 'EMP';
---> 결과창 EMPLOYEE_ID 컬럼에 인덱스 생성됨 화인

SELECT *
FROM hr.emp
WHERE employee_id = 100;
--->>> F10 : INDEX RANGE SCAN, BY INDEX ROWID 나옴. Access Pradicates 엑세스 술어.

● 인덱스 생성시에 아래 쿼리문이 작동함
SELECT ROWID, employee_id
FROM hr.emp
ORDER BY employee_id asc;

● DECISION TREE 의사결정나무
root > branch > leaf(emp_id, rowid)

❍ INDEX 삭제하는 방법

SELECT*FROM user_indexes WHERE table_name = 'EMP';
---> 결과창 UNIQUENESS : NONUNIQUE로 생성됨
--->> 이미 unique한 값인데 nonunique 인덱스를 만들 필요가 엄슴

DROP INDEX emp_id_idx;
Index EMP_ID_IDX이(가) 삭제되었습니다.

❍ UNIQUE INDEX 생성하는 방법
CREATE UNIQUE INDEX hr.emp_id_idx
ON hr.emp(employee_id);
INDEX HR.EMP_ID_IDX이(가) 생성되었습니다.

SELECT*FROM user_indexes WHERE table_name = 'EMP';
---> 결과창 UNIQUENESS : UNIQUE로 생성됨

SELECT*
FROM hr.employees
WHERE employee_id = 100;
--->>> F10 : INDEX UNIQUE SCAN 나옴(데이터 찾으면 다시 LEAF BLOCK 가지 않음)

DROP INDEX emp_id_idx;
Index EMP_ID_IDX이(가) 삭제되었습니다.

● PRIMARY KEY 제약조건 생성(UNIQUE INDEX 자동 생성)
ALTER TABLE hr.emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(employee_id);
Table HR.EMP이(가) 변경되었습니다.

SELECTFROM user_constraints WHERE table_name = 'EMP';
SELECTFROM user_cons_columns WHERE table_name = 'EMP';

SELECT*
FROM hr.employees
WHERE employee_id = 100;

SELECT*
FROM hr.employees
WHERE employee_id = 100;
--->>> F10 : INDEX UNIQUE SCAN 나옴

SELECT *
FROM hr.emp
WHERE last_name = 'King'; -- last_name 컬럼에 index 생선되어 있지 않기 때문에
--->>> F10 : FILTER 술어, FULL TABLE SCAN

---last_name은 중복성 있기 때문에 nonunique index를 만들어야 함

CREATE INDEX emp_name_idx
ON hr.emp(last_name);
Index EMP_NAME_IDX이(가) 생성되었습니다.

SELECT*FROM user_indexes WHERE table_name = 'EMP';
▽▽▽(결과창)
EMP_ID_PK NORMAL HR EMP TABLE UNIQUE DISABLED
EMP_NAME_IDX NORMAL HR EMP TABLE NONUNIQUE DISABLED

SELECT*FROM user_ind_columns WHERE table_name = 'EMP';

SELECT *
FROM hr.emp
WHERE last_name = 'King';
--->>> F10 : INDEX RANGE SCAN 나옴

--- INDEX 생성시에 내부적으로 아래 쿼리문장이 수행되어서 만듦
SELECT last_name, rowid
FROM hr.emp;

▽▽▽(결과창)

LAST_NAME ROWID
......
Kaufling AAAE+3AAEAAAAHzAAV
King AAAE+3AAEAAAAHzAAA
King AAAE+3AAEAAAAHzAA3
Kochhar AAAE+3AAEAAAAHzAAB
Kumar AAAE+3AAEAAAAHzABI
.....

SELECT *
FROM hr.emp
WHERE last_name = 'King'
AND first_name = 'Steven’
--->>> F10 : last_name은 NDEX RANGE SCAN 나옴(INDEX 걸려있음)
first_name은 FILTER술어, FULL RANGE SCAN 나옴(INDEX 안 걸려있음)
--------->>>>ROWID 모두 확인해야 함(속도 떨어짐)
------------>>> 이럴 때 조합 인덱스를 걺

DROP INDEX emp_name_idx;

❍ 조합 인덱스 생성하는 방법(현장에서 꽤 많이 씀)
CREATE UNIQUE INDEX emp_name_idx
ON hr.emp(last_name, first_name);

SELECT*FROM user_ind_columns WHERE table_name = 'EMP';

▽▽▽(결과창)

EMP_ID_PK EMP EMPLOYEE_ID 1 22 0
EMP_NAME_IDX EMP FIRST_NAME 2 20 20
EMP_NAME_IDX EMP LAST_NAME 1 25 25 --> 조합 인덱스로군

SELECT last_name,first_name, rowid
FROM hr.emp
ORDER BY 1,2;

▽▽▽(결과창)

.....
Kaufling Payam AAAE+3AAEAAAAHzAAV
King Janette AAAE+3AAEAAAAHzAA3
King Steven AAAE+3AAEAAAAHzAAA
Kochhar Neena AAAE+3AAEAAAAHzAAB
Kumar Sundita AAAE+3AAEAAAAHzABI
.....

SELECT
FROM hr.emp
WHERE last_name = 'King'
AND first_name = 'Steven';
--->>> F10 : last_name, first_name INDEX UNIQUE SCAN 나옴(INDEX 걸려있음)
SELECT
FROM hr.emp
WHERE last_name = 'King';
--->>> F10 : last_name만 검색해도 INDEX RANGE SCAN 나옴(INDEX 걸려있음)

● 조합 인덱스 생성시 선행 컬럼 중요함

  • 혼자서도 자주 사용되는 컬럼(예; last_name)
  • 범위를 좀 축소시킬 수 있는 컬럼

SELECT *
FROM hr.emp
WHERE first_name = 'Steven';
--->>> 조합 인덱스 생성후 후행 컬럼만 검색할시 다시 FILTER 술어 나옴(FULL TABLE SCAN)

profile
한희수
이전 포스트

SQL 13일차

다음 포스트

SQL 15일차

0개의 댓글