Programming/Microsoft SQL Server

일반적으로 텍스트 검색시 사용하는 쿼리로 Like검색을 들 수 있습니다.

 

Select *
From [Sales].[OrderLines]
Where [Description] Like '%car%';

 

그런데 위와 같은 방식은 테이블스캔작업이 발생되기에 행(Row)이 많으면 원하는 내용을 찾는데 오랜 시간이 소모될 것입니다. 물론 인덱스를 타게 할 수도 있으나 car%와 같은 방법으로 해야만 해서 내용 중간에 'car'가 있는 데이터는 찾을 수 없고 더군다나 무작위의 문자열을 다루는 열(Column)에는 특별한 경우가 아닌이상 인덱스를 걸어놓지 않습니다.

 

결국 신문기사 검색과 같은 텍스트검색을 위해서는 Like이외에 다른 방법이 필요하게 됐고 보통의 인덱스처럼 열(Column)에 인덱스를 적용하는 것이 아니라 특정 단어나 문장을 대상으로 인덱스를 적용하는 '전체 텍스트 검색'을 사용자에게 제공하게 되었습니다.

 

전체 텍스트 검색기능을 이용하려면 일단 서버에 SQL Full-text Filter Daemon 서비스가 실행중이어야 하는데

 

 

이 서비스는 SQL Server를 설치할때 'Full-Text and Semantic Extractions for Search'항목을 옵션으로 지정해 설치를 해야 합니다.

 

 

이것으로 보아 전체 텍스트 검색을 위한 엔진이 별도로 존재한다는 것을 알 수 있을 것입니다. 만약 사용자가 전체 텍스트 검색을 위한 쿼리를 실행하면 전체 텍스트 엔진은 전체 텍스트 인덱스를 검색해 해당 열을 찾아 그 결과를 SQL Server 서비스에 알려주고 서비스는 그 열의 내용을 가져와 사용자에게 반환하는 방식으로 전체 텍스트검색을 수행하게 됩니다.

 

그럼 실제 검색할 문자열이 존재하는 테이블을 대상으로 전체 텍스트 검색을 사용해 보겠습니다. 아래 예제의 대상은 WideWorldLmporters라는 Sample 데이터베이스를 사용한 것입니다.

 

전체 텍스트 검색사용을 위해서는 우선 전체 텍스트 카탈로그를 생성해야 합니다. 이는 전체 텍스트 인덱스를 저장하고 있는 저장소 역활을 수행합니다.

 

Create Fulltext Catalog SalesOrderLinesCatalog As Default;

 

카타로그는 Create Fulltext Catalog 구문을 사용하며 SalesOrderLinesCatalog라는 이름으로 카타로그를 생성하였습니다. 참고로 sys.fulltext_catalogs 테이블을 살펴보면 생성한 카타로그의 정보를 확인해 볼 수 있습니다.

 

Select *
From sys.fulltext_catalogs;

 

만약 생성한 카탈로그의 is_default값이 1이면 해당 카탈로그가 기본 카탈로그임을 의미하는 것입니다.

 

카탈로그 삭제는 Drop FullText를 사용합니다.

 

Drop Fulltext Catalog SalesOrderLinesCatalog

 

카탈로그를 생성하고 나면 이제 해당 카탈로그에 전체 텍스트검색 인덱스를 생성해야 합니다.

 

Create FullText Index On Sales.OrderLines([Description])
Key Index PK_Sales_OrderLines
On SalesOrderLinesCatalog
With Change_Tracking Auto;

 

위 예제에서 인덱스는 Sales.OrderLines 테이블의 Description 열에 생성하였으며 전체 텍스트 검색엔진이 검색후 결과를 PK_Sales_OrderLines 인덱스키로 SQL Server 서비스에 반환하도록 하였습니다. 즉, 전체 텍스트 검색엔진은 검색 후에 그 내용자체가 아닌 해당 내용이 있는 열을 가리키고 있는 키값을 반환하기 때문에 전체 텍스트 인덱스를 생성하려고 하는 테이블은 반드시 값이 중복될 수 없는 Primary Key나 Unique Key를 가지고 있어야 합니다. 참고로 Description 열은 nVarChar형인데 char, nChar, text, nText, xml등 문자열이 들어가는 거의 모든 열에 지정이 가능합니다. 또한 일반 인덱스와 다르게 전체 텍스트 인덱스는 테이블당 하나만 생성이 가능합니다.

 

