43_[db]객체 종류

객체 종류

데이터 사전 Data Dictionary

🌺 오라클 데이터베이스 테이블 = 사용자 테이블 User Table ➕ 데이터 사전 Data Dictionary

🌹 사용자 테이블 : DB를 통해 관리할 데이터를 저장하는 테이블 “normal table”

🌹 데이터 사전 : 데이터베이스를 구성하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블 [DB 생성 시점에 자동으로 만들어 짐!]”base table”

⭐ 오라클DB, 사용자가 직접 데이터 사전에 접근 🚫!! 대신 데이터 사전 뷰(Data Dictionary View)를 제공함으로써 SELECT로 정보열람은 가능!

데이터 사전 뷰 분류

🌟 사용 가능한 데이터 사전 살펴보기

SELECT * FROM DICT;

SELECT * FROM DICTIONARY;

⭐ USER_TABLES 테이블

• TABLE_NAME, TABLESPACE_NAME(테이블 스페이스 이름) 등의 열 정보 확인 가능

SELECT * FROM USER_TABLES;

🌺 ALL_TABLES 테이블

• USER_TABLES 테이블과 다르게, OWNER(테이블을 소유한 사용자) 열이 하나 더 존재

▶️ 다른 사용자가 소유한 테이블 중 현재 사용자에게 사용 허가가 되어 있는 테이블을 존재하고자 할 때 사용됨

✴️ DUAL 테이블의 경우, SYSTEM 소유이며, 다른 계정들은 테이블 사용을 허가받은 것!

⭐ DBA_XXXX테이블에 대해서 SYSTEM, SYS 계정 외의 계정에서

SELECT * FROM DBA_TABLES;

와 같이 접근하려고 하면,

SQL Error [942] [42000]: ORA-00942: table or view does not exist

에러가 발생한다. 그 이유는 ‘존재는 하지만, 권한이 없습니다’의 문구가 보안 문제를 일으킬 수 있기 때문이다!

🌺 DBA_TABLES : 데이터베이스 내에 존재하는 모든 테이블 정보 출력

🌺 DBA_USERS : 오라클 DB에 등록된 사용자 정보(USERNAME 컬럼이 사용자명을 나타냄)

인덱스

• 데이터 검색 성능 향상을 위해서 테이블 열에 사용하는 객체
• 특정 행 데이터의 주소(위치 정보)를 목록으로 만들어 둔 것
• 소유 사용자, 사용 권한이 존재
• USER_INDEXES, USER_IND_COLUMNS와 같은 데이터 사전을 이용하여 인덱스 정보 열람 가능

🌹 데이터 검색 방식 종류

(1) Table Full Scan : 테이블 데이터를 처음부터 끝까지 검색하여 원하는 데이터를 찾는 방식

(2) Index Scan : 인덱스를 통해 데이터를 찾는 방식

-INDEX_NAME과 TABLE_NAME으로 어떤 테이블에 대해서 어떤 인덱스가 지정되어 있는지 확인 가능!

[USAGE]

1. 인덱스 생성

CREATE INDEX 인덱스 이름
ON 테이블이름(열이름1 ASC OR DESC,
...
			       열이름N ASC OR DESC); --기본값은 ASC(인덱스 정렬 옵션)

--1. 단일 인덱스
CREATE INDEX 인덱스이름
ON 테이블이름(열이름1 ASC OR DESC);

--2.복합/결합 인덱스(concatenated / composite index)
--WHERE절의 두 열이 AND연산으로 묶이는 경우 주로 사용됨
--두 개 이상 열로 만들어지는 인덱스
CREATE INDEX 인덱스이름
ON 테이블이름(열이름1 ASC OR DESC,
...
				      열이름N ASC OR DESC);

--3.고유 인덱스
--열에 중복 데이터가 없을 때 사용
--UNIQUE 키워드 지정하지 않을 경우, 비고유 인덱스(non unique index)가 기본값
CREATE UNIQUE INDEX 인덱스 이름
ON 테이블이름(열이름 ASC OR DESC);

--4.함수 기반 인덱스
--열에 산술식같은 데이터 가공이 진행된 결과로 인덱스 생성
CREATE INDEX 인덱스이름
ON 테이블이름(함수식[==계산식]);

--5.비트맵 인덱스
--데이터종류가 적고 같은 데이터가 많을 때 주로 사용
CREATE INDEX 인덱스이름
ON 테이블이름(열이름1 ASC OR DESC);

1. 인덱스 삭제

DROP INDEX 인덱스이름;

뷰 VIEW

• 가상 테이블 Virtual Table
• 하나 이상의 테이블을 조회하는 SELECT 문을 저장한 객체
• 뷰의 사용목적
  1. 편리성
  2. 보안성
  3. 독립성

