database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 7007 게시물 읽기
No. 7007
full-text-search 결과 이상.
작성자
버거버그
작성일
2016-09-11 18:38ⓒ
2016-09-12 07:32ⓜ
조회수
4,093

MSSQL 2014 SP1 (build 12.0.4100.1)에 신규 DB(collation: Korean_Wansung_CI_AS) 를 만들고,

우편번호 테이블(zip) 도로명주소(roadname) 컬럼에 full-text-search index를 생성했습니다.

1. full text search 테스트 테이블(ziptest) 생성 후, full text search index를 생성,

zipkey roadname

------- -----------

1 벚꽃

2 벚꽃로

3 벚꽃길

10 중앙고등

11 성동고등

12 환일고등

 

CREATE FULLTEXT CATALOG ZipFullTextSearch

AS DEFAULT

go

CREATE FULLTEXT INDEX ON dbo.zip(roadname)

KEY INDEX zip_pk

ON ZipFullTextSearch

WITH CHANGE_TRACKING AUTO

go

-----

 

예를 들어 contains(roadname,N'벚꽃로')로 검색건수와 roadname like '%벚꽃로%'로 검색한 건수가 달라 확인해보니,full-text-search CONTAINS를 사용해도 'XX벚꽃길' 같은 결과까지 나와서 당황하고 있습니다.

1. where 조건으로 contains(roadname,N'벚꽃로'), contains(roadname,N'벚꽃'), contains(roadname,N'벚꽃길') 3가지를 수행한 결과 건 수가 똑같은 걸로 봐서는 동일한 결과가 나오는 것 같습니다.

zipkey roadname

------- -----------

1 벚꽃

2 벚꽃로

3 벚꽃길

 

조회시 contains(roadname,'고등')을 조회하면, '성동고등'이 빠집니다.

zipkey roadname

------- -----------

10 중앙고등

12 환일고등

 

 

2. SELECT * FROM sys.dm_fts_parser(N'벚꽃길', 1033, 0, 0) where special_term='Noise Word';

-- 결과를 보니 noise word를 포함하고 있지는 않습니다.

 

3. 다음 2개의 쿼리를 구글링해서 찾았지만, 키워드 hex 코드는 다르게 나옵니다.

SELECT * FROM sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID(N'dbo.zip')) WHERE display_term LIKE N'%벚꽃길%'

SELECT * FROM sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID(N'dbo.zip')) WHERE display_term LIKE N'%벚꽃로%'

 

 

고수님들의 조언을 부탁드립니다.

이 글에 대한 댓글이 총 2건 있습니다.
일반적으로 다음과 같은 내용이 본문에 들어있고
'섹션TV' 김국진 "추석엔 강수지와? 알아서 하겠다" 달달
사용자가 '김국진 강수지 추석'으로 검색하는 경우에 유용한 것이 fulltext search입니다.
LIKE를 이용해서 구현한다면
WHERE textfield LIKE '%김국진%강수지%추석%'
OR textfield LIKE '%김국진%추석%강수지'
OR textfield LIKE '%강수지%김국진%추석%'
OR textfield LIKE '%강수지%추석%김국진%'
OR textfield LIKE '%추석%김국진%강수지%'
OR textfield LIKE '%추석%강수지%김국진%'
와 같은 쿼리문을 생성해야하고, 실행 속도도 좋을 수가 없는 구조가 되기 때문에 fulltext search의 색인으로 (실제 돌려본 결과가 아니라 제가 대충 뽑은) 섹션, TV, 김국진, 추석, 강수지, 알(다), 하(다), 달달 정도를 뽑아 두었다가 사용자가 검색시 매칭이 되는 결과를 뽑아내야 합니다.
 
이 뿐만 아니라 본문에 '대학생선교회 추석에 사랑을 나누러...'라는 문장이 들어가 있는데, 사용자가 '생선 추석'으로 검색했을 때 해당 data가 결과로 나온다면 보통은 검색이 훌륭하다고 하지 않습니다. 따라서 fulltext search의 색인은 (이 역시 제가 손으로 뽑아본) 대학생, 대학, 학생, 선교, 선교회, 추석, 사랑 식으로 때때로는 본문 중의 일부 글자를 중복해서 뽑아야 되기도 합니다.
 
이런 문제를 해결하기 위해서 fulltext search의 index는 본문의 data를 적당히(물론 우리가 원하는 건 정확히 잘~) 자르고 복원하고 등의 작업을 수행해서 구축이 됩니다.
 
'벚꽃길'과 '벚꽃로'의 경우 실행해 보진 않았으나 벚꽃을 색인으로 뽑았기 때문에 벚꽃으로만 검색해도 결과에 나오는 상황이 됩니다. 이때 검색어에 대해서도 동일한 방법으로 색인어 추출하는 과정을 수행합니다.
 
'~~고등'으로 되어 있는 본문을 fulltext search로 검색어가 '고등'인 경우 모두 뽑아내야 한다면 이를 튜닝하셔야 합니다.
 
주소만을 대상으로 하는 등의 도메인이 한정되어 일부만 특수하게 손을 보면 쓸만하겠다 싶으시면 이참에 공부한다 생각하시고 덤비셔도 무방하겠으나 일반적인 게시판과 같은 경우에는 이참에 검색엔진을 도입하시는 것을 심각하게 고려해 보시기를 권장해드립니다. 구축까지 마무리 해주는 상업용 검색엔진(예: 다이퀘스트 등)부터 하나하나 튜닝하기가 SQL보다 편한 오픈소스 검색엔진(루씬 등)이 있습니다.
 
SQL에서 튜닝하는 방법에 대해서는 자세하게는 으로도 있고, 거기에 한국어에 대한 이해도(실제 분석자체를 구현할 부분이 아니라면 링크된 도서의 4부, 5부 위주) 필요한 부분이어서 댓글 하나로 다 설명을 드리기는 힘듭니다.
우욱님이 2016-09-12 08:37에 작성한 댓글입니다.
이 댓글은 2016-09-12 08:40에 마지막으로 수정되었습니다. Edit

우욱님 감사합니다.

 

이미 오픈된 서비스에 새로운 검색엔진 올리는건 무리(해본사람이 없어서)...

DB full-text-search 한글 검색에 대한 한계로 더이상 DB파트에서

full-text-search로 진행하는게 어렵다고 알게 된 것 만으로 정신건강에

도움이 된 거 같습니다.

(4-5일 정도 폐인생활 해서 그런지, 오전엔 마소에 전화까지 해서 환불 협박(?)까지 했었거든요.)

버거버그님이 2016-09-12 10:49에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
7010MS-SQL ORACLE 디비링크 관련 오류 문의 [1]
김도일
2016-10-10
4825
7009mssql에서 프로시져 소스보기 질문입니다
노른자
2016-09-30
2858
7008프로시저에서 개체이름이 잘못되었다고 나옵니다. [1]
둥둥이
2016-09-22
3687
7007full-text-search 결과 이상. [2]
버거버그
2016-09-11
4093
7005시간대별 통계 질문 입니다. [2]
Hanzel
2016-09-02
3191
7004월별 가로 출력 ms-sql 쿼리 질문입니다. 내공100 [1]
김민용
2016-09-01
3189
7003IF NOT EXISTS (...) INSERT ... ELSE UPDATE ... 구문에도 트랜잭션을 써야하나요?
iruis
2016-09-01
3149
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.038초, 이곳 서비스는
	PostgreSQL v14.2로 자료를 관리합니다