OR Expasion 유도하는 힌트

-- BITMAP CONVERSION (오라클성능고도화 참고) <https://cafe.naver.com/dbian/5224>
select /*+ 힌트없음    */ * from BIG_TABLE where id=384 or owner='APPQOSSYS';

-- concatenation
select /*+ use_concat */ * from BIG_TABLE where id=384 or owner='APPQOSSYS';

-- union all
select * from BIG_TABLE where id=384
union ALL
select * from BIG_TABLE where owner='APPQOSSYS';

IN절

-- IN-LIST ITERATOR
select * from BIG_TABLE where id in (1,2,3,4,5,6,7,8,9);

SELECT-LIST 가공시

select /*+ index(X x01)*/ nvl(max(created), '2022-01-01') from big_table X where owner='APPQOSSYS'; -- 가공안함
select /*+ index(X x01)*/ nvl(max(to_char(created, 'D')), 0) from big_table X where owner='APPQOSSYS'; -- 가공된 select, first row stopkey가 불가능함
select /*+ index(X x01)*/ nvl(to_char(max(created), 'D'), 0) from big_table X where owner='APPQOSSYS'; -- 해결책

Index Skip Scan

--[실습1]
drop table 사원;
create table 사원
nologging
as
select object_id as 고객번호
     , object_name as 고객명
     , created as 가입일시
     , temporary as 성별
     , object_id as 연봉
from all_objects
where rownum <= 1000
;

create index 사원_IDX on 사원(성별, 연봉);

select  *
from 사원
where 연봉 between 2000 and 3000
;

-- [실습2]
UPDATE big_table SET gender='여' WHERE rowid IN (
    SELECT r FROM (
        SELECT ROWID r FROM big_table ORDER BY dbms_random.value
    ) RNDM WHERE rownum < 10000
);

select * from big_table where gender = '여' and created >= TO_DATE('20190530', 'YYYYMMDD') ;
select /*+                               */ * from big_table X where created >= TO_DATE('20190530', 'YYYYMMDD') ;
select /*+ index_ss(X (gender, created))   */ * from big_table X where created >= TO_DATE('20230130', 'YYYYMMDD') ;

-- 선택도에 따른 실행결과
select /*+ */ * from big_table X where created >= TO_DATE('20230130', 'YYYYMMDD') ;
select /*+ */ * from big_table X where created >= TO_DATE('20220130', 'YYYYMMDD') ;

Index Fast Full Scan