SQL Server 2k8 Полнотекстовый поиск по "несвязанным" таблицам с использованием представления или?

Question

Я довольно новичок в полнотекстовом поиске, и мне бы очень хотелось узнать, как лучше всего выполнить полнотекстовый поиск в стиле "поиска по сайту" по нескольким несвязанным таблицам (я планирую сделать это по 4 таблицам). Я думал о том, чтобы использовать вид так:

CREATE VIEW [dbo].[Search] WITH SCHEMABINDING
    AS

        SELECT   p.ProductId AS ItemId
            ,'Product' AS ItemType
            ,p.Title AS ItemTitle
            ,p.LongDescription AS LongDescription
            ,p.Price AS Price
    FROM dbo.Product AS p
    WHERE p.IsActive = 1

    UNION

    SELECT   a.ArticleId AS ItemId
            ,'Article' AS ItemType
            ,a.ArticleTitle AS ItemTitle
            ,a.Contents AS LongDescription
            ,NULL AS Price
    FROM dbo.Article AS a
    WHERE a.IsActive = 1

Но, изучая правильный синтаксис для индекса, я понял, что "a" мне нужен уникальный индекс, а "b", очевидно, представления с Unions нельзя использовать для создания полнотекстового индекса...

Альтернативный подход, который я видел, состоял в том, чтобы создать FTI для каждой таблицы, затем в сохраненном процессе объединить их в таблицу tmp и затем выбрать таблицу tmp с ранжированием по порядку.

Я был бы очень признателен за некоторые рекомендации по этому вопросу. Большинство из того, что я нашел, относилось к нескольким связанным таблицам, где объединения с представлением достаточно для преодоления проблемы.

РЕДАКТИРОВАТЬ:

@Joe любезно ответил на этот вопрос, о котором я забыл и на самом деле решил каким-то образом, но был обеспокоен тем, что это было немного затянуто, кажется, что это может быть наиболее логичным из двух предложенных им способов, и вот что я ' Я использую - я полностью забыл, что мне пришлось разбить его на страницы... Я не думаю, что клиент был бы в восторге от бесконечного списка результатов...

Мой коллега также предложил другой метод, который, как он видел, реализовал, который заключается в том, чтобы бросать метаданные в таблицы и в основном кэшировать результаты в другой таблице, в которой вы затем выполняете полнотекстовый поиск, это не так уж плохо, если вы знаете, что такое метаданные будут плюс, вам также все еще нужно будет привязать их к исходным таблицам, чтобы получить фактический результат либо сразу, либо для отображения (полная статья, если нужно, так сказать)

    CREATE PROCEDURE [dbo].[up_Search]
     @Term VARCHAR(100)
    ,@Skip INT = 0
    ,@Take INT = 10
AS
DECLARE @Search TABLE
(
     ItemId INT
    ,ItemType VARCHAR(50)
    ,ItemTitle VARCHAR(100)
    ,LongDescription VARCHAR(MAX)
    ,Price DECIMAL(10,2)
    ,SearchRank INT
)

INSERT INTO @Search SELECT * FROM (

    SELECT   p.ProductId AS ItemId
            ,'Product' AS ItemType
            ,p.Title AS ItemTitle
            ,p.LongDescription AS LongDescription
            ,p.Price AS Price
            ,KEY_TBL.RANK AS SearchRank
    FROM dbo.Product AS p
    INNER JOIN CONTAINSTABLE(dbo.Product, Title, @Term) AS KEY_TBL ON p.ProductId = KEY_TBL.[KEY]
    WHERE p.IsActive = 1

    UNION

    SELECT   a.ArticleId AS ItemId
            ,'Article' AS ItemType
            ,a.ArticleTitle AS ItemTitle
            ,a.Contents AS LongDescription
            ,NULL AS Price
            ,KEY_TBL.RANK AS SearchRank
    FROM dbo.Article AS a
    INNER JOIN CONTAINSTABLE(dbo.Article, ArticleTitle, @Term) AS KEY_TBL ON a.ArticleId = KEY_TBL.[KEY]
    WHERE a.IsActive = 1

    UNION    

    SELECT   n.NewsId AS ItemId
            ,'News' AS ItemType
            ,n.NewsTitle AS ItemTitle
            ,n.Contents AS LongDescription
            ,NULL AS Price
            ,KEY_TBL.RANK AS SearchRank
    FROM dbo.News AS n
    INNER JOIN CONTAINSTABLE(dbo.News, NewsTitle, @Term) AS KEY_TBL ON n.NewsId = KEY_TBL.[KEY]
    WHERE n.IsActive = 1

    UNION

    SELECT   b.BusinessId AS ItemId
            ,bt.Title AS ItemType
            ,b.Title AS ItemTitle
            ,b.LongDescription AS LongDescription
            ,NULL AS Price
            ,KEY_TBL.RANK AS SearchRank
    FROM dbo.Business AS b
    INNER JOIN CONTAINSTABLE(dbo.Business, Title, @Term) AS KEY_TBL ON b.BusinessId = KEY_TBL.[KEY]
    INNER JOIN dbo.BusinessType AS bt ON b.BusinessTypeId = bt.BusinessTypeId
    WHERE b.IsActive = 1
) AS tmp;

