Oracle 심화

인덱스

디비 성능을 향상시키기 위한 매커니즘으로 인덱스라는 것을 사용하다. 인덱스는 테이블내의 행에 대한 빠른 임의 액세스를 허용하기 위해 사용되며 오라클에서 인덱스는 18자리의 ROWID와 컬럼 값으로 구성된다. PK와 Unique Key는 자동으로 인덱스가 생성되는 것을 기억하자

생성시 기준

인덱스 생성 시 고려해야 하는 사항

  • DML문의 WHERE절에 자주 사용하는 컬럼으로 만든다

  • Null 값이 많은 컬럼에 인덱스를 생성하면 실행속도가 빨라진다

  • WHERE 절에 의해 검색되는 데이터 분포가 테이블 전체의 약 10-15% 범위에 속할 때 인덱스가 생성

  • 테이블의 크기가 큰 경우에는 인덱스를 생성

  • 주민등록번호와 같이 넓게 분포된 컬럼에 인덱스를 생성한다

  • 불필요한 인덱스를 많이 생성하면, 실행속도가 지연되기 때문에 한 테이블의 3-4이내로 생성

  • 인덱스를 구성하는 컬럼이 16개를 초과할 수는 없다

  • 성별과 같이 좁게 분포된 컬럼에는 인덱스를 생성할 필요 없음

  • 테이블의 데이터가 자주 변경되는 경우에는 생성하지 않는다

인덱스 생성, 삭제

CREATE INDEX문을 사용해서 생성한다 

CREATE INDEX 인덱스이름 ON 테이블(인덱스에 걸릴 컬럼등, ...);

DROP INDEX문을 사용해서 인덱스를 삭제 

DROP INDEX 인덱스이름;

뷰

뷰는 하나 이상의 테이블의 쿼리에 기초한 저장된 쿼리이다. 뷰는 '뷰'라는 구조는 있으나 뷰 자체에 데이터를 저장하고 있지는 않기 때문에 가상 테이블이라고도 불리운다 이것을 사용하는 용도는

  • 테이블의 접근에 대한 권한을 제한

  • 개발자나 사용자에게 복잡성을 줄이고

  • 컬럼명을 변경해서 사용하고 보기 쉽게 쓸 때 사용

뷰에 대한 명령

뷰 생성

CREATE VIEW 뷰이름 [(컬럼1, 컬럼2, ...)] AS SELECT문;

일단 CREATE VIEW문은 SELECT문에 기술된 테이블이나 뷰 등에서 검색이 가능한 새로운 뷰의 객체를 생성해준다 고려사항으로는

  • 컬럼명을 생략하면 SELECT문의 SELECT절에 기술한 컬럼명으로 대체

  • 함수나 수식, 리터럴등의 사용되면 별명을 사용하거나, 컬럼명을 기술해야 한다

  • SELECT문에 사용하는 테이블의 기본키나 NOT NULL컬럼을 포함시켜야 데이터의 추가, 수정이 가능

뷰의 삭제는

DROP VIEW 뷰명;

뷰를 통해서 데이터의 트랜잭션이 가능하게 하기 위해서는

  • GROUP BY 절, DISTINCT, 그룹함수등을 사용한 뷰가 아니어야 한다

  • 하나의 테이블에서 생성된 뷰어야만 하다

  • 수식이 사용된 필드는 수정, 삭제할 수 없다

  • 기본 키, NOT NULL으로 설정된 컬럼이 모두 포함되어야 한다

시퀀스

시퀀스는 데이터베이스 객체로 시퀀스가 생성될 때, 설정된 규칙에 따라서 정수를 생성하게 된다. 시퀀스는 행을 식별하는 기본 키 값을 자동으로 생성하거나 난수 생성에 사용한다 기본 값은 1부터 시작해서 1씩 증가하고 최대 15개까지 만들 수 있다

시퀀스 생성

CREATE SEQUENCE 시퀀스명 
    [INCREMENT BY 시작값에서 증가할 증가값]
    [START WITH 시퀀스의 초기값]
    [MAXVALUE 시퀀스의 최대값]
    [CYCLE 최대값이 되었을 때 반복횟수]

사용하는걸 보면 

