SQL 문장 튜닝

select nvl(sum(a.dr_amt),0), nvl(sum(a.cr_amt),0)
  from  TB_SW23263 a, TB_SW23005 b
 where a.comp_cd     = b.comp_cd
   and a.comp_cd     = '11'
   and a.yymm       >= '200401'
   and a.yymm       <= '200410'
   and a.dept_cd     = '1000'
   and a.acc_cd      = b.acc_cd
   and b.comp_cd     = '11'
   and b.acc_div     = 'B' ;

이문장을 튜닝 하려고 하는데요..

먼저 해당 테이블에 대한 인덱스는

- tb_sw23263
PK_SW23263 COMP_CD
PK_SW23263 YYMM
PK_SW23263 DEPT_CD
PK_SW23263 ACC_CD
PK_SW23263 DESC_CD1
PK_SW23263 DESC1
PK_SW23263 DESC_CD2
PK_SW23263 DESC2
PK_SW23263 BUSI_JUMIN_NO

TX_SW23263_01 COMP_CD
TX_SW23263_01 YYMM
TX_SW23263_01 ACC_CD
TX_SW23263_01 DESC_CD1
TX_SW23263_01 DESC1
TX_SW23263_01 DESC_CD2
TX_SW23263_01 DESC2
TX_SW23263_01 BUSI_JUMIN_NO

TX_SW23263_03 COMP_CD
TX_SW23263_03 YYMM
TX_SW23263_03 ACC_CD

- tb_sw23005
PK_SW23005 COMP_CD
PK_SW23005 ACC_CD

TX_SW23005_01 COMP_CD
TX_SW23005_01 ACC_KEY  입니다...

tb_sw23263에서 인덱스 tx_sw23263_03을 추가했는데요..
이 조합은 이미 tx_sw23263_01에서 가지고 있으니까 만들어도 안타는게 맞나요?

-> tb_sw23263테이블의 인덱스를 살펴보면 우선 PK, 01, 03 인덱스가 모두 다 중복된 인덱스라고 보여집니다.

     03 인덱스는 불필요해 보이고, 또한, PK 인덱스 칼럼의 순서를

    COMP_CD+YYMM+ACC_CD+DESC_CD1+DESC1+DESC_CD2+DESC2+BUSI_JUMIN_NO+DEPT_CD

    로 조정하고 01 인덱스를 삭제해도 무방해 보입니다.

    단, 삭제하기 전에 여타 SQL문에서 DEPT_CD 칼럼의 사용 방식을 점검해보아야 합니다.

그리고 인덱스를 만드는 순서에 따라서 안탈수도 있는것가요?
-> 인덱스 만드는 순서에 따라 달라지진 않습니다.

    단지 복합인덱스의 경우 인덱스 칼럼의 순서에 따라 인덱스의 사용여부가 달라집니다.

 

만약에 tx_sw23263_03이 만들어진 후에 tx_sw23263_01을 만들었을 경우
위에 있는 문장 실행시 tx_sw23263_03 index를 scan할까요?
-> 인덱스 만드는 순서에 따라 달라지진 않습니다. 01인덱스와 03인덱스의 사용여부는 달라지지 않습니다.

    달라지는 경우는 rule-base로 운영되는 경우이고 cost-base의 경우는 각각의 cost를 계산하여 사용합니다.

select nvl(sum(a.dr_amt),0), nvl(sum(a.cr_amt),0)
  from  TB_SW23263 a, TB_SW23005 b
 where a.comp_cd     = b.comp_cd
   and a.comp_cd     = '11'
   and a.yymm       >= '200401'
   and a.yymm       <= '200410'
   and a.dept_cd     = '1000'
   and a.acc_cd      = b.acc_cd
   and b.comp_cd     = '11'
   and b.acc_div     = 'B' ; 문장에서

1) a.yymm부분을 제거시 tb_sw23263을 full scan
   SELECT STATEMENTcost  estimate:331
  SORT AGGREGATE
    HASH JOIN
      TABLE ACCESS FULL:SWG,,,,,,,,TB_SW23005(2)
      TABLE ACCESS FULL:SWG,,,,,,,,TB_SW23263(1)

2) a.dept_cd or a.acc_cd제거시 tx_sw23263_01 index scan
   SELECT STATEMENTcost  estimate:19
  SORT AGGREGATE
    HASH JOIN
      TABLE ACCESS FULL:SWG,,,,,,,,TB_SW23005(2)
      TABLE ACCESS BY INDEX ROWID:SWG,,,,,,,,TB_SW23263(1)
        INDEX RANGE SCAN:SWG,,,,,,,,,,,TX_SW23263_01

3) 이 문장을 그대로 사용할경우
   SELECT STATEMENTcost  estimate:19
  SORT AGGREGATE
    HASH JOIN
      TABLE ACCESS FULL:SWG,,,,,,,,TB_SW23005(2)
      TABLE ACCESS BY INDEX ROWID:SWG,,,,,,,,TB_SW23263(1)
        INDEX RANGE SCAN:SWG,,,,,,,,,,,TX_SW23263_01 이런 실행계획이 나오는데요..

   
1)번은 왜 인덱스를 타지 않나요?

-> yymm 칼럼이 빠지면서 오라클 Optimizer가 2개 테이블을 Full Scan후 Hash Join 하는 것이 cost가 낮다고

    계산되어서 그런 것입니다. comp_cd+dept_cd 의 분포도가 안 좋다구 판단을 한 것이죠.

2)번은 왜 1)번처럼 full scan을 하지 않나요?

-> 1번과 달리 yymm 칼럼이 들어가면서 TB_SW23263 테이블을 Full scan하기 전에 Index로 Filtering 한 다음에

    Hash join 하는 것이 cost가 낮다구 계산한 것이구요.

3)번은 tx_sw23263_01이 아니라 pk_sw23263을 scan해야 하는거 아닌가요?
인덱스도 만들어진 순서에 따라 탈수도 있고 안탈수도 있나요?

-> 01인덱스를 타는 이유는 PK 인덱스가 dept_cd 칼럼이 들어있어 인덱스의 리프 블럭 수가 01인덱스보다 작을 것이고,

    dept_cd 의 분포도가 좋지않아 Filtering factor가 좋지 않다구 판단한 것입니다.

 

    다시 말씀드리자면 인덱스 생성 순서가 영향 받는 경우는 rule-base로 운영되는 경우입니다.

    cost-base의 오라클 Optimizer의 판단은 analyze 정보를 바탕으로 비용 계산에 의한 것입니다.

   

  

alalyze index tx_sw23263_01 validate structure ; 문장이 하는일이 뭔지 알구 싶은데여~~

-> 저도 정확히 모르겠는데요... ㅡ.ㅡ;;;; 웹에서 찾아보니...아래처럼 나와있네요...^^

먼저 index의 space usage를 알기 위해 해당 index의 structure를 validate하여 필요한 정보를 모아야 한다. index의 structure를 validate하는 것은 아래의 command를 이용해서 수행한다.

analyze index <index name> validate structure;　　　　　

위의 analyze command를 수행하면 해당 index의 정보를 추출하게 되며 추출된 정보는 index_stats라는 table에 저장된다.　　　　　

index structure의 validate후 index_stats에 저장된 정보를 아래의 script를 이용해 index의 disk space 사용 현황을 볼 수 있다