WITH SearchCT AS
(
    SELECT   ItemId
            ,ItemType
            ,ItemTitle
            ,LongDescription
            ,Price
            ,SearchRank
            ,ROW_NUMBER() OVER (ORDER BY SearchRank DESC) AS RowNumber
            ,COUNT(*) OVER () AS RecordCount
    FROM @Search
)
SELECT ItemId, ItemType, ItemTitle, LongDescription, SearchRank, RowNumber, RecordCount
FROM SearchCT
WHERE RowNumber BETWEEN @Skip + 1 AND (@Skip + @Take)
ORDER BY RowNumber

ВОЗВРАТ 0

Answer 1

Я думаю, у вас есть два основных подхода:

1) Объедините четыре таблицы в одну таблицу и выполните поиск по этой таблице. Вам нужно будет иметь уникальный идентификатор первичного ключа в этой таблице. Таким образом, структура таблицы будет похожа на индексированное представление, которое вы рассматриваете, и будет выглядеть примерно так:

CREATE TABLE AggregatedTable
(
    Id int IDENTITY(1,1) primary key,
    ItemId int,
    ItemType nvarchar(50),
    ItemTitle nvarchar(255),
    LongDescription nvarchar(max),
    IsActive int
)

Затем вам нужно будет создать полнотекстовый индекс для столбца LongDescription.

Преимущества этого подхода в том, что вы можете выполнять полнотекстовый поиск по одной таблице в одном запросе, например:

SELECT Id, ItemId, ItemType, ct.RANK      
    FROM dbo.AggregateTable AS a INNER JOIN 
    CONTAINSTABLE (AggregateTable , *, '(light NEAR aluminum)',   1033) AS ct
        ON a.ItemId= ct.[KEY]
WHERE IsActive = 1
ORDER BY ct.RANK desc

Недостатки этого подхода: 1. Вам придется периодически запускать задание для загрузки данных из 4 базовых таблиц в вашу агрегированную таблицу. 2. Вы будете использовать вдвое больше дискового пространства.

Второй подход заключается в том, чтобы сохранить данные в четырех отдельных таблицах, а затем написать запросы FTS, которые объединяют результаты из четырех таблиц. Вы должны иметь возможность ранжировать результаты по релевантности, а затем взять N наиболее релевантных результатов. Вам нужно написать что-то следующее:

SELECT   p.ProductId AS ItemId, 'Product' AS ItemType, ct.RANK 'Rank'       
    FROM dbo.Product AS p INNER JOIN 
    CONTAINSTABLE (Product, *, '(light NEAR aluminum)',   1033) AS ct
        ON p.ProductId = ct.[KEY]
WHERE p.IsActive = 1
UNION
SELECT   a.ArticleId AS ItemId, 'Article' AS ItemType, ct.RANK  
      CONTAINSTABLE (Article, *, '(light NEAR aluminum)',   1033) AS ct
        ON p.ProductId = ct.[KEY]  
    FROM dbo.Article AS a
    WHERE a.IsActive = 1
    ORDER BY 'Rank' DESC
UNION ... other two tables

Преимущество этого подхода заключается в том, что вам не нужно иметь задания, которые объединяют содержимое из четырех таблиц в одну таблицу.

Недостатком является то, что ваши запросы являются более сложными, так как они должны объединить результаты из четырех запросов.

Я бы склонялся ко второму подходу. Я думаю, что это проще и проще в обслуживании, а запросы UNION просты в построении.