오라클 사용하면서 배운점 및 근황

허준현·2022년 9월 20일
1

Oracle

목록 보기
10/11
post-thumbnail

인덱스 컬럼에 대해 질의시 잘 사용하기

특정 쿼리가 인덱스를 타지 않아서 검색을 하던중 흥미로운 블로그 를 봐서 정리하고자 한다.
인덱스 컬럼을 조인할 때 해당 컬럼에 NOT 연산자 혹은 IS NULL 연산을 사용하면 인덱스 조회를 하는 것이 아닌 TABLE SCAN을 하는 것이다.
이 외에도 컬럼을 NVL,SUBSTR 와 같은 함수로 감싼다면 INDEX를 타지 않는다는 것이다.

IS NOT NULL, NOT

우선 인덱스 컬럼은 NOT NULL 컬럼에만 가능하기 때문에 IS NULL 에 대해서 타지 않는 다는 것은 이해하였다. 또한 NOT 연산은 특정 데이터가 아닌 데이터만 가져오기 때문에 전체 데이터 10~20% 가져오는 인덱스의 손익분기점에 맞지 않게 많이 가져오기 때문에 TFS 를 사용한다.

NVL, SUBSTR

처음에는 NVL, SUBSTR 을 사용하면 오라클에서 해당 컬럼의 인덱스를 인식못한다고 생각해서 FULL SCAN을 한다고 생각하였다. 하지만 회사 DB에 인덱스 컬럼에 함수를 사용해도 INDEX FULL SCAN을 사용하는 것이다.

테스트 및 후기

이는 함수를 사용하여 해당 컬럼을 감싸 B-TREE 구조에서 빠르게 가저오지 못하는 상황이며, MULTI-BLOCK IO를 사용하는 TABLE FULL SCAN을 사용하기엔 TABLE이 너무 큰 경우에는 INDEX FULL SCAN을 통해서 데이터를 가져오는 것으로 판단된다.

이 외에도 선행 인덱스 컬럼에는 SUBSTR을, 다음에 오는 인덱스 컬럼을 사용하게 되면 우리가 생각하는 인덱스의 구조에서 2번째 컬럼 기준으로 정렬된 값을 단순 INDEX를 타서는 가저오기 어려우므로 SKIP INDEX SCAN을 통해서 데이터를 가져오기도 한다.

따라서 무조건 해당 컬럼이 함수로 쌓여있거나 인덱스 순서에 맞지 않는다고 하여 인덱스를 안타는 것이 아닌 Optimizer가 적합한 판단을 하여 Scan 해서 가저오게 된다. 만일 화면단에서 TimeOut이 나거나 너무 느리다면 적절한 튜닝방법이 필요하다.

야간 작업을 하면서 DBA에게 들은 이모저모

데이터 전환을 하면서 DBA와 오라클 이야기 할 수 있는 좋은 기회를 얻을 수 있었다.

NO LOGGING 모드는 진짜 로깅을 안하는가

오라클에서는 dml작업을 하게 되면 로그파일에 저장하는데 NO LOGGING 모드에서도 로깅을 한다. 이는 LOGGING 모드 일때보다 최소한의 리두 로그를 기록하고 해당 테이블에 대해 작업을 해도 해당 데이터 BLOCK을 버퍼 캐시에 저장을 하지 않는다.

하지만 진짜 로그를 안하는 경우가 있는데 그 경우는 아래와 같다.
1. direct path 옵션을 준 경우
2. create table temp as select 문으로 테이블을 생성함과 동시에 데이터를 넣는 경우

INDEX를 UNUSABLE 상태로 두고 작업을 진행하면 좋지 않나?

앞서 APPEND 힌트절에 대해 기록했을 때 언급했던 내용이다.
작업시에는 인덱스에 대해 작업이 이루어지지 않지만 특정 명령어 (TRUNCATE) 시에는 인덱스를 사용하는 경우가 발생하여 이를 지우고 다시 재생성하는 것이 더 효율적이라고 들었다.
추가적으로 병렬작업은 업로드가 가능한 SESSION 여러개를 통해서 작업하는 것이며 파티션 테이블은 파티션 별로 병렬 처리가 가능하다.

오라클은 멀티 프로세스이다.

어떻게 보면 당연한 이야기이다. 앞 서 말한 것처럼 하나의 CLIENT에 대해서 PGA를 제공하기 때문이다. 이를 DEDICATE SERVER 라고도 부른다.
멀티 쓰레드의 DB로는 MS SERVER 가 있다.

다양한 window 함수와 집계함수 도출하기

전환작업을 진행하면서 이력 테이블의 컬럼값들을 하나의 row으로 만들어야 했다. 따라서 다음에 오는 값을 가져오는 쿼리를 작성하고 싶었는데 이를 눈치 챈듯 Oracle에서도 LEAD(다음 값 가져오기), LAG(이전 값 가져오기) 를 통해서 전환을 할 수 있었다.

쿼리를 작성할 시에 제일 많이 개발자를 괴롭히는 것이 null값이라고 생각한다. 집계함수를 통해서 값을 가져올 때 이 또한 개발자를 많이 괴롭히는데 해당 값이 null이면 Oracle 에서는 제외하고 계산을 하게된다. (count(*) 제외)
그래서 해당 null값을 어떻게 처리할 지를 고민해봐야 하는데 NVL(sum(column),0) 으로 처리하여 NULL값을 포함하지 않거나, sum(NVL(column,0)) 으로 null값을 포함시킬 수 있다.

