이펙티브 오라클 (2009년)
인덱싱 테크닉 0 0 62,760

by 구루비스터디 FBI [2018.05.26]


  1. FUNCTION-BASED INDEX(FBI. ORACLE 8I NEW FEATURE)
    1. 개요
    2. 테스트
    3. Reference Documents


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

"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3489

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

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