인덱스란?
색인
원하는 값을 빠르게 찾을 수 있다.
인덱스는 테이블과 맵핑되어 있다. 맵핑된 곳으로 가서 나머지 데이터를 꺼내오는 방식
인덱스 컬럼을 기준으로 정렬되어 저장된다 -> 특정 데이터를 조회 시 시작점을 지정해서 검색할 수 있다.
보통 테이블은 데이터가 물리적으로 흩어져 저장되어있다. -> 인덱스 없이 특정 데이터를 조회하려면 테이블을 full scan 하여 값을 찾아온다.
어떤 컬럼을 인덱스로 지정하는게 좋을까?
where절에 자주 등장하는 컬럼 - 조건에 대한 데이터를 찾을 때 효율적이다라고 했다.
order by 절에 자주 등장하는 컬럼 - 인덱스는 소팅되어 저장되기 때문에 따로 또 정렬할 필요가 없어진다.
select 절에 자주 등장하는 컬럼 - 인덱스는 단일컬럼뿐 아니라 여러 컬럼을 조합해서 결합 인덱스로도 구성이 가능하다.
참고) 카디널리티
그룹내 요소의 갯수
컬럼의 카디널리티가 높을 수록 해당 컬럼의 중복되는 값이 적다
그렇게 좋으면 인덱스를 많이 만들어 두면 좋겠네
마구잡이로 생성하면 안돼
select는 빨라질지 몰라도 insert, update, delete 할때는 성능이 저하 될 수 있다. -> 인덱스는 정렬되어 저장하기 때문
인덱스가 있다고 항상 인덱스를 타는건 아니야
인덱스 컬럼을 가공한 경우
WHERE SUBSTR(ORDER_NO, 1, 4) = '2023' > WHERE ORDER_NO LIKE '2023%'
인덱스 컬럼의 묵시적 형변환 (같은 타입으로 비교해야 한다)
WHERE REG_DATE = '20230201' > WHERE REG_DATE = TO_DATE('20230201', 'YYYYMMDD')
인덱스 컬럼 부정형 비교
WHERE MEMBER_TYPE != '10' > WHERE MEMBER_TYPE IN ('20', '30')
LIKE 연산자 사용시 '%' 가 앞에 위치한 경우
OR조건사용 > UNION ALL 대체
인덱스 스캔 방식
인덱스 풀스캔
인덱스 레인지 스캔
인덱스 스킵 스캔
인덱스 패스트 풀 스캔 등
참고하면 좋겠다
인덱스를 탄다고 무조건 좋은 것은 아니다
인덱스 손익분기점
테이블 전체 데이터의 10-15%의 데이터가 출력되는 경우 인덱스를 타는게 유리하고 그 외에는 테이블 풀스캔이 더 빠르다.
오라클 힌트를 남발하지 말자
옵티마이저가 최적의 실행계획을 타도록 설계되어있기 때문에 인덱스를 타는게 유리하면 알아서 타게 되어있다.
단, 테이블에 너무 많은 인덱스가 있어서 옵티마이저가 헷갈리지 않게 이 인덱스를 타라고 알려줄 순 있다.
오라클 실행계획 읽기
ALTER SESSION SET STATISTICS_LEVEL = 'ALL';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
가장 안쪽에서 부터 바깥쪽으로, 위에서 아래로
E-ROWS 예상 데이터 건수
A-ROWS 실제 데이터 건수
BUFFERS 각 오퍼레이션이 메모리에서 읽은 BLOCK의 수
FULL SCAN > INDEX REANGE SCAN
조인문에 대한 실행 계획
드라이빙(밖)/드라이븐(안) 이중 포문
조인의 종류
NESTED LOOP JOIN
중첩 for문
대량의 테이블 조인에는 적절하지 않아.
outer table - inner table (이너 테이블의 조인컬럼이 인덱스에 설정되어 있지 않으면 비효율이 발생(돌때마다 풀스캔 해야되서), 이너테이블의 인덱스 구성 전략이 매우 중요한 역할을 한다.)
SORT MERGE JOIN
NESTED LOOP JOIN와 비슷하다
양쪽 테이블을 조인 컬럼을 기준으로 먼저 소팅을 한다.
이너테이블에 인덱스가 없는 경우 사용하면 좋다.
PGA영역에서 소팅이 수행된다.
PGA 영역이란?
오라클 서버에 접속하는 모든 유저에게 각각 할당되는 오라클 메모리 영역
사용자가 오라클에 접속 > 사용자 프로세스가 생성 > 사용자 프로세스는 사용자가 작성한 SQL문을 서버 프로세스에게 넘기고 결과를 기다린다. 서버 프로세스는 오라클과 상호작용하며 결과를 만들어낸다. 만들어진 결과를 사용자 프로세스에게 넘겨주고 사용자는 SQL 결과 값을 얻게된다.
PGA공간은 서버프로세스가 사용하는 메모리공간
PGA에는 소트영역, 해시영역, 유저 세션정보를 저장하는 영역 등이 있는데 PGA의 소트영역에서 소팅이 수행되면 성능이 빠르지만 이를 넘어가면 디스크에서 작업이 수행되기 때문에 성능이 느려진다.
HASH 조인
대량의 테이블 조인에 적절하다.
드라이빙 테이블의 조인컬럼값을 기준으로 해시 펑션이 적용된다.
데이터가 저장되는 놀리적인구조
BLOCK
EXTENT
SEGMENT
TABLESPACE
BLOCK
데이터가 저장되는 가장 작은 공간
SELECT를 할 때 I/O를 해야하는 가장 기본적인 단위
데이터를 하나 꺼낼때 컬럼 1개를 SELECT 하든 여러개를 하든 IO하는 블럭의 수는 동일하다.
EXTENT
BLOCK의 집합
데이터베이스가 할당하는 가장 작은 단위
SEGMENT
EXTENT의 집합
OBJECT라고 생각하면된다. 테이블이나 인덱스 처럼 저장구조를 갖는 오브젝트
hwm 하이워터마크 - 책갈피, 세그먼트에서 사용한 공간을 표시해두는 것 > 풀스캔할 때 스캔하는 공간의 마지노선
TABLESPACE
세그먼트를 포괄하고있는 큰 공간
SELECT문의 실행 순서
5 SELECT
1 FROM
2 WHERE
3 GROUP BY
4 HAVING
6 ORDER BY
FROM 절에 지정한 테이블이 실제 존재하는지 그리고 SELECT권한이 있는지 확인을 합니다.
WHERE절에 내려와서 어떤 조건으로 데이터를 가져올지 데이터를 골라내고
GROUP BY에서 골라낸 데이터를 어떤식으로 그룹바이할지 실행한 후에
HAVING절에서 내가 그룹핑 한 것들 중에 버릴건 없는지 실행하게 됩니다.
그리고나서 SELECT절에가서 어떤 컬럼들이 필요한지 확인을 하게되고
마지막으로 내가 뽑아온 컬럼들을 기준으로 어떻게 정렬할지를 실행하게 됩니다.
- SELECT에서 하나의 컬럼을 가지고오던 *로 해서 모든 컬럼을 가지고오던 IO되는 블럭의 수는 동일하다.
- ORDER BY가 마지막에 수행되기때문에 SELECT절에서 alias를 준것을 사용할 수 있다.
서브쿼리
스칼라 서브쿼리 - 실행계획 읽는 순서에 예외가 있다. 하나의 값만 리턴한다. 캐싱기술이 있다.
select 절에서 사용
인라인뷰
from절에서 사용
중첩서브쿼리
where절에서 사용
erd 설명
테이블 구조
쿼리 몇가지
로그인
union all/ 페이징 처리
간단한 조인
rank 함수
서브쿼리
거래정산테이블
데이터 추출, ias, kps 조인해서