혹시 인덱스 생성시 Key Index로 어떤걸 지정해야 할지 모르는 경우라면

 

Select [name]
From sysindexes
Where OBJECT_ID('Sales.OrderLines') = id;

 

처럼 특정 테이블의 개체를 확인하고 PK로 시작하는 이름을 확인하시기 바랍니다.

 

그리고 해당 인덱스는 On구문을 통하여 이전에 만들어둔 전체 텍스트 카탈로그인 SalesOrderLinesCatalog에 생성되도록 합니다. 다만 SalesOrderLinesCatalog는 현재 기본 카탈로그이므로 생략이 가능합니다.

 

그리고 마지막으로 만약 실제 테이블의 값(Sales.OrderLines의 Description열)이 변경되는 경우 이를 인덱스에도 자동으로 반영되도록 Change_Tracing 옵션을 Auto로 지정하였습니다. 성능이 문제가 되는 경우라면 Auto대신 Munual을 지정해 변경데이터를 수동으로 적용시켜 주는 방법도 있으며 Off를 통해 아예 변경내용을 업데이트하지 않도록 처리할 수도 있습니다.

 

인덱스를 생성 후 sys.fulltext_indexes 테이블을 통해 인덱스의 상황을 확인할 수 있습니다.

 

Select *
From sys.fulltext_indexes;

 

만약 결과에서 is_enabled가 1이라면 현재 해당 인덱스는 활성화중이라는 것을 의미합니다.

 

인덱스를 생성하고 나면 어떤 단어들이 인덱스로 생성되었는가를 확인할 수 있습니다.

 

Select *
From sys.dm_fts_index_keywords(DB_ID(), OBJECT_ID('Sales.OrderLines'));

 

위 결과에서 display_term은 생성된 단어들 그리고 column_id는 해당 단어가 들어간 열의 순서값(2라면 2번째열), 마지막으로 document_count는 해당 단어가 몇번이나 들어가 있는지를 나타냅니다.

 

단어를 나열해 보면 가령 필요없는 단어들까지 인덱스로 만들어놓은걸 확인할 수 있습니다. 예들 들어 'ta' 이라는 단어까지 인덱스로 만들필요가 없다면 이들 단어는 중지목록에 포함시켜 인덱스에서 제외할 수 있습니다.

 

특정 단어를 중지목록에 넣으려면 우선 중지목록자체를 생성해야 합니다. SSMS(SQL Server Management Studio)에서 Object Explorer창을 통해 원하는 DB의 Storage->Full Text Stoplists 항목을 찾아가 마우스 오른쪽 버튼을 눌러 'New Full-Text Stoplist'메뉴를 선택합니다.

 

 

Full-text stoplist name에 적절한 목록이름을 입력하고 아래 옵션중 원하는 방식으로 목록옵션을 선택합니다. 옵션중에서 Create an empty stoplist는 말 그대로 빈목록을 만들고 거기에서 시작하겠다는 의미이며 Create from the system stoplist는 이미 시스템상에 만들어져 있는 목록에서 추가하는 형태로 새로운 목록을 만들겠다는 것을 의미합니다. 마지막으로 Create from an existing full-text stoplist는 다른 곳에서 만들어진 목록을 가져와 추가하는 형태로 목록을 만드는 경우 선택하는 옵션인데 이들은 인덱스를 생성하고자 하는 목적에 따라 적절이 선택하시면 됩니다. 예제에서는 이름을 mystoplist하고 Create an empty stoplist를 선택하도록 하겠습니다.

 

 

중지목록을 만들고 나면 위에서 처럼 목록이 생성되는 것을 확인할 수 있습니다. 아이콘 모양을 언뜻보면 뭔가 중지된 느낌이 들지만 중지목록이라는 의미로 모양자체가 원래 그런것이니 신경쓰지 않아도 됩니다.

 

