여러 인덱스가 함께 작동할 수 있습니까?

여러 인덱스가 함께 작동할 수 있습니까?

"foo"와 "bar"라는 두 개의 필드가 있는 데이터베이스 테이블이 있다고 가정합니다.둘 다 고유한 것은 아니지만, 각각 색인화되어 있습니다.그러나 함께 색인화되는 대신 각 색인에는 별도의 색인이 있습니다.

, 그럼 과 같은 을 해보겠습니다.SELECT * FROM sometable WHERE foo='hello' AND bar='world';내 테이블에는 foo가 '안녕하세요'인 큰 행과 막대가 '세계'인 작은 행이 있습니다.

따라서 데이터베이스 서버가 후드 아래에서 수행하는 가장 효율적인 작업은 막대 인덱스를 사용하여 막대가 '월드'인 모든 필드를 찾은 다음 foo가 '헬로'인 행만 반환하는 것입니다.는 이은입니다.O(n)여기서 n은 막대가 '월드'인 행의 개수입니다.

하지만, 저는 fo 인덱스를 사용하고 결과를 검색하는 과정이 반대로 발생할 가능성이 있다고 생각합니다.은 이은일 것입니다.O(m)여기서 m은 foo가 'hello'인 행의 개수입니다.

Oracle은 여기서 효율적으로 검색할 수 있을 정도로 똑똑합니까?다른 데이터베이스는 어떻습니까?아니면 적절한 순서로 검색할 수 있는 방법이 있나요? 아도퍼로으팅마▁putting로으를 넣음으로써.bar='world'최로초에서 WHERE조항?

Oracle은 거의 확실하게 가장 선택적인 인덱스를 사용하여 쿼리를 실행할 것이며, 설명 계획을 통해 확인할 수 있습니다.

또한 Oracle은 btree 인덱스를 비트맵으로 변환하여 비트맵 AND 작업을 수행하거나 두 인덱스에서 반환된 rowid에 대해 해시 조인을 수행하는 등 두 가지 방법으로 두 인덱스의 사용을 결합할 수 있습니다.

여기서 한 가지 중요한 고려 사항은 쿼리되는 값 사이의 상관 관계일 수 있습니다.foo='hello'가 테이블 값의 80%를 차지하고 bar='world'가 10%를 차지하는 경우 Oracle은 쿼리가 테이블 행의 0.8*0.1=8%를 반환한다고 추정합니다.그러나 이것은 올바르지 않을 수 있습니다. 쿼리는 실제로 값의 상관 관계에 따라 rwos의 10% 또는 행의 0%를 반환할 수 있습니다.테이블 전체에 걸쳐 해당 행의 분포에 따라 인덱스를 사용하여 행을 찾는 것이 효율적이지 않을 수 있습니다.필요한 행("클러스터링 팩터"의 경우 구글)을 검색하려면 여전히 70% 또는 테이블 블록에 액세스해야 할 수 있습니다. 이 경우 Oracle은 추정치가 정확할 경우 전체 테이블 검색을 수행합니다.

11g에서는 여러 열 통계를 수집하여 이러한 상황에 도움이 될 수 있다고 생각합니다.9i 및 10g에서는 동적 샘플링을 사용하여 검색할 행 수를 매우 잘 예측할 수 있습니다.

실행 계획을 가져오려면 다음을 수행합니다.

explain plan for
SELECT *
FROM   sometable
WHERE  foo='hello' AND bar='world'
/
select * from table(dbms_xplan.display)
/

이를 다음과 비교:

explain plan for
SELECT /*+ dynamic_sampling(4) */
       *
FROM   sometable
WHERE  foo='hello' AND bar='world'
/
select * from table(dbms_xplan.display)
/

일라이,

귀하가 작성한 의견:

안타깝게도, 저는 각각의 색인이 있는 많은 열이 있는 테이블을 가지고 있습니다.사용자는 필드 조합을 쿼리할 수 있으므로 각 필드 조합에 대한 인덱스를 효율적으로 작성할 수 없습니다.그러나 인덱스가 필요한 필드가 두 개뿐이라면 인덱스를 두 개 사용하자는 당신의 제안에 전적으로 동의할 것입니다.일라이 코트라이트 (9월 29일 15:51)

이것은 사실 꽤 중요한 정보입니다.때때로 프로그래머들은 질문을 할 때 스스로를 능가합니다.그들은 질문을 본질적인 포인트까지 끌어내려고 노력하지만 종종 너무 단순화되어 최고의 답을 얻는 것을 놓칩니다.

이 시나리오는 정확히 비트맵 인덱스가 만들어진 이유입니다. 알 수 없는 열의 그룹이 where 절에 사용되는 시간을 처리하기 위해 사용됩니다.

