오라클 9I SQL 조회시 PARTITION 강제로 안태우는 법 문의(글 재 정리) 0 3 2,389

by 빈이 [SQL Query] PARTITION [2017.04.17 14:37:27]


* 스키마생성 샘플

CREATE TABLE T1 ( ID     NUMBER(13)                      NOT NULL );
CREATE UNIQUE INDEX PK_T1 ON T1 (ID);

ALTER TABLE T1 ADD (
  CONSTRAINT PK_T1
  PRIMARY KEY
  (ID)
  USING INDEX PK_T1);

CREATE TABLE T2
( ID     NUMBER(13)                      NOT NULL,
    CREATE_DT DATE
)
PARTITION BY RANGE (CREATE_DT)
(PARTITION P_MAX VALUES LESS THAN (MAXVALUE));

CREATE INDEX IDX_T2_ID_01 ON T2 (ID);

 

* 조회 SQL

SELECT /*+ USE_NL(A B) INDEX(A PK_T1) INDEX(B IDX_T2_ID_01) */
*
FROM T1 A, T2 B
WHERE A.ID IN (1,2,3)
AND A.ID = B.ID(+) ;
 

* 문제점

위에서 HINT로 A PK_T1 PK INDEX를 우선 태운 후 T2 테이블의 ID INDEX(IDX_T2_ID_01)를 태우려고 합니다.

그런데 위와 같이 실행을 하면 T2의 PARTITION RANGE ALL을 타고 있습니다.

IDX_T2_ID_01을 태우고 싶네요.

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=4K)
   1    0   WINDOW (SORT) (Cost=9 Card=1 Bytes=4K)
   2    1     NESTED LOOPS (OUTER) (Cost=6 Card=1 Bytes=4K)
   3    2       INLIST ITERATOR
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes=4K)
   5    4           INDEX (RANGE SCAN) OF 'PK_T1' (UNIQUE) (Cost=2 Card=1)
   6    2       PARTITION RANGE (ALL)
   7    6         TABLE ACCESS (FULL) OF 'T2' (Cost=5 Card=1 Bytes=167)

 

by 마농 [2017.04.18 10:08:58]

실제 사용하신 생성 스크립트인가요?
파티션이 하나 뿐이네요?
데이터는 몇건 들어 있나요?
쿼리는 실제 쿼리가 아닌것처럼 보이네요?
실제 사용한 쿼리를 보여주세요.


by 미스틱매니아 [2017.04.18 11:14:15]

이상하네요 전 본문에 있는거 대충 돌려도 인덱스 스캔으로 풀리는데

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    35 |     1   (0)| 00:00:01 |    |          |
|   1 |  NESTED LOOPS OUTER                 |              |     1 |    35 |     1   (0)| 00:00:01 |    |          |
|   2 |   INLIST ITERATOR                   |              |       |       |            |          |    |          |
|*  3 |    INDEX UNIQUE SCAN                | PK_T1        |     1 |    13 |     1   (0)| 00:00:01 |    |          |
|   4 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T2           |     1 |    22 |     0   (0)| 00:00:01 |  1 |        1 |
|*  5 |    INDEX RANGE SCAN                 | IDX_T2_ID_01 |     1 |       |     0   (0)| 00:00:01 |    |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."ID"=1 OR "A"."ID"=2 OR "A"."ID"=3)
   5 - access("A"."ID"="B"."ID"(+))
       filter("B"."ID"(+)=1 OR "B"."ID"(+)=2 OR "B"."ID"(+)=3)

Note
-----
   - dynamic sampling used for this statement (level=2)


by chrome [2017.04.18 21:08:53]

GLOBAL INDEX 로 만드세요. 

LOCAL INDEX는 찾는 ID 의 값이  CREATE_DT기준으로 만들어진 파티션 중 어디에 있는지  알 수가 없습니다. 

 

파티션 5개가 있을때 각각 파티션 마다 만들어진 INDEX는 ID값이 중복가능하기 때문에 

ID 1 이 모든 파티션에 있을 수도 있고, 아닐 수도 있는거죠.

그러니 다 뒤져보는 실행계획을 가지게 됩니다. 

 

이는 통계정보와도 관련이 있습니다.

통계정보를 먼저 확인해보세요. 

찾아보는 파티션 갯수를 줄일 수 있습니다. 

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입