- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900977&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
FUNCTION-BASED INDEX(FBI. ORACLE 8I NEW FEATURE)
개요
개요
- 10g Standard Edition(10.1.0) 이상 부터 Function-based Index 기능 지원 가능
- 함수(function)이나 수식(expression)으로 계산된 결과에 대해 인덱스를 생성하여 사용할 수 있는 기능
- 인덱스 형태로 미리 계산되어 있는 결과를 가지고 처리하므로 성능 향상 기대 쿼리 수행 시 해당 함수나 수식을 처리하여 결과 가져 오는 것 아님!!
- 인덱스 되어 있는 컬럼이라도 이들을 where조건에서 연산하면 기존의 인덱스 사용 NO!! 즉, LOWER(), UPPER() 등의 함수 사용으로 FULL TABLE SCAN을 하는 경우에도 효과적으로 처리해 줄 수 있는 방법
제약사항
- aggregate function(집계함수. ex) sum(...)) 에 대한 function-based index 생성 불가
- LOB, REF, nested table 컬럼에 대한 function-based index 생성 불가
특징
- cost-based optimizer에서 사용 가능
- B*Tree / bitmap index로 생성 가능
- 산술식(arithmetic expression), PLSQL function, SQL built-in function 등에 적용 가능
- 함수나 수식으로 처리된 결과에 대한 range scan 가능
- NLS SORT 지원
- SELECT/DELETE를 할 때마다 함수나 수식의 결과를 계산하는 것이 아니라 INSERT/UPDATE 시 계산된 값을 인덱스에 저장
- 쿼리 속도 향상
- object column이나 REF column에 대해서는 해당 object에 정의된 방법에 대해 function-based index 생성 가능
- FBI를 생성하기 위해서는 QUERY REWRITE 권한이 부여 되어 있어야 함
생성 방법
- FBI는 CBO에서만 사용 가능하므로, 사전 작업
- 해당 테이블을 미리 analyze 한다
- instance level : init 파일에 【 OPTIMIZER_MODE = FIRST_ROWS || ALL_ROWS 】 지정
- SQL level : HINT 사용
- COMPATIBLE 설정 : init 파일에서 COMPATIBLE = 8.1 이상 ex) COMPATIBLE = 10.2.0.0.0
- QUERY_REWRITE_ENABLED 설정 : 【 QUERY_REWRITE_ENABLED = true 】로 설정(session/instance level)
- ex) ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
문법
CREATE BITMAP INDEX <index_name> ON <tablename> (<index-expression-list>) <index-expression-list> -> 【 <column_name> | <column_expression> 】
- 예제1
GRANT QUERY REWRITE TO SCOTT; CREATE INDEX EMP_NAME_INDEX ON EMP (UPPER(ENAME)); CREATE INDEX EMP_SAL_INDEX ON EMP( SAL + COMM, empno); CREATE INDEX sales_margin_idx ON sales(revenue - cost) ;
- 예제2
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE ; SELECT ordid FROM sales WHERE (revenue - cost) > 1000 ; ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE ;
확인
- DBA_INDEXES 또는 USER_INDEXES에서 【 FUNCIDX_STATUS = ENABLED 】 되어 있으면 FBI 임
select index_name, table_name, FUNCIDX_STATUS from user_indexes; SELECT INDEX_NAME, COLUMN_NAME, DESCEND FROM DBA_IND_COLUMNS WHERE INDEX_OWNER = 'SCOTT';
테스트
테스트 전 확인사항
- init 파라미터 설정 : 기준 값
- compatible = 10.2.0.1.0(반드시 8.1이상으로 설정)
- query_rewrite_enabled = true
- query_rewrite_integrity = trusted
conn scott/loveora
show parameter query_rewrite
NAME TYPE VALUE
------------------------------------ ----------- ----------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
show parameter comp
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
compatible string 10.2.0.1.0
nls_comp string
plsql_compiler_flags string INTERPRETED, NON_DEBUG
plsql_v2_compatibility boolean FALSE
alter session set query_rewrite_integrity=trusted;
Session altered.
테이블 생성
create table t as select 'Y' processed_flag, a.* from all_objects a; Table created.
view 생성
create or replace view v as select t.*, case when processed_flag='N' then 'N' else NULL end processed_flag_indexed from t; View created.
인덱스 생성 및 분석
create index t_idx on t(case when processed_flag = 'N' then 'N' else NULL end); Index created. analyze index t_idx validate structure; Index analyzed.
- 분석 결과 확인하기 : 최초 인덱스에는 아무것도 없음
SQL> col name for a10 SQL> col del_lf_rorws for 999 SQL> col lf_rows for 99999 SQL> col lf_blks for 99999 SQL> select name, del_lf_rows,lf_rows,lf_blks from index_stats; NAME DEL_LF_ROWS LF_ROWS LF_BLKS ---------- ----------- ------- ------- T_IDX 0 0 1
- 데이터 수정과 인덱스 변화
- processed_flag 컬럼을 100개 update 하면, 인덱스 엔트리(LF_ROWS)는 100개 생성됨
SQL> update t set processed_flag='N' where rownum <= 100; 100 rows updated. SQL> analyze index t_idx validate structure; Index analyzed. SQL> select name, del_lf_rows,lf_rows,lf_blks from index_stats; NAME DEL_LF_ROWS LF_ROWS LF_BLKS ---------- ----------- ------- ------- T_IDX 0 100 1
- FBI 사용을 위한 테이블 분석
analyze table t compute statistics for table for all indexes for all indexed columns SQL> / Table analyzed. column rowid new_val r select rowid,object_name from v where processed_flag_indexed='N' and rownum=1; ROWID OBJECT_NAME ------------------ ------------------------------ AAAM+vAAEAAAxkkAAA DUAL
- processed_flag의 수정 후 바로 trace 결과 확인
SQL> update v.processed_flag = 'Y'
2 set processed_flag='Y'
3 where rowid='&R';
old 3: where rowid='&R'
new 3: where rowid='AAAM+vAAEAAAxkkAAA'
1 row updated.
- autotrace 확인
set timing on set time on set autotrace on select rowid, object_name from v where processed_flag_indexed='N' and rownum=1; ROWID OBJECT_NAME ------------------ ------------------------------ AAAM+vAAEAAAxkkAAB DUAL Elapsed: 00:00:00.03 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 32 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IDX | 99 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------
- FIG 1. 시간에 따라 인덱스 엔트리가 변하므로 실제로 쿼리마다 여러번의 I/O 필요
- processed_flag 수정 & 인덱스 분석 후 trace 결과 확인
update v set processed_flag='Y' where rowid='&R';
old 3: where rowid='&R'
new 3: where rowid='AAAM+vAAEAAAxkkAAB'
1 row updated.
analyze index t_idx validate structure;
Index analyzed.
select name,del_lf_rows,lf_rows,lf_blks from index_stats;
NAME DEL_LF_ROWS LF_ROWS LF_BLKS
---------- ----------- ------- -------
T_IDX 2 100 1
- 100개(LF_ROWS) 중에서 2개(DEL_LF_ROWS)는 삭제되어 재사용 가능
- 다른 세션에서 데이터 삽입
insert into t select 'N' processed_flag, a.* from all_objects a where rownum <=2; 2 rows created. analyze index t_idx validate structure; Index analyzed. select name,del_lf_rows,lf_rows,lf_blks from index_stats; NAME DEL_LF_ROWS LF_ROWS LF_BLKS ---------- ----------- ------- ------- T_IDX 0 100 1 set autotrace on select rowid, object_name from v where processed_flag_indexed='N' and rownum=1; ROWID OBJECT_NAME ------------------ ------------------------------ AAAM+vAAEAAAxkkAAC SYSTEM_PRIVILEGE_MAP
- unique index
- 테스트 테이블 및 인덱스 생성
create table project (project_ID number primary key, teamid number, job varchar2(100), status varchar2(20) check(status in ('ACTIVE','INACTIVE'))); Table created. create unique index job_unique_in_teamid on project (case when status = 'ACTIVE' then teamid else null end, case when status = 'ACTIVE' then job else null end) / Index created.
- unique 제약조건에 위배
insert into project(project_id,teamid,job,status) values(1,10,'a','ACTIVE'); 1 row created. insert into project(project_id,teamid,job,status) values(2,10,'a','ACTIVE'); insert into project(project_id,teamid,job,status) values(2,10,'a','ACTIVE') * ERROR at line 1: ORA-00001: unique constraint (SCOTT.JOB_UNIQUE_IN_TEAMID) violated update project set status = 'INACTIVE' where project_id=1 and teamid=10 and status='ACTIVE'; 1 row updated. insert into project(project_id,teamid,job,status) values(2,10,'a','ACTIVE'); 1 row created.
- Plan table에서 실행계획 확인하기
create index idx_emp_lower_ename on emp ( lower(ename) ) ; Index created. analyze table emp compute statistics ; Table analyzed.
- optimizer_mode 확인
show parameter optimizer_mode
NAME VALUE
=============== ==========
optimizer_mode ALL_ROWS
if) optimizer_mode가 ALL_ROWS or FIRST_ROWS로 설정되어 있지 않을 경우
alter session set optimizer_mode = FIRST_ROWS;
alter session set optimizer_mode = ALL_ROWS;
- PLAN Table 만들기
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql
- Query 실행
explain plan set statement_id='qry1' FOR select empno, ename from emp where lower(ename) = 'ford' ;
- PLAN 분석
SELECT LPAD(' ',2*level-2)||operation||' '||options||' '||object_name query_plan FROM plan_table WHERE statement_id='qry1' CONNECT BY prior id = parent_id START WITH id = 0 order by id ; QUERY_PLAN -------------------------------------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID EMP INDEX RANGE SCAN IDX_EMP_LOWER_ENAME
- view 조회시 실행계획 보기
explain plan set statement_id='qry2' FOR select rowid, object_name from v where processed_flag_indexed='N' and rownum=1 Explained. SELECT LPAD(' ',2*level-2)||operation||' '||options||' '||object_name query_plan FROM plan_table WHERE statement_id='qry2' CONNECT BY prior id = parent_id START WITH id = 0 order by id QUERY_PLAN -------------------------------------------------------------------------------- SELECT STATEMENT COUNT STOPKEY COUNT STOPKEY TABLE ACCESS BY INDEX ROWID T TABLE ACCESS BY INDEX ROWID T TABLE ACCESS BY INDEX ROWID T TABLE ACCESS BY INDEX ROWID T INDEX RANGE SCAN T_IDX INDEX RANGE SCAN T_IDX INDEX RANGE SCAN T_IDX INDEX RANGE SCAN T_IDX INDEX RANGE SCAN T_IDX INDEX RANGE SCAN T_IDX INDEX RANGE SCAN T_IDX INDEX RANGE SCAN T_IDX 15 rows selected.
Reference Documents
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3900977&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.