CREATE SEQUENCE ST_SEQ 2 START WITH 920;
--이렇게 시퀀스를 만들어두고, 
INSERT INTO Computer_Student(Student_ID, Dept_ID, Year, Name, ID_Number, Email)
    VALUES
        (CONCAT('C', LTRIM(TO_CHAR(ST_SEQ.NEXTVAL, '0999'))),
        '&학과', '&학년', '&성명', '&주민번호', '&메일주소');
--이렇게 값을 입력받는걸 해서 값을 입력받으면, STUDNET_ID에 C로 시작해서 920부터 1씩 올라가는 최대치가 0999인 시퀀스가 적용되서 값이 들어간다

시퀀스의 값을 반환하는 참조변수로 NEXTVAL, CURRVAL이 존재한다 시퀀스명.NEXTVAL : 시퀀스의 현재 값에 '증가값'을 더한 값을 정수로 리턴 시퀀스명.CURRVAL : 변수는 시퀀스의 현재 값을 정수로 리턴 해당 참조 변수는 INSERT문에서 VALUES, SELECT에서 선택할 수 있고, UPDATE문의 SET절에서 사용할 수 있으며 DISTINCT 절이 있는 SELECT, ORDER BY, GROUP BY가 있는 SELECT문, 서브 쿼리, 뷰 에서는 사용할 수 없다

시퀀스의 삭제는

DROP SEQUENCE 시퀀스명;

이렇게 진행된다

생성된 테이블의 관리

생성한 테이블이나 뷰와 같은 생성된 객체를 관리하기 위한 기능들이 있음

사용자의 객체명 출력

SELECT * FROM TAB;
SELECT * FROM USER_CATEALOG;

이렇게 명령어를 입력하면 오라클 사용자가 생성한 모든 객체들을 조회할 수 있다 -> 테이블, 뷰, 인덱스, 동의어등 모든 객체가 출력

테이블의 구조 출력

describe[desc] 테이블

이렇게 쓰면 생성된 테이블과 뷰의 구조를 출력해준다

테이블의 제약조건 출력

테이블의 정보를 볼 수 있는데 제약조건은 단지 NULLABLE에 대한 제약조건만 확인할 수 있지만 다른 명령어를 통해, 다른 모든 제약조건도 볼 수 있다는점! 그 정보는 USER_CONSTRAINTS 이라고하는 객체로부터 확인할 수 있다. 그리고 그것은 desc 명령어를 사용해서 조회하면 된다 USER_CONSTRAINTS의 주요 컬럼

  • OWNER : 객체를 생성한 오라클 사용자를 리턴

  • CONSTRAINTS_NAME : 제약조건명을 리턴

  • CONSTRAINT_TYPE : 제약조건에 대한 타입을 리턴하는데, 그 타입은

    • P : 기본 키

    • R : 외부 키

    • U : 고유 키

    • C : NOT_NULL

  • SEARCH_CONDITION : 제약조건이 지정된 내용을 리턴

  • TABLE_NAME : WHERE절에 조회할 테이블명을 기술

제약조건의 활성화와 비활성화

PK를 제외한 모든 제약조건은 일시적으로 활성화, 비활성화가 가능하다 

ALTER TABLE 테이블명 DISABLE CONSTRAINT 제약조건명;

이렇게 제약조건을 비활성화 시키면 데이터 무결성을 검증하지 않는다 

ALTER TABLE 테이블명 ENABLE CONSTRAINT 제약조건명;

제약조건이 비활성화되어 입력된 데이터가 제약조건이 위배되면 제약조건을 활성화할 수 없다

SQL 튜닝

튜닝이란 의미는 SQL문을 최적화해서 빠른 시간 내에 결과값을 가져오는 방식을 의미한다

일반적인 튜닝 방식

부하가 생기면 이에 대한 원인을 분석을 해보고 -> 조치를 취한(서버/SQL 튜닝) -> 점 이러한 방식을 부하를 해결할 때 까지 반복해서 진행 접근 방법을 살펴보자 부하의 감소 : 일반적으로 접근하는 방식으로, 동일한 부하를 더 효율적인 방법으로 수행 부하의 조정 : 부하 정도에 따라 업무를 조정하는 접근 방법으로, 일반 업무(OLTP)를 분리 부하의 병렬 수행 : 부하가 많이 걸리는 부분에서 병렬 서비스를 실행해서 응답 시간을 크게 단축

튜닝 예시