⭐ 뷰를 사용하려면, 계정에 대해서 뷰를 생성할 수 있는 권한을 부여해주어야 함!!

GRANT CREATE VIEW TO 계정명;

🌺뷰 생성

CREATE[ OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름(열이름1,...,열이름N)
AS (저장할 SELECT 문)
[WITH CHECK OPTION [CONSTRAINT 제약조건]]
[WITH READ ONLY [CONSTRAINT 제약조건]];

• CREATE OR REPLACE: 같은 이름의 뷰가 이미 존재한다면, 현재 생성할 뷰로 대체하여 생성
• FORCE: 뷰가 저장할 SELECT 문의 기반 테이블이 존재하지 않아도 강제 생성 (기본값: NOFORCE-뷰가 저장할 SELECT 문의 기반테이블이 존재할 경우에만 생성)
• WITH CHECK OPTION : 지정한 제약 조건을 만족하는 데이터에 대해서 DML 작업이 가능하도록 뷰 생성
• WITH READ ONLY : 뷰의 열람만 가능하도록 뷰 생성
• WITH CHECK OPTION, WITH READ ONLY 뒤에 CONSTRAINT 제약조건명을 붙여준다면, 해당 뷰의 SELECT 문을 참조하여 WITH CHECK OPTION, WITH READ ONLY 제약조건을 설정함

🌻 WITH CHECK OPTION 확인하기

CREATE OR REPLACE VIEW VW_EMP20
AS (SELECT EMPNO, ENAME,JOB, DEPTNO
	FROM EMP
	WHERE DEPTNO=20)
WITH CHECK OPTION CONSTRAINT CK_VEMP20;

만약 위와 같이 부서번호 20인 부서에 대해서 WITH CHECK OPTION을 걸어주게 되면,

INSERT INTO VW_EMP20
VALUES(1000,'GOOD','SALESMAN',30);

위와 같이 부서번호 30인 데이터를 추가하는 DML 등을 시도하게 된다면

“SQL Error [1402] [44000]: ORA-01402: view WITH CHECK OPTION where-clause violation”

와 같이 WITH CHECK OPTION 뷰에서 WHERE 절 조건에 위배된다는 에러를 확인할 수 있다!

🌻 WITH READ ONLY 확인하기

(예시)

CREATE OR REPLACE VIEW VW_EMP20
AS (SELECT EMPNO, ENAME,JOB, DEPTNO
	FROM EMP
	WHERE DEPTNO=20)
WITH READ ONLY CONSTRAINT RO_VEMP20;

위와 같이, WITH READ ONLY 속성을 부여한 후,

SELECT * FROM VW_EMP20;

위와 같이 SELECT 질의는 처리할 수 있다!

하지만,

INSERT INTO VW_EMP20
VALUES(1000,'GOOD','SALESMAN',30);

위와 같은 DML을 수행하려고 하였을 때,

“SQL Error [42399] [99999]: ORA-42399: cannot perform a DML operation on a read-only view”

위와 같이 DML 수행을 read-only 뷰에서 실행할 수 없음이라는 에러를 직면할 수 있다!

-생성된 뷰 확인하기

SELECT VIEW_NAME, TEXT_LENGTH, TEXT
FROM USER_VIEWS;

• TEXT 내에는 위에서 뷰를 생성할 때 사용되었던 SELECT문이 저장됨

만약, FORCE 옵션을 이용하여 아래와 같이 기존의 DEPT 테이블에는 DEPTNO, DNAME, LOC 속성만 존재하였는데, 존재하지 않는 속성을 FORCE 옵션과 같이 지정하여 뷰를 생성하고자 한다면

CREATE OR REPLACE FORCE VIEW VW_FOR(CNT,ID,UPWD)
AS (SELECT * FROM DEPT);

컬럼 갯수에 맞게 DEPT 테이블의 각 컬럼이 1:1 대응된다!(DEPTNO-CNT, DNAME-ID, UPWD-LOC)

⚠️ 하지만, 갯수가 맞지 않으면 뷰가 만들어지는 것처럼 보일 수 있지만, SELECT 쿼리로 확인하려고 하면 “SQL Error [4063] [72000]: ORA-04063: view “KH.VW_FOR” has errors”와 같은 에러가 발생할 수 있다!

한 가지 더, 기반 테이블이 존재하지 않는 경우, 뷰가 만들어지지만, SELECT로 확인해보려 한다면,

CREATE OR REPLACE FORCE VIEW VW_FOR(CNT,ID,UPWD,GR)
AS (SELECT * FROM ORANGE);

동일하게 “SQL Error [4063] [72000]: ORA-04063: view “KH.VW_FOR” has errors” 에러가 발생한다!

🌺 뷰 삭제

DROP VIEW 뷰이름;

🌺 뷰 컬럼 코멘트 추가

COMMENT ON COLUMN 뷰이름(테이블 이름).컬럼명 IS '코멘트 내용';

🌸코멘트 확인(뷰, 테이블 모두 가능)

SELECT * FROM USER_COL_COMMENTS [WHERE TABLE_NAME='테이블 이름'];

시퀀스

• 오라클 DB에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체

🌺 시퀀스 생성

CREATE SEQUENCE 시퀀스 이름
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];

