[오라클(ORACLE)/SQLDeveloper] 인덱스 생성 부터.. 공부!

MG_세대 2023. 6. 21. 15:21

인덱스

B-tree+ 구조로 많이 보관한다.

인덱스를 만들 테이블을 만들어야 하기때문에 용량을 차지하는 단점이 있다.

수정 및 삭제가 좀 불편하다.

Primary Key는 자동 정렬이 되어있다.

내가 임의로만든 테이블에서 한번 데이터 조회를 해보았다.

select id,score from Score
where 80<score;

우선 인덱스를 안만들어도 이렇게 잘 나오는데!

인덱스를 따로 생성하는 이유는 데이터가 만약 1억개가 있다고 가정하면

1억개의 행을 다~ 확인을 해서 원하는 데이터를 보여줘야 하기때문에 조회 및 검색 하는데 너무 비효율 적이기 때문에

인덱스를 따로 생성해서 그 컴퓨터가 찾기 편하게 나름의 구간을 정해 놓는다라고 이해하였다.

데이터양이 너무 작지만 그래도 만들어보고 안만들어보고 차이가 있으므로 인덱스를 생성 해 보았다.

1. 인덱스 생성

CREATE INDEX LA_SA ON EMPLOYEES(LAST_NAME, SALARY);

위와 같이 내가 설정한 인덱스가 만들어 졌다.

2. 생성된 인덱스에서 값 찾아보기

SELECT /*+INDEX(EMPLOYEES LA_SA)*/ * FROM EMPLOYEES WHERE LAST_NAME = 'King';

노란색으로 된 문장은 힌트를 사용하는거라고한다.. 힌트가 뭔지 사실 잘 모른다.

라스트 네임이 King인 사람을 찾아보았다.

근데 이게 내가 만든 인덱스에서 뽑아온건지 아니면 그냥 EMPLOYEES테이블에서 들고온건지 알 수가 없는데

내가 설정한 인덱스값은 LAST_NAME, SALARY 인데? 아마도 그냥 EMPLOYEES테이블에서 들거온거라 생각하는데...

아무리 인덱스를 이용하면 내가 설정한 값 LAST_NAME, SALARY 이것만 나올 줄 알았는데 그게 아닌가 보다...

3. 데이터베이스 실행 계획 표시

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

위 구문은 정확히 뭐하는건지 모르겠지만

내 인덱스가 사용되어서 검색이 되었다는걸 알려준는 것 같다.

위 구문에대해서 GPT에 물어보니 다음과 같이 대답해주었다.

이 구문은 Oracle 데이터베이스에서 실행 계획을 표시하는 데 사용되는 DBMS_XPLAN 패키지의 DISPLAY_CURSOR 프로시저를 호출하는 SQL 문입니다. 이 구문은 현재 커서에 대한 실행 계획을 모두 표시하고, 통계 정보도 함께 표시합니다.

구체적으로 말하면, `DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')`는 `NULL`로 지정된 두 개의 매개변수와 `'ALLSTATS LAST'`로 지정된 세 번째 매개변수를 사용하여 DISPLAY_CURSOR 프로시저를 호출합니다. 이렇게 호출하면 실행 계획의 모든 세부 정보와 마지막으로 수집된 통계 정보가 표시됩니다.

실행 계획은 SQL 문이 데이터베이스에서 실행될 때 데이터베이스 옵티마이저에 의해 작성된 계획으로, 데이터 액세스 및 조인 방법 등을 포함합니다. DISPLAY_CURSOR를 사용하여 실행 계획을 확인하면 쿼리의 성능을 분석하고 최적화하는 데 도움이 됩니다.

내가 이해한 내용은 대충 들어있는듯 하다..

4. (3.)구문이 실행이 안될 때가있다. 권한 어쩌구저쩌구하는데.. 그건

커맨드창에서 sqlplus /as sysdba 로 시스템계정으로 들어가

GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO (ID);
GRANT SELECT ON V_$SESSION TO (ID);
GRANT SELECT ON V_$SQL TO (ID);

위 의 명령어로 권한을 부여해주면된다.

실제 업무중에 INDEX를 사용했을 때와 비교를 하고싶으나 ... 나중에는 해볼 수 있을꺼라 믿어 의심치 않는다.