NOT EXIST 와 NOT IN 을 처리할 때 :
이 또한 NULL이 개발자를 괴롭힌다고 생각한다. 결론적으로는 NOT IN 절에서 서브쿼리 결과값에 null(false)가 나온다면 아무결과를 얻을 수 없다. NOT EXIST 는 조인조건 값이 null인 경우 해당 결과값도 도출한다.

SELECT * FROM DBA_SEGMENTS;

빠르게 전환이 되어야 한데 전환 툴에서 옵션으로 parrallel 을 주면 현재 진행 상황을 알 수 없었다. DBA가 말씀하시기를 이때 SELECT * FROM DBA_SEGMENTS 를 사용하여 TEMPORARY 테이블에 어느정도 BLOCK이 있는지를 파악하여 일정 데이터 개수를 추측할 수 있었다.

DBA_SEGMENTS 와 DBA_TABLES 의 차이점🔛

우선 오라클에서 모든것이 Object 혹은 인스턴스라고 정의 한 적이 있다. 이처럼 Object 로는 table, index, view , sequnce 처럼 다양하게 존재한다. 이들 Object 중에서 저장공간을 갖는 것들을 SEGMENTS 라고 한다.

이 저장공간에 담는 것에 따라서 정의가 달라진다.

  • Data Segments -> table, table partition, cluster
  • Index Segments -> index-oraganised table, index partition

따라서 SEGMENTS는 TABLE을 포함하는 큰 개념으로 이해하였다.
다시초기로 돌아와서 해당 테이블에 데이터가 어느 정도 들어가 있는지 확인할때는 DBA_TABLES , DBA_SEGMENTS 중 어느 곳을 참고해야 할까?

DBA_TABLES 는 논리적인 구조를 보여주며 , DBA_SEGMENTS는 데이터 파일을 보여주는 물리적인 구조 성격을 가지고 있다.

또 한 TABLES는 DBMS_STATS를 실행 할 때 계산되는 반면에 SEGMENTS는 실제 디스크에 있는 BLOCK 수를 나타낸다.

작업하면서 혼동했던 용어 정리

serial# , SID👨‍🔧

최근에 Tera Stream을 사용하면서 해당 프로젝트를 중지하였으나 백그라운드에서 프로세스가 죽지않아 다시 Job을 실행하지 못하는 상태가 발생하였다.
먼저 다른 업무팀에서 해당 테이블에 작업을 할 수 있기 때문에 Lock을 조회하면서 SID, serial# 에 대한 용어를 접하게 되었다.
SID : 현재 데이터베이스에 접근한 세션
serial# : 해당 SID가 종료되고 이후 다른 클라이언트가 같은 SID 번호를 부여 받게 되었을 때 정확한 세션을 구분하기 위한 상수이다.

Sharding, Partitioning ✂

공부하다 샤딩과 파티셔닝에 대해 잘못 알고 있는 개념이 있어서 다시 확립하고자 한다.

샤딩과 파티셔닝 둘다 데이터를 나누는 것을 말하는데 샤딩은 나눈 몫을 여러 인스턴스에 나누어 저장하는 반면에 파티셔닝은 하나의 인스턴스의 여러 테이블에 저장하게 된다.

즉 위에서 말했던 것처럼 인스턴스는 하나의 sid를 말하면서 운영01, 운영02, 운영03 에 나누어서 넣는 것이 샤딩, 특정 db에 데이터를 나누어서 저장하는 것은 파티셔셔닝이다.

샤딩의 장점, 단점 : 여러개의 인스턴스에 나누어서 넣고 적절한 샤딩키를 통해서 모든 인스턴스를 검색하지 않아도 되지만, 그렇지 않은 경우 모든 인스턴스를 참조해 샤딩의 의미가 없어진다.

파티셔닝의 장점, 단점 : 날짜나 해시를 기준으로 테이블을 나누게 되어서 그만큼 index 부하가 적어지게 된다. 하지만 샤딩과 마찬가지로 나누는 기준을 잘못 나누게 되어 한쪽으로 데이터가 몰리게 된다면 성능상 문제를 야기하게 될 것이다.

추가적으로 까먹은 정규화, HA(hight Availabitity), RAC(Real Application Cluster) 를 다시 공부하며 엔티티, 스키마 같은 용어에 대해서 공부하면서 부족한 부분을 채워넣는 시간을 가졌다.

최근 근황😀

회사생활하면서 야간작업도 하고 업무파트 전환이 어느 정도 완료되어 다시 의료급여 파트로 넘어가게 되었다. 주말출근도 하면서 야근을 밥먹듯이 하다가도 시간을 짬내어서 공부하고 있다.
이 후 오라클 공부를 일단락 접어두고 스프링과 몽고db를 다시 공부하고 있다.
기초적인 지식만 알고 있는 부분도 있고, 까먹은 부분도 있어서 이 후 포스트에는 이 둘을 중심적으로 다루고자 한다. 이 외에도 Spring Batch를 올해 말까지 공부하는게 목표다.

profile
best of best

0개의 댓글