대소문자 구분없이 검색하기
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
Linux (Kernel 2.4.18-14)
어떤 사이트에서 maddog이라는 ID를 가지는 사용자를 찾는다고 하자.
다음과 같이 할 것이다.
select * from member where id='maddog';
그럼 이런 경우는 어떤가? Maddog, MadDog, maddog 등 대소문자 관계없이 찾고 싶다면?
보통 아래처럼 질의를 작성할 것이다.
select * from member where upper(ID) = upper('maddog');
제대로 된 질의로 보이는가?
뭔가 이상함을 느끼지 못한다면 당신의 사이트는 심각한 문제를 가질 수 있다.
ID컬럼에 대해 upper함수를 먹이면 ID컬럼에 걸린 인덱스를 타지 못하므로 질의성능이 상당히 떨어질 것이고 이로인해 오랫동안 SQL*Plus화면을 쳐다보고 있어야 할 것이다.
암튼 대강의 상황은 파악되었으리라.
그럼 요지는 위의 질의에 대해 인덱스를 태우게 만드는 것이라 할 수 있다.
이렇때 사용할 수 있는 것이 function-based index이다.
말그대로 인덱스된 컬럼에 대해 함수를 먹인 값을 인덱스키로 저장한다는 것이다.
이 기능은 8i부터 사용가능하다.
SQL> conn sys/xxxxxx as sysdba
연결되었습니다.
autotrace를 이용하기 위해, function based index와는 직접적인 관련은 없다
위의 질의가 인덱스를 타는지 파악하기 위해 autotrace를 사용할 것이다.
SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
1행에 오류:
ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다
SQL> create role plustrace;
롤이 생성되었습니다.
SQL>
SQL> grant select on v_$sesstat to plustrace;
권한이 부여되었습니다.
SQL> grant select on v_$statname to plustrace;
권한이 부여되었습니다.
SQL> grant select on v_$session to plustrace;
권한이 부여되었습니다.
SQL> grant plustrace to dba with admin option;
권한이 부여되었습니다.
SQL> set echo off
테스트할 사용자로 maddog을 만든다
SQL> create user maddog identified by maddog
2 default tablespace users
3 temporary tablespace temp;
SQL> grant connect, resource, plustrace, query rewrite to maddog;
권한이 부여되었습니다.
사용자 maddog으로 연결하여 member테이블을 만들고, function-based index를 생성하기 위해
테스트 데이터를 몇개 집어 넣는다.
SQL> conn maddog/maddog
SQL> create table member(id varchar(30), name varchar(30));
테이블이 생성되었습니다.
ID컬럼을 Primary key로 한다.
Primary Key컬럼은 기본적으로 B트리 Unique index가 생성된다. 즉, ID컬럼에는 인덱스가 걸린다는 것이다.
SQL> alter table member
2 add constraint member_pk primary key(id);
테이블이 변경되었습니다.
테스트를 위해 all_objects에서 데이터를 가져와 넣는다.
다량의 데이터를 insert줄때, 아래와 같이 append힌트를 주다면 속도가 빠르다.
SQL> insert /*+ append */ into member
2 select object_name, owner from all_objects where object_type='VIEW';
855 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
우리가 함수기반인덱스를 테스트할 때 사용할 데이터를 넣자.
SQL> insert into member values('maddog', '강명규');
1 개의 행이 만들어졌습니다.
SQL> insert into member values('Maddog','강명규');
1 개의 행이 만들어졌습니다.
SQL> insert into member values('MadDog', '강명규');
1 개의 행이 만들어졌습니다.
플랜테이블(plan_table)을 생성한다.
SQL> @?/rdbms/admin/utlxplan.sql
테이블이 생성되었습니다.
autotrace와 execution plan를 활성화하여 질의가 어떻게 돌아가는지 파악할 것이다.
SQL> set autotrace on explain
SQL> select * from member where upper(ID) = upper('maddog');
ID NAME
------------------------------ ------------------------------
maddog 강명규
Maddog 강명규
MadDog 강명규
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MEMBER'
ID컬럼에는 B트리 인덱스가 걸려있으나 인덱스컬럼에 대한 Operation(upper함수사용)으로 인해 FULL 테이블스캔을 하고 있음을 알 수 있다.
이것은 테이블의 모든 row를 다 뒤져봐서 maddog을 찾는다는 의미이다.
이제, function-based index기능을 사용하여 질의의 성능을 높이도록 하자.
SQL> alter session set query_rewrite_enabled=true;
세션이 변경되었습니다.
SQL> alter session set query_rewrite_integrity=trusted;
세션이 변경되었습니다.
function-based 인덱스를 생성한다.
SQL> create index member_id_idx on member(upper(id));
인덱스가 생성되었습니다.
SQL> alter session set optimizer_mode=all_rows;
세션이 변경되었습니다.
SQL> select * from member where upper(ID) = upper('maddog');
ID NAME
------------------------------ ------------------------------
maddog 강명규
Maddog 강명규
MadDog 강명규
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=7 Bytes=238)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER' (Cost=1 Card=7 Bytes=238)
2 1 INDEX (RANGE SCAN) OF 'MEMBER_ID_IDX' (NON-UNIQUE) (Cost=1 Card=3)
위에서 보듯이 동일 질의에 대해 인덱스검색이 이루어졌음을 알 수 있다.
여기에서는 시간을 재워보지 않았는데, 10만개정도의 테스트row를 만들고 SQL*PLUS상에서 set timing on을 주고 위의 질의를 테스트해보면 성능향상을 체감할 수 있을 것이다.
옵티마이저 모드를 first_rows로 변경하고 해보자. 결과는 별 차이 없다.
SQL> alter session set optimizer_mode=first_rows;
세션이 변경되었습니다.
SQL> select * from member where upper(ID) = upper('maddog');
ID NAME
------------------------------ ------------------------------
maddog 강명규
Maddog 강명규
MadDog 강명규
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=7 Bytes=238)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER' (Cost=1 Card=7 Bytes=238)
2 1 INDEX (RANGE SCAN) OF 'MEMBER_ID_IDX' (NON-UNIQUE) (Cost=1 Card=3)
이제, 테이블을 analyze후에 다시 해보자.
SQL> analyze table member compute statistics;
테이블이 분석되었습니다.
SQL> select * from member where upper(ID) = upper('maddog');
ID NAME
------------------------------ ------------------------------
maddog 강명규
Maddog 강명규
MadDog 강명규
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MEMBER' (Cost=2 Card=1 Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'MEMBER_ID_IDX' (NON-UNIQUE) (Cost=1 Card=1)
SQL>
위에서 보듯이, 테이블 분석후, 질의 비용이 상당히 감소했음을 알 수 있다.
function-base index를 사용하면, 주기적으로 테이블을 분석(analyze table)해주는 것이 필요함을 알 수 있다.
[대충 정리]
function based index를 사용하기 위해선
query rewrite권한이 필요하다.
옵티마이저가 CBO(Cost Based Optimize)모드에 있어야 한다.
수시로 테이블을 analyze해준다.(CBO를 사용하므로 당연히 analyze해줘야 할 것이다.)
위에서 보면 analyze전과 후의 결과가 차이가 남을 알 수 있다.
function based index를 이용한 결과에서의 실행계획은 인덱스를 타고 있음을 알 수 있다. |
This article comes from dbakorea.pe.kr (Leave this line as is)
|
