1.전체 텍스트 검색 서비스 개요
-전체텍스트 검색서비스는 SQL 서버의 외부기능이다.
-긴 문장으로 구성된 열의 내용을 검색할 때 외부인덱스를 사용해서 빠른 시간 내에 검색한다.
-시작하는 방법 : SSMS의 관리 - 전체텍스트 검색 - 시작 , 구성관리자에서도 시작할 수 있다.
2.전체 텍스트 인덱스와 전체 텍스트 카탈로그
(1)전체 텍스트 인덱스
-신문 기사와 같이 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스를 말한다.
-파일과 비슷한 개념
(2)SQL 서버의 인덱스와의 차이점
-전체 텍스트 검색엔진이 생성하는 특수 토큰 기반 기능의 인덱스
-외부파일로 저장된다.
-테이블 당 하나만 생성된다.
-char , varchar,nvarchar,text,ntext 등의 열에만 생성이 가능하다.
(3)전체텍스트 카탈로그
-전체 텍스트 인덱스가 저장되는 저장소(폴더와 비슷한 개념)
-전체 텍스트 인덱스가 생성되기 전에 생성해 놓아야 한다.
(4)전체텍스트 인덱스 채우기
-전체 텍스트 인덱스를 생성하고 관리하는 것을 말한다.
[채우는방법]
<전체채우기>
- 처음 전체 텍스트 인덱스를 생성할 때 지정한 열의 모든 데이터 행에 대해서 인덱스를 생성하는 것
<변경 내용 추적 기반 채우기>
-전체 채우기를 수행한 이후에 ,변경된 내용을 채우는 것
<증분 타임스탬프 기반 채우기>
-증분채우기는 마지막 채우기 후 추가,삭제,수정된 행에 대해서 전체 텍스트 인덱스를 업데이트한다.
(5)전체 텍스트 검색을 위한 쿼리
-일반 SELECT 문의 WHERE절에 키워드를 사용하면 된다.
[쿼리문 예제]
(article 열에 전체텍스트 인덱스가 생성되었다고 가정하자)
CONTAINS
-정확히 '영화'라는 단어가 들어간 기사를 찾는 구문
SELECT * FROM newspaper WHERE CONTAINS(article,N'영화');
FREETEXT
-'영화에 출연한 배우'와 비슷한 단어를 포함한 것을 검색
SELECT * FROM newspaper WHERE FREETEXT(article , N'영화에 출연한 배우')
CONTAINSTABLE
-'영화'의 내용이 포함된 인덱스의 키와 해당 순위 확인
SELECT * FROM CONTAINSTABLE(newspaper ,article , N'영화')
3.전체 텍스트 검색 서비스의 작동
(1)작동원리
-쿼리 실행
- SQL서버 엔진
-SQL서버 엔진이 전체텍스트 쿼리를 전체텍스트검색서비스에게 날린다.
-전체텍스트검색서비스는 카탈로그파일로 부터 전체 텍스트인덱스를 검색해서 해당열을 찾아냄
-전체텍스트검색서비스는 검색된 열의 키의 정보를 SQL서버에게 알려준다
- SQL서버는 해당 열을 데이터베이스에서 가져온다.
- 그 값을 사용자에게 돌려준다
(2)전체 텍스트 검색서비스의 사용
USE master;
CREATE DATABASE FullDB;
GO
USE FullDB;
CREATE TABLE FullTbl
(id int
IDENTITY CONSTRAINT pk_id PRIMARY KEY, -- 고유번호
(고유번호가 있어야 된다) title NVARCHAR(10) NOT NULL, -- 영화제목
description NVARCHAR(max) ); -- 영화내용요약
INSERT INTO FullTbl VALUES('천하장사마돈나','척 보기엔 뚱보 소년, 마음만은 마돈나. 우리의 오.동.구.');
INSERT INTO FullTbl VALUES('일본침몰','일본사회를 뒤흔든 거대한 지진 마침내 현실이 된 일본의 멸망');
INSERT INTO FullTbl VALUES('괴물','햇살 가득한 평화로운 한강 둔치 그곳에서 괴물이 나타났다.');
INSERT INTO FullTbl VALUES('뚝방전설','그들은 스스로를 노타치라 칭했고, 세상은 그들을 전설이라 불렀다');
INSERT INTO FullTbl VALUES('센티넬','141년 국가안보국 역사상 최초의 미국대통령암살음모');
INSERT INTO FullTbl VALUES('플라이트93','미국무역센터, 손에 땀을 쥐게하는 긴장감. 펜타곤에 연쇄 충돌한 3대의 민항기');
INSERT INTO FullTbl VALUES('예의없는 것들','세상의 더럽고 추한 예의 없는 것들을 한 방에 날려 버리는 한 판');
INSERT INTO FullTbl VALUES('레이크 하우스','2004년의 남자, 2006년의 여자. 같은 공간, 다른 시간 속 사랑');
INSERT INTO FullTbl VALUES('왕의 남자','세 번의 공연, 그 절체절명의 순간들');
INSERT INTO FullTbl VALUES('투사부일체','투사부일체; 고딩 조폭, 이번엔 교생으로 뜬다');
INSERT INTO FullTbl VALUES('미션 임파서블 3','극악무도한 적과 자신의 조직에 맞서, 성공 불가능한 미션에 도전한다');
INSERT INTO FullTbl VALUES('다빈치 코드','천재화가 다빈치의 작품 속에 숨겨진 충격적인 코드');
INSERT INTO FullTbl VALUES('한반도','일본은 한반도의 경의선 철도 개통을 허가하지 않겠습니다');
INSERT INTO FullTbl VALUES('홀리데이','단 1초라도... 맑은 공기를 마시며 죽고 싶다');
INSERT INTO FullTbl VALUES('킹콩','수백만년 동안 숨어 있던 킹콩과 공룡들이 모습을 드러내면서 탐험대는 위협에 직면한다');
//like를 사용해서 검색 - 성능 (Clustered Index Scan - 전체테이블 스캔과 같다.) - 속도가 많이 느리다.
SELECT * FROM FullTbl
WHERE description LIKE '%일본%'
//카탈로그 생성여부 체크
SELECT * FROM sys.fulltext_catalogs;
//카탈로그 생성 -폴더 개념이다.(여기에는 영화설명에 대한 인덱스만 들어있다)
CREATE FULLTEXT CATALOG movieCatalog AS DEFAULT;
//전체텍스트 생성
CREATE FULLTEXT INDEX ON FullTbl(description) KEY INDEX pk_id ON movieCatalog //카탈로그 지정
WITH CHANGE_TRACKING AUTO ; //이 옵션을 사용하면 ms sql알아서 관리해준다.
//전체텍스트 인덱스 생성 확인
EXEC sp_help_fulltext_tables;
//일치검색
SELECT * FROM FullTbl
WHERE
CONTAINS(description, N'일본');
SELECT * FROM FullTbl
WHERE
CONTAINS(description, N'"일본"
OR "미국"');
SELECT * FROM FullTbl
WHERE
CONTAINS(description, N'"미국"
AND "민항기"');
SELECT * FROM FullTbl
WHERE CONTAINS(description, N'경의선개통');
//자유검색
SELECT * FROM FullTbl
WHERE
FREETEXT(description, N'경의선개통');
//가중치검색
SELECT * FROM
CONTAINSTABLE(FullTbl, *, N'일본')
//보통 가중치 검색은 아래와 같이 조인된 형태로 많이 사용된다.
SELECT f.id, c.RANK AS [가중치], f.title, f.description
FROM FullTbl AS f
INNER JOIN CONTAINSTABLE(FullTbl, *, N'일본') AS c
ON f.id = c.[KEY]
ORDER BY c.RANK DESC
DROP FULLTEXT CATALOG movieCatalog;
//전체텍스트카탈로그는 전체텍스트인덱스를 삭제한 후에 삭제해야 삭제가 된다.
DROP FULLTEXT INDEX on FullTbl;
DROP FULLTEXT CATALOG movieCatalog;
USE FullDB;
SELECT * FROM FullTbl
WHERE CONTAINS(description, N'일본');
BACKUP DATABASE FullDB TO DISK = 'C:\FullDB.bak' WITH INIT
USE master;
DROP DATABASE FullDB;
GO
RESTORE DATABASE FullDB FROM DISK = 'C:\FullDB.bak' WITH REPLACE
USE FullDB;
SELECT * FROM sys.fulltext_catalogs;
USE FullDB;
SELECT * FROM sys.fulltext_catalogs;
USE FullDB;
DROP FULLTEXT INDEX on FullTbl;
DROP FULLTEXT CATALOG movieCatalog;
SELECT * FROM FullTbl
WHERE CONTAINS(description, N'일본');
GO
CREATE FULLTEXT CATALOG movieCatalog AS DEFAULT;
GO
CREATE FULLTEXT INDEX ON FullTbl(description)
KEY INDEX pk_id
ON movieCatalog
WITH CHANGE_TRACKING AUTO ;
GO
BACKUP LOG FullDB WITH NO_LOG -- 카탈로그를 다시 생성하기 위해서 실행(큰 의미 없음)
