오라클 인덱스를 선택하고 최적화하는 방법
인덱스를 작성하기 위한 일반적인 규칙이 있는지 알고 싶습니다.이 인덱스에 포함할 필드 또는 포함하지 않을 필드를 선택하려면 어떻게 해야 합니까?
항상 환경과 데이터 양에 따라 다르지만 Oracle에서 인덱스를 만드는 데 대해 세계적으로 인정받는 규칙을 만들 수 있는지 궁금합니다.
Oracle 문서에는 색인 선택에 대한 고려사항이 포함되어 있습니다.http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#PFGRF004
19c 업데이트 : https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/designing-and-developing-for-performance.html#GUID-99A7FD1B-CEFD-4E91-9486-2CBBFC2B7A1D
견적:
WHERE 절에서 자주 사용되는 인덱스 키를 고려해 보십시오.
SQL 문의 테이블을 결합하는 데 자주 사용되는 인덱스 키를 고려해 보십시오.조인 최적화에 대한 자세한 내용은 "퍼포먼스를 위한 해시 클러스터 사용" 섹션을 참조하십시오.
선택성이 높은 인덱스 키를 선택합니다.인덱스의 선택성은 테이블에서 인덱스된 키에 동일한 값을 가진 행의 백분율입니다.인덱스의 선택성은 동일한 값을 가진 행이 거의 없는 경우 최적입니다.참고: Oracle은 사용자가 무결성 제약으로 정의한 고유 키와 기본 키의 식에 대해 인덱스를 자동으로 생성하거나 기존 인덱스를 사용합니다.낮은 선택도 열을 인덱싱하는 것은 데이터 분포가 치우쳐 있어 하나 또는 두 개의 값이 다른 값보다 훨씬 적게 발생하는 경우 유용합니다.
고유한 값이 거의 없는 키 또는 식에는 표준 B-트리 인덱스를 사용하지 마십시오.이러한 키 또는 표현은 일반적으로 선택성이 낮기 때문에 자주 선택하는 키 값이 다른 키 값보다 적게 표시되지 않는 한 성능을 최적화하지 않습니다.이러한 경우 높은 동시성 OLTP 응용 프로그램에서처럼 인덱스를 자주 수정하지 않는 한 비트맵 인덱스를 효과적으로 사용할 수 있습니다.
자주 수정되는 열을 인덱싱하지 마십시오.인덱스된 열을 수정하는 UPDATE 문과 인덱스된 테이블을 수정하는 INSERT 및 DELETE 문에는 인덱스가 없는 경우보다 시간이 더 오래 걸립니다.이러한 SQL 문은 테이블의 데이터뿐만 아니라 인덱스의 데이터도 수정해야 합니다.또한 실행 취소 및 재실행도 추가로 생성합니다.
WHERE 절에만 표시되는 키를 함수 또는 연산자와 함께 인덱싱하지 마십시오.MIN 또는 MAX 이외의 함수를 사용하는 WHERE 절 또는 인덱스 키를 가진 연산자는 함수 기반 인덱스를 사용하는 경우를 제외하고 인덱스를 사용하는 액세스 경로를 사용할 수 없습니다.
다수의 동시 INSERT, UPDATE 및 DELETE 문이 상위 및 하위 테이블에 액세스하는 경우 참조 무결성 제약 조건의 외부 키를 인덱싱하는 것이 좋습니다.이러한 인덱스를 사용하면 상위 테이블에서 UPDATE 및 DELETE를 공유 잠금 없이 사용할 수 있습니다.
키를 인덱스하도록 선택할 때는 쿼리에 대한 성능 향상을 INSERT, UPDATE 및 DELETE의 성능 손실과 인덱스 저장에 필요한 공간을 사용할 가치가 있는지 여부를 고려하십시오.인덱스를 사용하는 경우와 사용하지 않는 경우의 SQL 문의 처리 시간을 비교하여 실험해 볼 수 있습니다.SQL 트레이스 기능을 사용하여 처리 시간을 측정할 수 있습니다.
항상 색인화할 필요가 있는 몇 가지 사항이 있습니다.
- 기본 키 - 자동으로 인덱스가 제공됩니다(Oracle에서 사용할 적절한 기존 인덱스를 지정하지 않은 경우).
- 고유 키 - 색인이 자동으로 지정됩니다(반드시).
- 외부 키 - 이러한 키는 자동으로 색인화되지 않지만 제약 조건을 확인할 때 성능 문제를 방지하려면 외부 키를 추가해야 합니다.
그런 다음 쿼리 필터링에 자주 사용되는 다른 열을 찾습니다. 예를 들어 사람들의 성이 대표적인 예입니다.
10g Oracle Database Application Developers Guide - Fundamentals, 5장 참조:
일반적으로 다음과 같은 경우 열에 인덱스를 생성해야 합니다.
- 열은 자주 쿼리됩니다.
- 열에 참조 무결성 제약 조건이 있습니다.
- 열에 고유 키 무결성 제약 조건이 있습니다.
인덱스를 작성할 시기를 결정하려면 다음 지침을 따르십시오.
- 큰 테이블에서 행의 약 15% 미만을 자주 검색하려는 경우 인덱스를 만듭니다.그러나 이 임계값 백분율은 테이블 검색의 상대 속도 및 인덱스 키에 대한 행 데이터의 클러스터링 정도에 따라 크게 달라집니다.테이블 스캔 속도가 빠를수록 백분율이 낮아지고 행 데이터가 클러스터링될수록 백분율이 높아집니다.
- 조인 성능을 향상시키기 위해 조인에 사용되는 열을 색인화합니다.
- 기본 키와 고유 키에는 자동으로 색인이 있지만, 외부 키에 색인을 작성할 수도 있습니다. 자세한 내용은 6장 "애플리케이션 개발 시 데이터 무결성 유지 관리"를 참조하십시오.
- 작은 테이블에는 인덱스가 필요하지 않습니다.조회가 너무 오래 걸리는 경우 테이블이 작은 테이블에서 큰 테이블로 커졌을 수 있습니다.
일부 열은 인덱싱에 적합한 열입니다.다음 특성 중 하나 이상의 열이 인덱싱에 적합합니다.
- 열에서 값이 고유하거나 중복되는 항목이 거의 없습니다.
- 값의 범위는 매우 넓습니다(일반 인덱스에 적합합니다).
- 값의 범위가 좁습니다(비트맵 인덱스에 적합합니다).
이 열에는 많은 null이 포함되어 있지만 쿼리에서는 값이 있는 모든 행을 선택하는 경우가 많습니다.이 경우 다음과 같은 null 이외의 모든 값과 일치하는 비교입니다.
여기서 COL_X > = -9.99 *power (10,125)는 COL_X가 null이 아닌 경우보다 바람직합니다.
이는 첫 번째가 COL_X의 인덱스를 사용하기 때문입니다(COL_X가 수치열이라고 가정).
다음과 같은 특성을 가진 열은 인덱싱에 적합하지 않습니다.
- 열에는 많은 null이 있으며 null이 아닌 값은 검색하지 않습니다.
와, 너무 큰 주제라서 이런 형식으로 대답하기는 어려워요.나는 이 책을 꼭 추천한다.
Tapio Lahdenmaki의 관계형 데이터베이스 인덱스 설계 및 최적화
표 접근을 빠르게 하기 위해 색인을 사용하는 것이 아니라 표 접근을 방지하기 위해 색인을 만들기도 합니다.아직 언급되지 않았지만 필수적인 것.
데이터베이스의 성능을 최대한으로 높이고 싶다면 과학적으로 이해할 수 있습니다.
아, Oracle에 대한 특정 최적화 중 하나는 역키 인덱스를 구축하는 것입니다.시퀀스처럼 단일 원자적으로 값이 증가하는 PK 인덱스가 있고 동시 삽입이 매우 많으며 해당 열을 범위 검색하지 않을 계획이면 이 인덱스를 역키 인덱스로 만듭니다.
이러한 최적화가 얼마나 구체적일 수 있는지 알 수 있습니까?
데이터베이스 정규화 조사 - 존재하는 키, 데이터베이스 관련 방법 및 인덱스에 대한 힌트에 대한 업계 표준 규칙이 많이 있습니다.
-아담
일반적으로 ID 열은 앞에 배치되고 ID 열은 일반적으로 행을 고유하게 식별합니다.열의 조합도 동일한 작업을 수행할 수 있습니다.예를 들어 자동차를 사용하는 경우...태그 또는 번호판은 고유하며 인덱스에 적합합니다.이들(태그 열)은 프라이머리 키에 적합할 수 있습니다.만약 당신이 이름을 검색한다면 소유주 이름은 인덱스에 적합할 수 있다. 자동차 제조사는 너무 많이 달라지지 않기 때문에 처음에 인덱스를 얻으면 안 된다.열의 데이터가 크게 달라지지 않으면 인덱스는 도움이 되지 않습니다.
SQL - where 절이 무엇을 보고 있는지 살펴봅니다.그것들은 지표가 필요할지도 모른다.
재다.문제 - 페이지/쿼리가 너무 오래 걸리는 경우 - 쿼리에 사용되는 것은 무엇입니까?해당 열에 인덱스를 만듭니다.
주의: 인덱스는 업데이트 및 공간이 필요합니다.
또한 전체 테이블 스캔이 인덱스보다 빠를 수 있습니다. 작은 테이블 스캔은 인덱스를 가져온 다음 테이블을 치는 것보다 더 빨리 스캔할 수 있습니다.조인을 보세요.
언급URL : https://stackoverflow.com/questions/212264/how-to-choose-and-optimize-oracle-indexes
'programing' 카테고리의 다른 글
| ORA-28040: 일치하는 인증 프로토콜 예외 없음 (0) | 2023.02.28 |
|---|---|
| ASMX 파일에서 JSON을 출력하는 방법 (0) | 2023.02.28 |
| as 키워드는 어떤 역할을 합니까? (0) | 2023.02.28 |
| Java의 Mono 클래스: 무엇을 언제 사용할 수 있습니까? (0) | 2023.02.28 |
| HTTP Client 응답으로부터의 GZip 스트림 압축 해제 (0) | 2023.02.28 |