1.Sample Data 입력
create table t_first (col1 varchar2(100));
insert into t_first values('토플');
insert into t_first values('깡다구');
insert into t_first values('거울');
insert into t_first values('땅끝마을');
insert into t_first values('success');
insert into t_first values('듀오백');
insert into t_first values('사랑');
insert into t_first values('나오미');
insert into t_first values('미국');
insert into t_first values('naver');
insert into t_first values('뻥이야');
insert into t_first values('토마토');
insert into t_first values('새해');
insert into t_first values('premature');
insert into t_first values('찜질방');
insert into t_first values('사진');
insert into t_first values('fallen');
insert into t_first values('energy');
insert into t_first values('햄버거');
insert into t_first values('ㄱ');
insert into t_first values('ㄴ');
insert into t_first values('ㄷ');
insert into t_first values('가');
insert into t_first values('나');
insert into t_first values('다');
select * from t_first;
2.Data가 소량이라면 다음처럼 하면 됩니다.
select *
from t_first
where case when col1 < 'ㄱ' then substr(col1, 1, 1)
when ascii('ㄱ') <= ascii(col1) and
ascii(col1)<= ascii('ㅎ') then col1
when col1 < '나' then 'ㄱ'
when col1 < '다' then 'ㄴ'
when col1 < '라' then 'ㄷ'
when col1 < '마' then 'ㄹ'
when col1 < '바' then 'ㅁ'
when col1 < '사' then 'ㅂ'
when col1 < '아' then 'ㅅ'
when col1 < '자' then 'ㅇ'
when col1 < '차' then 'ㅈ'
when col1 < '카' then 'ㅊ'
when col1 < '타' then 'ㅋ'
when col1 < '파' then 'ㅌ'
when col1 < '하' then 'ㅍ'
else 'ㅎ'
end = 'ㄱ'; <== 이 부분을 바꿔가면서 테스트해 보면 됩니다.
select * from t_first
order by col1;
3.그러나 Data의 양이 많고 검색할 때 반드시 인덱스를 이용해야 한다면 다음처럼
해야 합니다. 8i이후부터 가능한 FBI(function-based index)를 활용하는 예제입니다.
이 예제는 dba 권한을 가진 유저로 테스트한 것입니다.
-- 함수 생성
create or replace function sf_ganada
(p_name varchar2)
return varchar2
deterministic
is
v_ret varchar2(10);
begin
v_ret := case when p_name < 'ㄱ' then substr(p_name, 1, 1)
when ascii('ㄱ') <= ascii(p_name) and
ascii(p_name)<= ascii('ㅎ') then p_name
when p_name < '나' then 'ㄱ'
when p_name < '다' then 'ㄴ'
when p_name < '라' then 'ㄷ'
when p_name < '마' then 'ㄹ'
when p_name < '바' then 'ㅁ'
when p_name < '사' then 'ㅂ'
when p_name < '아' then 'ㅅ'
when p_name < '자' then 'ㅇ'
when p_name < '차' then 'ㅈ'
when p_name < '카' then 'ㅊ'
when p_name < '타' then 'ㅋ'
when p_name < '파' then 'ㅌ'
when p_name < '하' then 'ㅍ'
else 'ㅎ'
end;
return v_ret;
end sf_ganada;
/
-- 인덱스 생성
create index t_first_col1_fbi_idx
on t_first(sf_ganada(col1));
-- 통계 생성
analyze table t_first compute statistics
for table
for all indexed columns
for all indexes;
-- 세션 환경 설정
alter session set QUERY_REWRITE_ENABLED=TRUE;
alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
-- 테스트 및 실행계획
select col1, sf_ganada(col1)
from t_first
where sf_ganada(col1) = 'ㄱ';
Execution Plan
----------------------------------------------------------
| 0 |
|
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=36) |
|
| 1 |
0 |
TABLE ACCESS (BY INDEX ROWID) OF 'T_FIRST' (Cost=2 Card=4 Bytes=36) |
|
| 2 |
1 |
INDEX (RANGE SCAN) OF 'T_FIRST_COL1_FBI_IDX' (NON-UNIQUE) (Cost=1 Card=4) |
참고 1 : FBI(function-based index, 함수기반 인덱스)를 사용하려면...
# You must have the following initialization parameters defined
to create a function-based index:
-- QUERY_REWRITE_INTEGRITY set to TRUSTED
-- QUERY_REWRITE_ENABLED set to TRUE
-- COMPATIBLE set to 8.1.0.0.0 or a greater value
# The table must be analyzed after the index is created.
# The query must be guaranteed not to need any NULL values
from the indexed expression, since NULL values are not stored in indexes.
참고 2 : 비슷한 문제 => http://blog.naver.com/orapybubu/40021815464
=================================================================================