효율적인 중첩 루프 조인으로 결과 도출하기 -> 조인할 떄 사용되는 컬럼들을 인덱스에 적용시켜서 아주 빠르게 조회하는데 차이를 벌일 수 있다 SUM 함수를 이용하여 반복적인 테이블 스캔 제거하기 재귀 호출 부하 최소화하기 아우터 조인을 스칼라 서브쿼리 방식으로 변환하여 성능 극대화하기 인라인 뷰를 이용한 해시 조인으로 성능 극대화히기

쿼리 튜닝 규칙

https://www.itworld.co.kr/tags/2665/SQL/105792?page=0,0

  1. 가능하면 커서를 피하자 -> 속도 이슈도 있고, 하나의 작업을 블록시킬 수도 있기 때문에 동시성 저하됨

  2. 커서를 피할 수 없다면 임시 테이블을 사용하라

  3. 임시테이블을 현명하게 사용하라 -> 테이블 조인 시, 더 큰 테이블에서 필요한 데이터만 빼와서 조인하는 방식

  4. 데이터를 미리 준비하라 -> 미리 테이블을 조인하고, 영속화해두자

  5. 복합 뷰를 최소화하자 -> 단순한 뷰는 좋지만 중첩되는 복합 뷰는 심각한 성능 저하를 유발가능

  6. UPDATE 대신 CASE를 사용하자

  7. 스칼라 대신 테이블 반환 함수를 사용하자 -> 스칼라 함수 대신 테이블 반환 함수를 사용하고 CROSS APPLY를 사용하면 쿼리 시간이 반절

  8. SQL서버에서 분할을 사용하라

  9. 배치 모드로 DELETE와 UPDATE 작업을 하라

  10. 서두르지 말고 천천히 하라

  11. ORM을 피하라

  12. 가능한 경우, 저장 프로시저를 사용하라

  13. 더블 디핑을 피하라

  14. ... 좀 어렵긴하네

힌트

힌트란 SQL 튜닝 중 한 가지 방법으로 일종의 지시 구문이다 오라클 옵티마이저에게 SQL 실행을 위해 데이터를 스캐닝하는 경로, 조인하는 방법등을 알려주는 방식이다 항상 오라클이 최적의 실행 경로를 만들어주지는 않기 때문에 사람이 직접 들어가서 직접 최적의 실행 경로를 작성해주는 것 하지만 왠만하면 정확하게 최적의 실행 경로를 알고 있는 경우에 사용하자 모든 힌트의 기본 사용법은 쿼리 서두에 힌트를 명시하는 것이다 

SELECT /*+ index_asc(e idx_myemp1_enmae) */
  EMPNO, ENAME, SAL FROM MYEMP1 e
WHERE ENAME >= '가'

이렇게 주석 앞에 +을 사용하는 것으로 적용할 수 있다

https://gurume.tistory.com/entry/오라클-자주사용하는-힌트목록-정리친절한-sql-튜닝

-- 1. 최적화 목표
/*+ALL_LOWS*/ : 전체 처리 속도 최적화
/*+FIRST_ROWS(N)*/ : 최초 N 건 응답속도 최적화

-- 2. 엑세스 방식 
/*+FULL*/ : 인덱스를 타지 말고 테이블 풀 스캔으로 접근라
/*+INDEX*/ : 인덱스를 타자
/*+INDEX_DESC*/ : 인덱스를 ORDER BY DESC 역순으로 타라(시간, 결과값등 최근인것 혹은 MAX값 구할때 좋음
/*+INDEX_FFS*/ : INDEX FAST FULL SCAN으로 타라
/*+INDEX_SS*/ : INDEX SKIP SCAN으로 타라

-- 3. 조인 순서
/*+ORDERED*/ : FROM절에 나열된 테이블 순서대로 조인하라
/*+LEADING*/ : 내가 힌트절에 열거한 테이블 순서대로 조인하라
/*+SWAP_JOIN_INPUTS*/ : 해시 조인인 경우, BUILD INPUT을 명시적으로 선택

-- 4. 조인 방식
/*+USE_NL*/ : NL(NESTED LOOP - 중첩루프) 방식 조인 유도
/*+USE_MERGE*/ : 소트머지 조인으로 유도
/*+USE_HASH*/ : 해시 조인으로 유도
/*+NL_SJ*/ : NL SEMI조인으로 유도
/*+MERGE_SJ*/ : 소트머지 세미조인으로 유도
/*+HASH_SH*/ : 해시 세미조인으로 유도

PreviousOracle 기본NextSQL_연습

Last updated

Was this helpful?