이 쿼리에 인덱스가 사용되지 않는 이유는 무엇입니까?

이 쿼리에 인덱스가 사용되지 않는 이유는 무엇입니까?

가능하다고 생각될 때 인덱스가 사용되지 않는 쿼리가 있어서 호기심으로 재생산했습니다.

성을 합니다.test_table1행(1.000.000에서 의 고유한 값을 col 단위의 :some_data).

CREATE TABLE test_table AS (
  SELECT MOD(ROWNUM,10) col, LPAD('x', 500, 'x') some_data
  FROM dual
  CONNECT BY ROWNUM <= 1000000
);

인덱스를 만들고 테이블 통계를 수집합니다.

CREATE INDEX test_index ON test_table ( col );

EXEC dbms_stats.gather_table_stats( 'MY_SCHEMA', 'TEST_TABLE' );

다음과 같은 고유한 값을 얻으려고 노력합니다.col 리고그고.COUNT:

EXPLAIN PLAN FOR
  SELECT col, COUNT(*)
  FROM test_table
  GROUP BY col;

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |    30 | 15816   (1)| 00:03:10 
|   1 |  HASH GROUP BY     |            |    10 |    30 | 15816   (1)| 00:03:10 
|   2 |   TABLE ACCESS FULL| TEST_TABLE |   994K|  2914K| 15755   (1)| 00:03:10 
--------------------------------------------------------------------------------- 

힌트가 이 값을 변경하지 않으면 인덱스가 사용되지 않습니다.

이런 경우에는 지수를 사용할 수 없는 것 같은데, 왜 그럴까요?

업데이트: 열 열을 NULL로 설정하지 마십시오.그것이 그것이 색인을 사용하지 않는 이유입니다.무효가 아닐 때는 다음과 같은 계획을 세웁니다.

SELECT STATEMENT, GOAL = ALL_ROWS           69  10  30
                    HASH GROUP BY           69  10  30
 INDEX FAST FULL SCAN   SANDBOX TEST_INDEX  56  98072   294216

최적화 도구가 인덱스를 사용하지 않는 것이 더 효율적이라고 판단하면(쿼리를 다시 쓰기 때문일 수 있음) 그렇지 않습니다.Optimizer 힌트는 Oracle에 사용할 인덱스를 알려주는 힌트입니다.당신은 그들을 제안으로 생각할 수 있습니다.그러나 Optimizer가 인덱스를 사용하지 않는 것이 더 낫다고 판단하는 경우(예: 쿼리 다시 쓰기의 결과) 그렇지 않습니다.

다음 링크를 참조하십시오. http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm "다음 힌트 중 하나를 지정하면 최적화 도구는 인덱스 또는 클러스터의 존재와 SQL 문의 구문 구조를 기반으로 액세스 경로를 사용할 수 있는 경우에만 지정된 액세스 경로를 선택합니다.힌트가 사용할 수 없는 액세스 경로를 지정하면 옵티마이저는 이를 무시합니다."

카운트(*) 작업을 실행 중이므로 최적화 도구는 인덱스를 사용하는 대신 전체 테이블과 해시를 검색하는 것이 더 효율적이라고 판단했습니다.

힌트에 대한 또 다른 유용한 링크는 http://www.dba-oracle.com/t_hint_ignored.htm 입니다.

당신은 이 정말 중요한 정보를 잊었습니다: COL은 null이 아닙니다.

열이 NULLABLE이면 인덱스되지 않은 행이 있을 수 있으므로 인덱스를 사용할 수 없습니다.

SQL> ALTER TABLE test_table MODIFY (col NOT NULL);

Table altered
SQL> EXPLAIN PLAN FOR
  2  SELECT col, COUNT(*) FROM test_table GROUP BY col;

Explained
SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1077170955
--------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |    10 |    30 |  1954   (1)| 00:00:2
|   1 |  SORT GROUP BY NOSORT|            |    10 |    30 |  1954   (1)| 00:00:2
|   2 |   INDEX FULL SCAN    | TEST_INDEX |   976K|  2861K|  1954   (1)| 00:00:2
--------------------------------------------------------------------------------

나는 피터의 원래 물건을 실행했고 그의 결과를 재현했습니다.그런 다음 dcp의 제안을 적용했습니다...

SQL> alter table test_table modify col not null;

Table altered.

SQL> EXEC dbms_stats.gather_table_stats( user, 'TEST_TABLE' , cascade=>true)

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
  2    SELECT col, COUNT(*)
  3    FROM test_table
  4    GROUP BY col;

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 2099921975

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |    10 |    30 |   574   (9)| 00:00:07 |
|   1 |  HASH GROUP BY        |            |    10 |    30 |   574   (9)| 00:00:07 |
|   2 |   INDEX FAST FULL SCAN| TEST_INDEX |  1000K|  2929K|   532   (2)| 00:00:07 |
------------------------------------------------------------------------------------

9 rows selected.

SQL>

이 문제가 중요한 이유는 NULL 값이 일반 B-TREE 인덱스에 포함되지 않지만 GROUP BY는 쿼리에 NULL을 그룹화 "값"으로 포함해야 하기 때문입니다.에 " " " NULL"에 .col훨씬 효율적인 인덱스를 자유롭게 사용할 수 있습니다(FTS를 통해 거의 3.55초의 경과 시간을 얻었습니다).이것은 메타데이터가 최적화 도구에 어떻게 영향을 미칠 수 있는지 보여주는 전형적인 예입니다.

덧붙여서, 이것은 오래된 SORT(GROUP BY) 알고리즘 대신 HASH GROUP BY 알고리즘을 사용하기 때문에 분명히 10g 또는 11g 데이터베이스입니다.

비트맵 인덱스도 좋습니다.

실행 계획----------------------------------------------------------계획 해시 값: 2200191467
---------------------------------------------------------------------------------ID | 작업 | 이름 | 행 | 바이트 | 비용(%CPU) | 시간 |---------------------------------------------------------------------------------0 | 문 선택|            |    10 |    30 | 15983   (2)| 00:03:12 |1 | 해시 GROUP BY || 10 || 30 || 15983 (2) || 00:03:12 |2 | TABLE ACCESS Full | TEST_TABLE | 1013K | 2968K | 15825 (1) | 00:03:10 |---------------------------------------------------------------------------------
SQL > test_table(col)에 비트맵 인덱스 test_index 생성;

인덱스가 생성되었습니다.

SQL > EXEC dbms_stats.gather_table_stats( 'MY_SCHEMA', 'TEST_TABLE' );
PL/SQL 프로시저가 성공적으로 완료되었습니다.

SQL > SELECT col,카운트(*)2 테스트_테이블로부터콜별 3개 그룹4  /
실행 계획----------------------------------------------------------계획 해시 값: 238193838
---------------------------------------------------------------------------------------ID | 작업 | 이름 | 행 | 바이트 | 비용(%CPU) | 시간 |---------------------------------------------------------------------------------------0 | SELECT STATION | | 10 | 30 | 286 (0) | 00:00:04 |1 | SORT GROUP by 노소트 | | 10 | 30 | 286 (0) | 00:00:04 |2 | 비트맵 변환 카운트 || 1010K || 2961K || 286 (0) || 00:00:04 |3 | 비트맵 INDEX FULL SCAN | TEST_INDEX | | | |---------------------------------------------------------------------------------------

언급URL : https://stackoverflow.com/questions/2185754/why-isnt-index-used-for-this-query