SQLD 자격증 공부 8 일차 (Execute Immediate / Stored Module / 트리거(Trigger) / CBO / 규칙기반 옵티마이저 / 실행계획 읽는 순서

EXECUTE IMMEDIATE

• PL / SQL 에서 테이블의 모든 데이터를 ROLLBACK이 불가능하도록 삭제 함

---

STORED MOCULE(ex : PL / SQL, PL/SQL, T-SQL)로 구현 가능한 기능

• 저장형 프로시져는 SQL을 로직과 함께 데이터베이스 내에 저장해 놓는 명령문의 집합을 의미
• 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기 보다는 다른 SQL문을 통하여 호출되고 그 결과를 리턴하는 SQL의 보조적인 역할
• 트리거는 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML 문이 수행되었을 때 데이터베이스에서 자동으로 동작하도록 작성된 프로그램

 

---

트리거(TRIGGER)

• 데이터베이스에 의해서 자동 호출 수행
• 특정 테이블에 대해서 INSERT, UPDATE, DELETE문이 수행되었을 때 호출되도록 정의
• COMMIT, ROLLBACK 같은 TCL 사용 못함
• 데이터베이스에 로그인하는 작업에도 정의 가능
• 트리거는 테이블 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 TRIGGER와 각 행에 대해서 발생되는 TRIGGER가 있따

 

※ 데이터의 무결성과 일관성을 위해서 사용자 정의 함수를 사용하는 것은 TRIGGER의 용도

---

CBO (Cost Based Optimizer) , 비용기반 옵티마이저

• 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL 문을 실행하는데 소요될 처리시간 및 CPU, I/O자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저

 

---

실행계획을 읽는 순서

• 위에서 아래로 -> 안에서 밖으로 순으로 읽는다
• 실행계획은 실행계획이 달라도 결과가 달라지지는 않는다
• SQL 처리를 위한 실행 절차와 방법을 표현한 것이다.
• 조인방법, 조인순서, 액세서 기법 등이 표현
• CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표시

---

규칙기반 옵티마이저

• 제일 낮은 우선순위는 전체 테이블 스캔
• 제일 높은 우선순위는 ROWID를 활용하여 테이블을 엑세스 하는 방법이다.
• SQL 처리 흐름도는 SQL문의 처리 절차를 시각적으로 표현한 것으로, 인덱스 스캔 및 전체 테이블 스캔 등의 엑세스 기법을 표현할 수 있으며, 성능적으로 측면도 표현 할 수 있다.
• 인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않는다.

 

---

 

관계형 데이터베이스 인덱스(INDEX)

• 테이블의 전체 데이터를 읽는 경우는 인덱스를 사용하지 않는 FTS를 사용
• 인덱스는 조회만을 위한 오브젝트이며, 삽입, 삭제, 갱신의 경우 오히려 부하를 가증한다.
• Balance Tree는 관계형 데이터베이스에서 가장 많이 사용되는 인덱스이다.
• 인덱스 존재하는 상황에서 데이터를 입력하면, 매번 인덱스 정렬이 일어나므로 데이터 마이그레이션 같이 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋다.

---

B - TREE 인데스

• 인덱스 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다. 일반적으로 OLTP 시스템 환경에서 가장 많이 사용된다.

CLUSTERED 인덱스

• 인덱스는 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는 인덱스키 컬럼 순으로 물리적으로 정려되어 저장한다.

BITMAP 인덱스

• 인덱스는 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC질의 환경을 위해서 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조이다.

---

인덱스 설명

• 인덱스 구성 칼럼으로는 정렬 순서 설정에 따라 달라진다.
• 비용기반 옵티마이저는 SQL을 수행하는데 있어 소요되는 비용을 계산하여 실행계획을 생성하므로 인덱스가 존재하더라도 전체 테이블 스캔이 유리하다고 판단할 수도 있따.
• 규칙기반 옵티마이저의 규칙에 따라 적절한 인덱스가 존재하면 전체 테이블 스캔보다는 항상 인덱스를 사용하려고 한다.
• 인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있다.
• 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식이 랜덤엑세스인데, 이러한 랜덤 엑세스의 부하가 크기 때문에 매우 많은 양의 데이터를 읽는 경우에는 인덱스 스캔보다 테이블 전체 스캔이 유리할 수도 있다.
• 인덱스의 목적은 조회 성능을 최적화 하는 것이다.
• INSERT, UPDATE, DELTE 등의 DML 처리 성능을 저하 실킬 수도 있다.
• B-TREE 인덱스는 일치 및 범위 검색에 적절한 구조이다.
• 대량의 데이터를 조회하는 경우 인덱스를 이용한 조회보다는 테이블 전체 스캔 방식으로 조회하는 것이 더 빠를 수도 있다.
• 인덱스를 구성하는 컬럼들의 순서는 데이터 조회 시 성능적인 관점에서 매우 중요한 역할을 한다.
• 인덱스를 구성하는 컬럼 이외의 데이터가 UPDATE될 때는 인덱스로 인한 부하가 발생하지 않는다.
• 인덱스는 데이터 조회 목적에는 효과적이지만 INSERT, UPDATE, DELETE 작업에는 오히려 많은 부하를 줄 수도 있다.
• SQL Server의 클러스터형 인덱스는 ORCACLE의 IOT와 매우 유사하다.
• 인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE작업에는 부하가 없을 수도 있다 .