반응형
하위 쿼리 또는 분석 기능이 표시된 전체 테이블 검색을 피합니다.
Oracle 11(SQL Fiddle 참조)과 Oracle 12로 다음 동작을 재현할 수 있습니다.
CREATE TYPE my_tab IS TABLE OF NUMBER(3);
CREATE TABLE test AS SELECT ROWNUM AS id FROM dual CONNECT BY ROWNUM <= 1000;
CREATE UNIQUE INDEX idx_test ON test( id );
CREATE VIEW my_view AS
SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt
FROM test;
다음의 경우는 예상대로 인덱스를 사용합니다.
SELECT * FROM my_view
WHERE id IN ( 1, 2 );
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 2 (0)| 00:00:01 |
| 1 | VIEW | MY_VIEW | 2 | 52 | 2 (0)| 00:00:01 |
| 2 | WINDOW BUFFER | | 2 | 8 | 2 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
|* 4 | INDEX UNIQUE SCAN| IDX_TEST | 2 | 8 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
다음 경우는 카디널리티 힌트를 제공하더라도 인덱스를 사용하지 않습니다.
SELECT * FROM my_view
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ COLUMN_VALUE
FROM TABLE( NEW my_tab( 1, 2 ) ) tab );
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 33 (4)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 28 | 33 (4)| 00:00:01 |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 2 | 4 | 29 (0)| 00:00:01 |
| 3 | VIEW | MY_VIEW | 1000 | 26000 | 4 (25)| 00:00:01 |
| 4 | WINDOW SORT | | 1000 | 4000 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST | 1000 | 4000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
편집:
인라인 뷰 및 a 사용JOIN대신에IN다음과 같은 계획을 사용합니다.
SELECT /*+ CARDINALITY( tab, 2 ) */ *
FROM ( SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt FROM test ) t
JOIN TABLE( NEW my_tab( 1, 2 ) ) tab ON ( tab.COLUMN_VALUE = t.id );
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 56 | 33 (4)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 56 | 33 (4)| 00:00:01 |
| 2 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 2 | 4 | 29 (0)| 00:00:01 |
| 3 | VIEW | | 1000 | 26000 | 4 (25)| 00:00:01 |
| 4 | WINDOW SORT | | 1000 | 4000 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST | 1000 | 4000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
분석 기능을 a로 대체LEFT JOIN와 함께GROUP BY는 다음에도 도움이 되지 않습니다.
SELECT *
FROM ( SELECT t.id, s.cnt
FROM test t
LEFT JOIN ( SELECT id, COUNT(*) AS cnt
FROM test
GROUP BY id
) s ON ( s.id = t.id )
)
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ COLUMN_VALUE
FROM TABLE( NEW my_tab( 1, 2 ) ) tab );
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 64 | 34 (6)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 2 | 64 | 34 (6)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 12 | 30 (4)| 00:00:01 |
| 3 | SORT UNIQUE | | 2 | 4 | 29 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 2 | 4 | 29 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | IDX_TEST | 1 | 4 | 0 (0)| 00:00:01 |
| 6 | VIEW | | 1000 | 26000 | 4 (25)| 00:00:01 |
| 7 | HASH GROUP BY | | 1000 | 4000 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TEST | 1000 | 4000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
PL/SQL Collection을 하위 선택으로 바꾸는 것도 도움이 되지 않는 것 같습니다.CARDINALITY 힌트가 고려되지만(계획에는 2행이 표시됨), 인덱스는 여전히 무시됩니다.
SELECT *
FROM ( SELECT id, cnt FROM my_view )
WHERE id IN ( SELECT /*+ CARDINALITY( tab 2 ) */ id FROM test tab );
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 60 | 4 (25)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 60 | 4 (25)| 00:00:01 |
| 2 | VIEW | MY_VIEW | 1000 | 26000 | 4 (25)| 00:00:01 |
| 3 | WINDOW SORT | | 1000 | 4000 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST | 1000 | 4000 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | IDX_TEST | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
추가하기WHERE tab.id <= 2in-list-subquery는 인덱스를 사용하므로 옵티마이저는 분석 함수가 있는 뷰(또는 다른 하위 선택)에서 선택하고 값 목록으로 필터링할 때 "CARDINALITY 힌트를 충분히 심각하게 받아들이지 않는 것으로 보입니다.
이러한 쿼리가 예상대로 인덱스를 사용하도록 하려면 어떻게 해야 합니까?
생각합니다
그. 한 가지 문제는 외부 쿼리 블록에 PL/SQL 함수가 포함되어 있을 때 Optimizer가 뷰 병합(기본 테이블의 인덱스를 고려)을 거부하는 것일 수 있습니다.TABLE()).
보기를 수동으로 확장하고 테이블을 직접 쿼리하면 인덱스에 미세하게 액세스할 수 있습니다.
SELECT id, COUNT(1) OVER ( PARTITION BY id ) AS cnt
FROM test
WHERE id IN ( SELECT COLUMN_VALUE
FROM TABLE( NEW my_tab( 1, 2 ) ) tab )
;
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 31 (4)| 00:00:01 |
| 1 | WINDOW SORT | | 1 | 6 | 31 (4)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 6 | 30 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IDX_TEST | 1000 | 4000 | 1 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
이 동작을 무시할 방법이 있는지, 아니면 옵티마이저의 제한 사항인지 잘 모르겠습니다.TABLE 기능을 CTE로 이동하려고 했는데, 도움이 되지 않는 것 같습니다.
언급URL : https://stackoverflow.com/questions/54348748/avoid-full-table-scan-with-subquery-or-analytic-function-in-view
반응형
'programing' 카테고리의 다른 글
| Next.js: getInitialProps()에서 데이터 가져오기: 서버 측 대 클라이언트 측 (0) | 2023.10.29 |
|---|---|
| 모듈 'mysql' node.js를 찾을 수 없습니다. (0) | 2023.10.29 |
| pthread 조건 변수를 언제 사용해야 합니까? (0) | 2023.10.29 |
| Swift에서 Change 문자InRange는 어떻게 작동합니까? (0) | 2023.10.29 |
| 불법 논변예외: 탐색 대상 xxxx는 이 NavController에 알 수 없습니다. (0) | 2023.10.29 |