1. INCREMENT BY n : 시퀀스에서 생성할 번호의 증가값(기본값은 1)
2. START WITH n : 시퀀스에서 생성할 번호의 시작값(기본값은 1)
3. MAXVALUE n: 시퀀스에서 생성할 번호의 최댓값을 지정. 시작값(START WITH) 이상, 최솟값(MINVALUE) 초과하는 값으로 설정 NOMAXVALUE : 오름차순이면 10^27 , 내림차순이면 -1로 설정
4. MINVALUE n : 시퀀스에서 생성할 번호의 최솟값 지정. 시작값(START WITH) 이하, 최댓값(MAXVALUE) 미만값으로 지정 NOMINVALUE : 오름차순이면 1, 내림차순이면 10^(-26)으로 설정
5. CYCLE : 시퀀스에서 생성한 번호가 최댓값에 도달했을 경우, START WITH 값에서 다시 시작 NOCYCLE: 번호 생성이 중단되고, 추가 번호 생성 요청시 오류 발생
6. CACHE n: 시퀀스가 생성할 번호를 메모리에 미리 할당해 놓은 수를 지정 - n 개수만큼까지 시퀀스로 번호를 미리 생성해두고(LAST_NUMBER에 저장됨), n개수만큼 채워지면, 또 n 만큼 미리 계산해두고 기다리고 있게 됨!(그러다가 최댓값 도달 시 끝남) NOCACHE: 미리 번호를 생성하지 않도록 설정 [옵션을 모두 생략할 경우 기본값은 20]

CREATE SEQUENCE SQ_EMP
INCREMENT BY 2
START WITH 3
MAXVALUE 30
MINVALUE 1
CYCLE
CACHE 10;

위와 같은 시퀀스를 만들어보자!(시퀀스 이름은 임의로 연습용으로 만든것이다!)

생성된 시퀀스는 데이터 사전 뷰 중 USER_SEQUENCES 에서 확인할 수 있다!

SELECT * FROM USER_SEQUENCES;

위를 실행해보면, 시퀀스이름(SEQUENCE NAME) 뿐 아니라 최솟값(MINVALUE), 최댓값, 증감수, CYCLE 여부, CACHE 크기, LAST_NUMBER(최근에 실행된 번호) 등을 확인할 수 있다!

🌹 생성된 시퀀스만 다른 조작없이 실행해보기

SELECT 시퀀스명.NEXTVAL FROM DUAL;

• 만약 위에서 생성해둔 SQ_EMP 시퀀스를 실행해보면, 10개가 되는 시점에서 LAST NUMBER가 23에서 31로 바뀌는 것을 확인해볼 수 있다![실행 전에는 데이터 사전뷰로 확인해보면 3, 즉 시작값임을 확인할 수 있다!]
• 30이 최댓값인데 LAST NUMBER가 31로 저장된 것은, CACHE 특성상 미리 저장은 하는데, MAX VALUE가 30이므로 잘리게 되어 사실상 29가 되면 최솟값으로 돌아간다!

⭐ CYCLE 속성이 붙고, MINVALUE값이 존재하면 최댓값 이후에는 최솟값부터 반복하게 된다!

🌹 생성된 시퀀스에 대해서 가장 마지막 값 확인하기

• 위의 NEXTVAL이 한번이라도 실행되어야 확인할 수 있다!

SELECT 시퀀스명.CURRVAL FROM DUAL;

🌺 시퀀스 수정

[USAGE]

ALTER SEQUENCE 시퀀스 이름
[INCREMENT BY n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];

• 시퀀스 수정은 생성때와 다르게 START WITH 값을 변경할 수 없다는 점에 주의하자!

위에서 2씩 증가하고 MINVALUE를 주었으며, CYCLE을 설정해주었던 시퀀스 SQ_EMP를

3씩 증가하고 NOMINVALUE, NOCYCLE로 바꾸어보자

ALTER SEQUENCE SQ_EMP
INCREMENT BY 3
NOMINVALUE
NOCYCLE;

