🔍 ALTER TABLE
테이블의 스키마(Schema, 구조)를 변경하기 위한 SQL구문
- 테이블에 대한 변경사항이 추가구문(절)로 작성됨 👉🏻 코드 목적에 따라 키워드가 달라짐
➰ ADD
새로운 컬럼을 추가 👉🏻 추가된 컬럼은 마지막 컬럼으로 들어감
ALTER TABLE alter_table
ADD (phone VARCHAR2(11));➰ MODIFY
기존 컬럼의 데이터타입을 변경
- 해당 컬럼에 값이 존재할 때 NOT NULL로 변경 불가
- 컬럼에 데이터가 한개라도 존재하면 데이터 타입을 변경할 수 없음
- 데이터가 존재할 때, 같은 데이터타입으로 크게 크기변경 가능 👉🏻 가장 큰 데이터보다 작게 변경은 불가
- 데이터 타입과 함께 크기도 변경할 수 있음 👉🏻 크기만 변경도 가능
- CHAR와 VARCHAR2는 데이터가 존재해도 서로 변경 가능
-- 기존 컬럼의 데이터 타입 변경하기 : phone VARCHAR2(11) -> phone VARCHAR2(100)
ALTER TABLE alter_table
MODIFY (phone VARCHAR2(100));
-- 기존 컬럼에 NOT NULL 속성 부여
ALTER TABLE alter_table
MODIFY (empno NOT NULL);➰ SET UNUSED
컬럼을 비활성화 함
- 컬럼과 데이터를 물리적으로 지우는 것이 아니라 사용할 수 없게 만드는 것
❗ 단, 복구되지도 않음 - 테이블의 컬럼을 삭제할 때 테이블에
락(LOCK)이 걸림 - 서비스 중인 DB의 데이터를 함부로 DROP하지 않도록 함
user_unused_col_tabs: 비활성화 컬럼 자료사전 👉🏻 상세항목은 없고 비활성화 컬럼 개수만 적혀있음
ALTER TABLE alter_table
SET UNUSED (deptno);📑 DB LOCK
- ROW LOCK / TABLE LOCK / DB LOCK
- 특정 사용자에게만 해당 LOCK영역에 대한 접근을 허용하는 것 👉🏻 사용자 이외에는 SELECT 조차 안됨
- 동시에 여러 사용자가 데이터처리하지 못하도록 막는 개념
- 트랜잭션은 변경사항에 대해 ROW LOCK이 발생
➰ DROP
<기존 컬럼 삭제>
-- phone 컬럼 삭제하기
ALTER TABLE alter_table
DROP COLUMN phone;
<비활성화 해둔 컬럼을 물리적으로 삭제>
ALTER TABLE alter_table
DROP UNUSED COLUMNS;➰ RENAME
<테이블 이름 변경>
ALTER TABLE alter_table
RENAME TO alter_tb;
<컬럼 이름 변경>
ALTER TABLE alter_table
RENAME COLUMN ename TO deptno;➰ 제약사항 설정
- ADD CONSTRAINT : 테이블에 제약조건 추가
- DROP CONSTRAINT : 테이블의 제약조건을 제거
- MODIFY : 컬럼의 정보로 등록된 제약사항 변경 👉🏻
NOT NULL, DEFAULT
-- 테스트 테이블
CREATE TABLE alter_cons(
data VARCHAR2(10)
);
-- MODIFY
-- NOT NULL설정
ALTER TABLE alter_cons
MODIFY (data NOT NULL); --CHECK 제약사항 생김
--MODIFY (data NULL);
-- DEFAULT 설정
ALTER TABLE alter_cons
MODIFY (data DEFAULT 'HI');
-- DEFUALT 설정 제거 -> 디폴트로 null값 부여
ALTER TABLE alter_cons
MODIFY (data DEFAULT null);
-- ADD
-- UNIQUE 설정
ALTER TABLE alter_cons
ADD CONSTRAINT uk_data UNIQUE(data);
-- CHECK 설정
ALTER TABLE alter_cons
ADD CONSTRAINT ck_data CHECK(data >='a' AND data <= 'b');
-- PRIMARY KEY 설정
ALTER TABLE alter_cons
ADD CONSTRAINT pk_data PRIMARY KEY(data);
-- FOREIGN KEY 설정
ALTER TABLE fk_cons
ADD CONSTRAINT fk_data
FOREIGN KEY(fk_data)
REFERENCES alter_cons(data);
-- DROP
-- UNIQUE 설정 제거
ALTER TABLE alter_cons
DROP CONSTRAINT uk_data;
-- CHECK 설정 제거
ALTER TABLE alter_cons
DROP CONSTRAINT ck_data;
-- PRIMARY KEY 설정 제거
ALTER TABLE alter_cons
DROP CONSTRAINT pk_data;
-- FOREIGN KEY 설정 제거
ALTER TABLE fk_cons
DROP CONSTRAINT fk_data;🔍 테이블 삭제
➰ DELETE
테이블의 데이터(내용물, 행)을 삭제
- 행을 지우고 난 후 용량이 줄어들지 않음
- DML ➡ 트랜잭션에 포함됨
➰ DROP TABLE
테이블의 자체를 삭제 👉🏻 스키마(구조)까지 포함하여 제약사항, 컬럼, 데이터 전부 삭제
- DDL ➡ 트랜잭션을 종료시킨 후 수행됨(Auto commit)
➰ TRUNCATE TABLE
테이블의 데이터(내용물, 행)만 삭제 👉🏻 스키마(구조)는 유지
- 데이터를 지우고 난 후 용량이 줄어듬
- DDL ➡ 트랜잭션을 종료시키고 Auto commit함
📑 데이터 베이스 객체(Database Object)
테이블, Table / 사용자 계정, User / 인덱스, Index / 뷰, View / 시퀀스, Sequence
🔍 사용자 계정, User
CREATE 하기 위해서는 system(관리자)계정으로 로그인 해야 함 👉🏻 관리자 접속 만들기
➰ 관리자 계정
DBA계정, Database Administrator
- 데이터베이스의 모든 객체들의 생성과 관리를 담당하는 계정 👉🏻 모든 권한, 모든 책임을 가지는 계정
- 오라클 DB에서는
SYS, SYSTEMS계정을 기본으로 제공SYS- DB생성권한을 가지고 있음, 자료사전의 소유자(OWNER)SYSTEM- DB생성 권한이 없음
➰ 일반 사용자 계정
DB객체들에 대한 관리작업을 수행하는 일반 계정
- 소유자로써 자신이 생성한 객체들을 위주로 작업함 👉🏻 최소한의 권한을 유지하는 것이 원칙
- 추가 권한을 받아서 다른 소유자의 객체를 다루기도 함
- 테스트용으로 제공되는 일반 사용자 계정 :
scott, hr
CREATE USER sample IDENTIFIED BY apple; --아직 아무 권한이 없음
--DROP USER 구문에 CASCADE옵션을 적용하면 계정에 종속된 모든 객체도 함께 삭제됨
DROP USER sample CASCADE;
-- 처음 생성된 사용자 계정은 아무 권한도 없음 / DB접속 권한(CREATE SESSION)도 없음
-- 기본 권한 부여 -> system 이용
GRANT
RESOURCE --테이블 관리 권한
, CONNECT --DB 접속 권한
TO sample;🔍 인덱스, Index
테이블의 탐색(검색) 속도를 향상시키기 위해 사용하는 DB객체
- 테이블의 컬럼 데이터를 기준으로 인덱스가 생성됨 👉🏻 테이블에 의존적임
- 별개의 공간에 독립적으로 생성됨
- 테이블을 조회하면 인덱스를 먼저 확인하여 빠르게 데이터를 찾고, 연결된 테이블의 실제 데이터를 확인할 수 있도록 도와줌
- Primary key(기본키), Unique Key(유일키) 제약사항이 적용되면 인덱스가 자동으로 생성됨
- 인덱스 자료사전
user_indexes: 인덱스 정보user_ind_columns: 인덱스가 적용된 컬럼 정보
➰ 인덱스의 종류
- Non-Unique Index : 중복값을 허용하는 인덱스
CREATE INDEX idx_salgrade
ON salgrade(grade);
INSERT INTO salgrade
VALUES( 5, 7777, 8888); --중복값 허용이라 여러번 들어감
-- 인덱스 삭제
DROP INDEX idX_salgrade;- Unique Index : 중복값을 허용하지 않는 인덱스
CREATE UNIQUE INDEX idx_salgrade --salgrade 테이블에 중복된 데이터들이 있어 안됨
ON salgrade(grade);
SELECT * FROM salgrade
--DELETE salgrade
WHERE grade = 5 AND losal = 7777; --중복된 데이터 삭제
-- unique 특성때문에 삽입 불가
INSERT INTO salgrade
VALUES( 5, 7777, 8888);- Composite Index : 복합 컬럼 인덱스
여러 개의 컬럼을 한번에 묶어서 인덱스로 저장 👉🏻 컬럼을 나열한 순서대로 탐색에 이용할 때 인덱스가 적용됨
-- 조건으로 2개 이상의 컬럼이 같이 조회되는 경우 -> 복합인덱스 필요
CREATE INDEX idx_emp
ON emp (empno, ename);➰ 인덱스의 장단점
-
장점
- 테이블에 대한 전체탐색(Full Scan) 횟수를 줄여줌
-
단점
- 인덱스 객체를 생성하는데 시간과 별도의 공간이 필요
- 검색 기능의 성능은 향상시키지만 추가(INSERT), 수정(UPDATE), 삭제(DELETE)에는 도움이 안됨 👉🏻 오히려 성능을 떨어뜨려 방해가 됨
- 추가, 수정, 삭제가 발생하면 인덱스를 재구성해야 함 👉🏻 인덱스를 생성한 후에 테이블에 변화(INSERT, UPDATE, DELETE)가 있더라도 인덱스에 반영되지 않음
-- 인덱스 재구성(재생성)
ALTER INDEX idx_salgrade REBUILD;- 인덱스 생성이 불필요한 경우
- 데이터가 적을 때 👉🏻 수 천 건 이하
- 테이블에 SELECT보다 INSERT, UPDATE, DELETE가 빈번한 경우
- 인덱스 탐색으로 조회되는 결과가 전체 행의 상당 부분을 차지하는 경우(약 15%정도)
🔍 시퀀스, Sequence
연속적인 숫자를 생성하는 객체
- 정수값으로 생성 👉🏻 기본 숫자 범위는 1~무한대까지 1부터 1씩 증가하는 값 생성
- 테이블마다 사용할 유일한 숫자를 자동으로 생성할 때 사용하는 객체 👉🏻 PRIMARY KEY 컬럼의 값(데이터)으로 사용
- 테이블마다 각각의 시퀀스 객체를 만들어서 사용
- 시퀀스 객체 사용하는 변수(키워드)
.currval: 현재 시퀀스 값 확인 👉🏻 nextval을 한 번이라도 해야 실행 가능.nextval: 다음 시퀀스 값 확인
- 옵션 추가 구문
START WITH n: 시작값 설정
INCREMENT BY n: 증가단위 설정
MAXVALUE n | NOMAXVALUE: 최대값 설정
MINVALUE n | NOMINVALUE: 최소값 설정 👉🏻 start의 값보다 작거나 같아야 함
CYCLE | NOCYCLE: 순환구조 여부
CACHE | NOCACHE: 미리 생성할 숫자의 개수 (캐시의 개수)- 1CYLCLE의 개수가 CACHE보다 커야 CYCLE을 설정할 수 있음
- CYCLE의 기본값 : 10 / CACHE의 기본값 : 20
- 시퀀스의 변경은 위의 추가옵션 중 start with를 제외하고 모두 적용(변경) 가능
- 시퀀스 관련 자료사전 :
user_sequences
-- 시퀀스 자료사전
SELECT * FROM user_sequences;
-- 시퀀스 생성
CREATE SEQUENCE seq;
-- 시퀀스의 현재 값
SELECT seq.currval FROM dual; 👉🏻 currval은 한 번이라도 사용된 적이 있어야 조회 가능
-- 시퀀스의 다음 값
SELECT seq.nextval FROM dual;
-- 시퀀스 값 사용해서 INSERT 하기
INSERT INTO emp(empno, ename)
VALUES (seq.nextval, 'TEST'); --시퀀스를 사용하면 중복값을 체크하기 쉬움
-- 시퀀스 삭제
DROP SEQUENCE seq;
-- 옵션을 부여해서 시퀀스 생성
CREATE SEQUENCE seq
START WITH 2001 --시작값
INCREMENT BY 100 --증가값
MINVALUE 2001 --최소값
MAXVALUE 3000; --최대값
-- CACHE 5개로 저장해서 1CYCLE보다 적어지도록 설정
ALTER SEQUENCE seq
CYCLE
CACHE 5;🔍 뷰, VIEW
복잡한 쿼리를 간단한 이름으로 지정해서 사용하는 것
❗ 일반 사용자 계정은 view 객체 생성권한이 없음! system 계정 이용해 권한부여 필요!
❗ 뷰를 이용한 데이터 삽입에서 삽입된 데이터가 저장되는 곳은 원본테이블
- VIEW 객체는 SELECT 쿼리를 저장하고 있음 👉🏻 뷰 객체가 서브쿼리처럼 동작(인라인 뷰)
✔ 자료사전들은 대부분 VIEW 객체임 - 부가적인 효과
- 조회가능한 컬럼이나 조건이 적용된 행을 제한할 수 있음
- 원본 데이터의 노출을 최소화할 수 있음
- 뷰 관련 자료사전
user_views - 뷰 생성구문
CREATE OR REPLACE VIEW➡ 뷰를 생성하거나 이미 존재하는 뷰라면 쿼리를 수정
👉🏻 판단 기준은 뷰 이름 - 옵션 추가 구문
WITH CHECK OPTION: AS 절의 서브쿼리에 적용된 CONSTRAINT 제약조건 또는 WHERE 조건절에 맞는 데이터만 뷰를 이용하여 다룰 수 있도록 설정WITH READ ONLY: 조회만 가능한 뷰로 설정하는 옵션
-- system 계정으로 scott에게 권한 부여
GRANT
CREATE VIEW
TO
scott;
-- 뷰 생성
CREATE OR REPLACE VIEW test_view
AS (
SELECT * FROM emp
WHERE empno > 7800
);
-- 뷰를 이용한 데이터 삽입 👉🏻 원본테이블에 삽입 및 저장됨
INSERT INTO test_view(empno, ename)
VALUES (9152, 'DAVE');
-- WITH CHECK OPTION
CREATE OR REPLACE VIEW test_view
AS (
SELECT * FROM emp
WHERE empno > 7800
)
WITH CHECK OPTION;
-- 에러, WITH CHECK OPTION으로 서브쿼리의 WHERE절 검사
INSERT INTO test_view (empno, ename)
VALUES (7000, 'TEST');
-- WITH READ ONLY
CREATE OR REPLACE VIEW test_view
AS (
SELECT * FROM emp
WHERE empno > 7800
)
WITH READ ONLY;
-- 에러, 읽기 전용 뷰에는 DML을 수행할 수 없음
INSERT INTO test_view(empno, ename)
VALUES(9060, 'GG');
IT꿈나무 댓츠미