만약 누군가 BMI가 낮은 카디널리티 컬럼에만 해당되며 당신의 경우에는 적용되지 않을 수도 있다고 말할 경우.Low는 아마도 당신이 생각하는 것만큼 작지 않을 것입니다.유일한 실질적인 문제는 테이블에 대한 DML의 동시성입니다.단일 나사산이거나 희귀해야 작동합니다.

예, 오라클에 쿼리와 함께 "힌트"를 제공할 수 있습니다.이러한 힌트는 데이터베이스에 대한 주석("/* HINT */")으로 위장되며 주로 벤더별로 다릅니다.따라서 한 데이터베이스에 대한 힌트는 다른 데이터베이스에서 사용할 수 없습니다.

여기서는 인덱스 힌트를 사용합니다. 작은 테이블의 첫 번째 힌트입니다.여기 보세요.

반면에 이 두 필드를 자주 검색한다면 이 두 필드에 대한 인덱스를 만드는 것은 어떨까요?올바른 구문을 가지고 있지 않지만, 그것은 다음과 같은 것일 것입니다.

CREATE INDEX IX_BAR_AND_FOO on sometable(bar,foo);

이런 방식으로 데이터를 검색하는 것은 상당히 빠를 것입니다.그리고 연결이 고유한 경우에는 고유한 인덱스를 만들기만 하면 됩니다. 이 인덱스는 번개처럼 빨라야 합니다.

먼저, 저는 당신이 정상적인 표준 b*tree 인덱스에 대해 이야기하고 있다고 가정하겠습니다.비트맵 인덱스에 대한 답은 근본적으로 다릅니다.Oracle에는 다양한 유형의 인덱스에 대한 옵션이 많이 있습니다. 이 옵션을 사용하면 정답이 변경되거나 변경되지 않을 수 있습니다.

적어도 최적화 도구가 특정 조건의 선택성을 결정할 수 있는 경우에는 보다 선택적인 색인(즉, 막대의 색인)을 사용합니다.그러나 치우친 데이터가 있는 경우(열 막대에는 N개의 값이 있지만 특정 값의 선택성은 데이터의 1/N보다 훨씬 크거나 작음) 최적화 도구에 어떤 값이 더 많거나 적을지 알려주기 위해 열에 히스토그램이 있어야 합니다.또한 모든 우수한 OLTP 개발자가 사용하는 것처럼 바인딩 변수를 사용하는 경우 Oracle 버전에 따라 바인딩 변수 피킹에 문제가 있을 수 있습니다.

잠재적으로 Oracle은 두 개의 b*tree 인덱스를 비트맵으로 즉시 변환하고 비트맵을 결합하여 두 인덱스를 사용하여 검색해야 할 행을 찾을 수도 있습니다.그러나 이는 특히 한 열이 매우 선택적인 두 개의 열만 있는 경우에는 상당히 이례적인 쿼리 계획입니다.

Oracle은 여기서 효율적으로 검색할 수 있을 정도로 똑똑합니까?

간단한 대답은 "아마도"입니다.각 데이터베이스 공급업체에는 매우 똑똑한 사람들이 많이 있습니다. 쿼리 최적화기를 최적화하기 위해 노력하고 있기 때문에, 아마 여러분이 생각지도 못했던 일들을 하고 있을 것입니다.통계를 업데이트하면 아마 더 많은 효과를 얻을 수 있을 것입니다.

Oracle에서 쿼리 계획을 표시하여 어떤 인덱스가 먼저 사용되는지 정확하게 확인할 수도 있습니다.

가장 좋은 방법은 foo를 bar의 인덱스에 추가하거나 foo의 인덱스(또는 둘 다)에 bar를 추가하는 것입니다.Foo의 인덱스에 막대에 인덱스가 포함되어 있는 경우, 해당 인덱스의 현재 사용 중인 Foo 인덱스의 유용성에 추가 인덱싱 수준이 영향을 미치지 않고 해당 인덱스를 유지 관리하는 성능에도 영향을 미치지 않지만 예제와 같이 쿼리를 최적화하는 데 사용할 수 있는 추가 정보를 데이터베이스에 제공합니다.

그것보다 더 좋습니다.

색인 검색은 항상 전체 테이블 검색보다 빠릅니다.따라서 Oracle(및 SQL Server)은 먼저 두 인덱스에서 행 범위를 찾습니다.그런 다음 어떤 범위가 더 짧은지 확인하고(내부 결합인지 확인), 더 짧은 범위를 반복하여 둘 중 더 큰 범위와 일치하는 항목을 찾습니다.

사용할 인덱스에 대한 힌트를 제공할 수 있습니다.Oracle은 잘 모르지만, Mysql에서는 USE|IGNORE|FORCE_INDEX를 사용할 수 있습니다(자세한 내용은 여기 참조).최상의 성능을 얻으려면 결합된 인덱스를 사용해야 합니다.

언급URL : https://stackoverflow.com/questions/149078/can-multiple-indexes-work-together