이 작업은 아래 쿼리를 통해서도 동일하게 처리할 수 있습니다.

 

Create FullText Stoplist mystoplist;

 

혹은 시스템 목록에서 만들고자 하는 경우

 

Create FullText Stoplist mystoplist From System StopList;

 

이제 목록에 인덱스에서 예외로 하고자 하는 단어를 추가해 봅시다. 목록에서 마우스 오른쪽 버튼을 눌러 Properties(속성)을 선택합니다.

 

 

위 화면에서 예외로 하고자 하는 단어를 입력하고 OK(확인)버튼을 누르면 해당 단어가 추가됩니다. 이 작업은 아래 쿼리를 통해서도 동일하게 처리할 수 있습니다.

 

Alter FullText StopList mystoplist Add 'ta' Language 'English';

 

이제 기존의 만들어둔 인덱스를 삭제하고

 

Drop FullText Index On Sales.OrderLines;

 

새롭게 인덱스를 구성해 보겠습니다. 쿼리는 이전과 완전히 동일하지만 중지목록에 있는 단어들은 제외하도록 지정해야 합니다.

 

Create FullText Index On Sales.OrderLines([Description])
Key Index PK_Sales_OrderLines
On SalesOrderLinesCatalog
With Change_Tracking Auto, StopList = mystoplist;

 

위에서 처럼 인덱스를 생성하고 나면 중지목록에 있는 단어는 더이상 포함되어 있지 않음을 확인할 수 있습니다.

 

인덱스를 생성하였으면 이제 테이블에서 전체텍스트 검색을 통해 데이터를 찾아보로고 하겠습니다.

 

Select *
From Sales.OrderLines
Where CONTAINS([Description], 'to');

 

Description 컬럼에 to라는 단어가 들어간 모든 데이터를 찾도록 합니다.

 

실행계획에서 FullTextMatch를 확인해 볼 수 있습니다.

 

Select *
From Sales.OrderLines
Where Contains([Description], 'to*');

 

to로 시작하는 단어를 찾습니다.

 

Select *
From Sales.OrderLines
Where CONTAINS([Description], 'to And become');

 

to 와 become 두개가 동시에 들어간 데이터를 찾습니다.

 

Select *
From Sales.OrderLines
Where Contains([Description], 'Near((to, become), 2)');

 

to와 become라는 단어가 서로 근접한것을 찾습니다. 이때 이 두단어 사이에 최대 2개까지의 단어만 포함하도록 합니다.

 

Select *
From Sales.OrderLines
Where Contains([Description], 'IsAbout (to weight (1.0), become weight (0.5))');

 

가중치를 부여한 검색을 수행합니다. 0~1까지 지정이 가능하며 숫자가 높을수록 가중치를 많이 부여합니다.

 

Select *
From Sales.OrderLines
Where CONTAINS([Description], 'to Or box');

 

to 또는 box단어가 존재하는 데이터를 찾습니다.

 

Select *
From Sales.OrderLines
Where FreeText([Description], 'press F1 to');

 

FreeText는 빗스한 내용을 찾아주는 역활을 수행합니다. 예제에서 press F1 to로 지정했는데 이와 완전히 동일하지 않더라도 비슷한 내용이 있으면 그 결과를 반환할 것입니다.

 

Select *
From ContainsTable(Sales.OrderLines, [Description], 'to');

 

ContainsTable은 지정한 열을 포함하는 키값별로 지정한 단어를 가장 많이 그리고 정확하게 포함하고 있는 가중치값을 표현합니다. 비슷한 기능으로 FreeTextTable이 있으며 개념은 FreeText와 같습니다.

'Programming > Microsoft SQL Server' 카테고리의 다른 글

인덱스(Index) - 1  (0) 2019.03.26
[SQL] 전체 텍스트 검색  (0) 2018.09.27
트랜잭션 (Transaction)  (0) 2018.09.12
분산 트랜잭션 설정  (0) 2018.07.11
[SQL] 기본언어확인및 변경  (0) 2018.01.30
[SQL] 스키마(Schema)  (0) 2018.01.23
0 0