데이터베이스 질문과 답변
REPLACE 함수를 만들었는데....이게 LIKE 검색을 타서....속도가 엄청 안나오는데....방법을 찾고 있습니다.
TEST테이블이 5천만건이고 CODETABLE 테이블이 2천만건 인데..
SELECT FN_TEST(MEMO) FROM TEST 이렇게 했을경우 몇시간이 지나도 결과값이 안나오고...
SELECT FN_TEST(MEMO) FROM TEST WHERE ROWNUM =1
이렇게 한건만 SELECT하는데도.....1분 50초가 걸리네요....
MEMO컬럼이...여러가지 내용을 담고있는데
======================
ex)
853153874 지역 전화
코드번호 2340985324 가입방법
=======================
이렇게 되어있는데
그 문자열속에 코드를 찾으면 CODETABLE 에서 찾아가지고
REPLACE 해야 하거든요...
그래서 LIKE 검색을 FUNCTION 에 넣어놨는데....이게 영 속도가.....엄청 느려서요...
방법을 모르겠어서 올립니다...부탁좀드립니다....ㅠ
CREATE OR REPLACE FUNCTION FN_TEST(MEMO IN VARCHAR2)
RETURN VARCHAR2
IS
v_sq VARCHAR2(4000) := MEMO ;
CURSOR cur1 IS
SELECT ACODE, TCODE
FROM CODETABLE
WHERE MEMO LIKE '%' || ACODE|| '%'
;
BEGIN
FOR c IN cur1
LOOP
v_sq := REPLACE(v_sq, c.ACODE, c.TCODE);
END LOOP;
RETURN v_sq;
EXCEPTION
WHEN OTHERS THEN
RETURN v_sq;
END;
http://wiki.gurubee.net/pages/viewpage.action?pageId=26738860
요건 다른 사이트에서 제가 답변 드린 내용 같군요 ^^
워낙 건수가 많네요.
데이터 많은거야 어쩔수 없다 치더라도...코드가 너무 많네요...
5천만건을 루프 돌려가며 각각 2천만건식 검색하는 형상이네요.
코드 테이블에 인덱스를 만들어야 할 듯 합니다.
흔히 말하는 인덱스는 맨 처음 글자부터 찾지 않고 중간부터 찾으면 인덱스가 무의미합니다.
Full Text Search 이 키워드로 검색해 보세요.
그런데.. Full Text Search 이게 도움이 안될지도 모른다는 생각이 드네요.
검색 조건이 거꾸로네요.(MEMO LIKE '%' || ACODE|| '%')
코드에서 메모를 찾는게 아니라 메모에서 코드를 찾네요.
코드가 2천만건 정도 되면 코드가 포함관계를 맺고 있거나
- 111, 111111, 111222 등이 예가 되구요.
또는 코드가 서로 물리고 물리는 관계가 되거나
- 111 => 222
- 222 => 111 이런식인거죠.
이런 코드들도 존재할 가능성을 배재하기 어려워 보입니다.
워낙 건수가 많다보니 다양한 케이스가 존재하겠지요.
제가 처음 위 PL/SQL 을 만들어 드린건 데이터 건수가 몇건 안된다는 가정으로 만들어 드린 것입니다.
음.. 결론은 전혀 새로운 접근방법을 모색해 봐야 할 듯 하네요.
위 방법으로는 안될 듯 합니다.
select '853153874 지역 전화' memo from dual union all
select '코드번호 2340985324 가입방법' from dual
),
code as (
select '853153874' acode, 'aaaaaa' tcode from dual union all
select '2340985324' acode, 'bbbbbb' tcode from dual
)
select (select tcode
from code b
where b.acode = regexp_substr(a.memo, '[0-9]{1,}', 1)
) tcode
from test a
위 처럼 스칼라 서브쿼리나 아래처럼 인라인뷰를 쓴 방식 중 실행계획이 빠른것으로 한번 해보세요.
select b.tcode
from ( select regexp_substr(memo, '[0-9]{1,}', 1) acode
from test
) a
, code b
where a.acode = b.acode
함수로 파싱부하를 일단 줄여보시는거를..
부쉬맨님 답글은 질문 상황과는 맞지 않는 듯하구요
신이만든짝퉁님 답글은 메모와 코드가 1:1 매칭될때만 가능한 답이네요
메모 하나에 코드가 두개 이상 매칭이 가능한 상황을 가정해서 루프방식으로 푼거거든요.
코드는 숫자로 이루어졌다는 가정으로 푸셨는데...
이부분에 대한 실 데이터를 봐야 접근방법을 모색할 수 있을 듯 합니다.
2천만건으로 건수가 너무 많아 패턴 분석이 쉽지는 않을 것으로 예상되기는 합니다.
혹시 코드값에 일정한 패턴이 있는지요?
오라클은 Oracle full text 검색 일명 "가난한 자를 위한 검색엔진" 있죠
다만 이건 도메인 인덱스로 구축되기 때문 테이블 크기가(정확히는 인덱스를 생성할 컬럼) 어떻게 되냐에 따라서 인덱스 용량이 어마어마하게 증가합니다 그래서 쉽게 적용하기는 쉽지 않습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.