今天有个徒弟找我看一条SQL
SELECT /*+ use_hash(REGION,MPI) */REGION.REGION_CODE,REGION.REGION_NAME,MPI.YEARS,SUM(MPI.MPI_COUNT) SUM_MPI_COUNTFROM(SELECT /*+ index_ffs(T_REGION T_REGION_INDEX) */ REGION_CODE, REGION_NAME FROM T_REGION where REGION_CODE is not nulland REGION_NAME is not null) REGIONLEFT JOIN( SELECT /*+ index_ffs(MPI_PERSON_INDEX MPI_PERSON_INDEX_INDEX) */ AR_CD,TO_CHAR(CREATETIME,'YYYY') YEARS,COUNT(1) AS MPI_COUNTFROM MPI_PERSON_INDEXGROUP BY AR_CD,TO_CHAR(CREATETIME,'YYYY')) MPION REGION.REGION_CODE=MPI.AR_CDGROUP BY REGION.REGION_CODE,REGION.REGION_NAME,MPI.YEARS
加了hint use_hash不生效， 然后让他生成10053 trace，结果trace里面没有 use_hash 的hint ...，但是有 index_ffs 的hint 太奇怪了，于是把sql改了
SELECT /*+ use_hash(region,mpi) */REGION.REGION_CODE,REGION.REGION_NAME,MPI.YEARS,SUM(MPI.MPI_COUNT) SUM_MPI_COUNTFROM (SELECT /*+ index_ffs(T_REGION T_REGION_INDEX) */REGION_CODE, REGION_NAMEFROM T_REGIONwhere REGION_CODE is not nulland REGION_NAME is not null) REGION,(SELECT /*+ index_ffs(MPI_PERSON_INDEX MPI_PERSON_INDEX_INDEX) */AR_CD, TO_CHAR(CREATETIME, 'YYYY') YEARS, COUNT(1) AS MPI_COUNTFROM MPI_PERSON_INDEXGROUP BY AR_CD, TO_CHAR(CREATETIME, 'YYYY')) MPIwhere REGION.REGION_CODE = MPI.AR_CD(+)GROUP BY REGION.REGION_CODE, REGION.REGION_NAME, MPI.YEARS改成 + 方式居然hint生效了， 因为无法连接他电脑，就没有深入研究下去了，先记录一下。