그리고 이전에 실행되었던 번호가 23이었다면, 이번에는 3씩 증가되어

26, 29를 실행하다가 최댓값인 30과 비교해서 더이상 실행이 불가하면

NOCYCLE 속성으로 인해 최솟값으로(NOMINVALUE이므로 최솟값은 오름차순인 상황에서 1) 돌아갈 수 없으므로

“SQL Error [8004] [72000]: ORA-08004: sequence SQ_EMP.NEXTVAL exceeds MAXVALUE and cannot be instantiated”와 같이 최댓값을 넘어서 예시로 보여질 수 없음을 경고해준다!

🌺 시퀀스 삭제

DROP SEQUENCE 시퀀스이름;

⭐ 시퀀스를 삭제한 후, 데이터 사전 뷰 중 USER_SEQUENCES 에서는 삭제된 것을 확인해볼 수 있다!

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME='SQ_EMP';

• 위의 결과로, 빈 테이블이 보일 것이다!

SELECT SQ_EMP.CURRVAL FROM DUAL;
SELECT SQ_EMP.NEXTVAL FROM DUAL;

• 그리고! 가장 마지막 값과 다음 값을 조회하려고 하면

“SQL Error [2289] [42000]: ORA-02289: sequence does not exist” 와 같이 시퀀스가 존재하지 않는다는 문구를 볼 수 있을 것이다!

⭐ 하지만!! 중요한 사실은!! 아래와 같이 기존에 시퀀스를 이용해서 작업된 데이터는 삭제되지 않는다!!

CREATE TABLE EMP_SQ
AS SELECT * FROM EMP WHERE 1<>1;

SELECT * FROM EMP_SQ;
--EMPNO에 시퀀스를 넣도록 할 것
INSERT INTO EMP_SQ
VALUES(SQ_EMP.NEXTVAL,'김길동','SALEMAN',2000,'2020-10-10',2000,30,10);

INSERT INTO EMP_SQ
VALUES(SQ_EMP.NEXTVAL,'정길동','ANALYST',3000,'2020-10-10',2000,30,10);

INSERT INTO EMP_SQ
VALUES(SQ_EMP.NEXTVAL,'홍길동','MANAGER',2500,'2020-10-10',3000,30,10);

동의어

• 테이블, 뷰, 시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체
• 주로 테이블 이름이 너무 길어서 사용이 불편할 때 좀 더 간단하고 짧은 이름을 하나 더 부여해주기 위해서 사용

[동의어 생성]

⭐ 먼저, 동의어를 생성할 수 있는 권한을 사용자에게 SYSTEM 계정에서 부여해주어야 한다!

GRANT CREATE SYNONYM TO 계정명;
GRANT DROP ANY SYNONYM TO 계정명;--삭제 권한도 주어져야 함!

이제, 동의어를 생성할 수 있다!

CREATE [PUBLIC] SYNONYM 동의어_이름
FOR [사용자.][객체이름];

1. PUBLIC : 동의어를 데이터베이스 내 모든 사용자가 사용할 수 있도록 설정 [생략 시, 동의어를 생성한 사용자만 사용 가능] -중요한 점은, PUBLIC 으로 생성되어도, 본래 객체의 사용 권한이 있어야 사용할 수 있다!

GRANT CREATE PUBLIC SYNONYM TO 계정명;
GRANT DROP PUBLIC SYNONYM TO 계정명;

1. 사용자. 객체이름: 사용자는 생성할 동의어의 본래 객체 소유 사용자를 지정하는 것인데, 이를 생략할 경우 현재 접속한 사용자가 지정됨

▶️ 이렇게 생성된 동의어는 DQL, DDL, DML 등 다양한 SQL에서 사용할 수 있다!

예를 들어서 아래처럼 기존 EMP 테이블에 대해서 PUBLIC 동의어를 만들면, SELECT를 이용해서 다른 계정에서도 해당 테이블을 확인해볼 수 있다!

CREATE PUBLIC SYNONYM SYN_EMP
FOR EMP;

SELECT * FROM SYN_EMP;

🌺 동의어 삭제

DROP SYNONYM 동의어_이름;

• 동의어를 삭제하면, 동의어_이름으로 SELECT를 할 수는 없지만, 원본 테이블에는 영향을 미치지 않을 수 있다!

PUBLIC SYNONYM은

DROP PUBLIC SYNONYM 동의어_이름;

로 삭제해주어야 한다! 그리고 위에서 SYNONYM 삭제에 대한 권한을 부여하지 않는다면

“SQL Error [1434] [72000]: ORA-01434: private synonym to be dropped does not exist” 등과 같은 오류에 직면할 수 있다!