SQL 기본 및 활용

▶ SELF JOIN을 수행해야 할 경우

→ 한 테이블 내에서 두 칼럼이 연관관계가 있다.

 

셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다.

 

 

▶  PL/SQL의 특징

• PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다.
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• PL/SQL은 응용 프로그램의 성능을 향상 시킨다.
• PL/SQL은 여러 SQL문장을 BLOCK으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.
• 변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE 절의 조건 등으로 대입할 수 있다.
• Procedure, User Defined Funtion, Trigger 객체를 PL/SQL로 작성할 수 있다.
• Procedure 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고 일반적인 SQL 문장은 SQL 실행기가 처리한다.
• PL/SQL로 작성된 Procedure, User Defined Function은 작성자의 기준으로 트랜잭션을 분할할 수 있으며, 또한 프로시저 내에서 다른 프로시저를 호출할 경우에 호출 프로시저의 트랜잭션과는 별도로 PRAGMA AUTONOMOUS_TRANSACTION을 선언하여 자율 트랜잭션 처리를 할 수 있다.
• PL/SQL에서는 동적 SQL 또는 DDL 문장을 실행할 때 EXECUTE IMMEDIATE를 사용하여야 한다.

 

 

▶ 저장 모듈(Stored Module)

: SQL 문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다. Oracle의 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다.

 

 

▶ Trigger에 대한 설명

• Trigger는 데이터베이스에 의해서 자동으로 호출되고 수행된다.
• Trigger는 특정 테이블에 대해서 INSERT, UPDATE, DELETE 문이 수행되었을 때 호출되도록 정의할 수 있다.
• Trigger는 데이터베이스에 로그인하는 작업에도 정의할 수 있다.
• Trigger는 Procedure와 달리 Commit 및 Rollback과 같은 TCL을 사용할 수 없다.

 

 

 

▶ INDEX

• B-TREE 인덱스는 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬된다. 일반적으로 OLTP 시스템 환경에서 가장 많이 사용된다.
• CLUSTERED 인덱스는 인덱스의 리프 페이지가 곧 데이터 페이지이며, 리프 페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장된다.
• BITMAP 인덱스는 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조이다.
• 규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려한다.
• 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있다.
• 인덱스의 목적은 조회 성능을 최적화하는 것이다.
• Insert, Update, Delete 등의 DML 처리 성능을 저하시킬 수도 있다.
• B-트리 인덱스는 일치 및 범위 검색에 적절한 구조이다.
• 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식이 랜덤 엑세스인데, 이러한 랜덤 엑세스의 부하가 크기 때문에 매우 많은 양의 데이터를 읽을 경우에는 인덱스 스캔보다 테이블 전체 스캔이 유리할 수도 있다.
• 인덱스는 데이터 조회 목적에는 효과적이지만, INSERT, UPDATE, DELETE 작업에는 오히려 많은 부하를 줄 수도 있다.
• SQL Server의 클러스터형 인덱스는 ORACLE의 IOT와 매우 유사하다.
• 인덱스는 INSERT와 DELETE 작업과는 다르게 UPDATE 작업에는 부하가 없을 수도 있다.

 

 

 

▶ 옵티마이저와 실행계획

• ORACLE의 규칙기반 옵티마이저에서 가장 우선 순위가 높은 규칙은 Single row by rowid 엑세스 기법이다.
• 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통계정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있다
• ORACLE의 실행계획에 나타나는 기본적인 Join 기법으로는 NL Join, Hash Join, Sort Merge Join 등이 있다.
• NL Join은 데이터를 집계하는 업무 보다는 OLTP의 목록 처리 업무에 많이 사용된다. DW 등의 데이터 집계 업무에서 많이 사용되는 Join 기법은 Hash Join or Sort Merge Join이다.

 

 

 

▶ Nested Loop Join

• 조인 칼럼에 적당한 인덱스가 이어서 자연조인이 효율족일 때 유용하다
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식이다.
• 유니크 인덱스를 활용하여 실행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용

 

 

EXISTS는 주로 SEMI JOIN

 

 

 

▶ Sort Merge Join

• 조인 칼럼에 적당한 인덱스가 없어서 NL 조인이 비효율적 일 때 사용할 수 있다
• Driving Table의 개념이 중요하지 않은 조인 방식이다.
• 조인 조건의 인덱스의 유무에 영향 받지 않는다

 

▶ Hash Join은 한쪽 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적이다. 다음 중 해시조인이 더 효과적일 수 있는 조건에 대한 설명

• 조인 컬럼에 적당한 인덱스가 없어서 자연조인이 비효율적일 때
• 자연조인시 Driving 집합 쪽으로 조인 엑세스량이 많아 Random 액세스 부하가 심할 때
• Sort Merge Join을 하기에는 두 테이블이 너무 커서 Sort 부하가 심할 때