SQL 2005 索引视图上的全文索引填充性能
我创建了一个索引视图：
CREATE VIEW LogValueTexts WITH SCHEMABINDING AS
SELECT ISNULL(LRVS_SLOG_ID*256+LRVS_IDX,0) AS ID,LRVS_VALUE AS Value
FROM dbo.LRVS_LogRecordedValues WHERE LEN(LRVS_VALUE)>4
CREATE UNIQUE CLUSTERED INDEX IX_LogValueTexts ON LogValueTexts (ID)
在 SQL 2005 Standard SP3 上，需要永远在该视图上填充全文索引，因为全文索引对视图中的每一行执行以下查询：
SELECT COLUMN FULLTEXTALL FROM[dbo].[LogValueTexts] WHERE COLUMN FULLTEXTKEY = @p1
我假设 COLUMN FULLTEXTALL 和 COLUMN FULLTEXTKEY 实际上是 Value 和 ID，但这就是 SQL Server Profiler 显示的内容。问题在于，查询计划对大约 11M 行/1GB 数据使用聚集索引扫描，因为它不使用视图上的索引。 我已尝试为该查询创建计划指南，但由于它不是标准 T-SQL 查询，因此不允许这样做（关键字“FULLTEXTKEY”附近的语法不正确）。
有没有办法让这个全文索引正常工作，除了：
- 升级到 SQL 2008（或 SQL 2005 Enterprise），它可以正常工作。
- 在基础表上创建唯一 ID 和覆盖索引。
升级需要服务器停机，并且可能需要新的 SQL Server 许可证，同时创建唯一 ID 和覆盖索引会浪费大量空间，因为只有 11M 行的子集需要全文索引 (LRVS_VALUE通常为 NULL 或具有非常短的文本值）。
I have created an indexed view:
CREATE VIEW LogValueTexts WITH SCHEMABINDING AS
SELECT ISNULL(LRVS_SLOG_ID*256+LRVS_IDX,0) AS ID,LRVS_VALUE AS Value
FROM dbo.LRVS_LogRecordedValues WHERE LEN(LRVS_VALUE)>4
CREATE UNIQUE CLUSTERED INDEX IX_LogValueTexts ON LogValueTexts (ID)
On SQL 2005 Standard SP3 it takes forever to populate a full-text index on that view because the full-text indexing executes the following query for every row in the view:
SELECT COLUMN FULLTEXTALL FROM[dbo].[LogValueTexts] WHERE COLUMN FULLTEXTKEY = @p1
I assume that COLUMN FULLTEXTALL and COLUMN FULLTEXTKEY are actually Value and ID, but that's what SQL Server Profiler shows. The problem is that the query plan uses a clustered index scan over about 11M rows/1GB of data because it doesn't use the index on the view.
I have tried creating a plan guide for that query, but since it's not a standard T-SQL query it doesn't allow it (Incorrect syntax near the keyword 'FULLTEXTKEY').
Is there a way to get this full-text index to work other than:
- upgrading to SQL 2008 (or SQL 2005 Enterprise) where it works fine.
- creating a unique ID and a covering index on the underlying table.
Upgrading would require downtime on the server and probably new SQL Server licences while creating the unique ID and a covering index would waste a lot of space because only a subset of the 11M rows needs full-text indexing (LRVS_VALUE is often NULL or has a very short text value).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我不知道你的数据；为什么不能将全文索引放在原始表上？您可以将计算列添加到表结构中。这样你就不会进行索引重建操作（我认为这是你扫描的原因）
如果你不能这样做，那么下一个最简单的更改可能是创建一个用 sp 或触发器填充的查找表，这样你就可以更改表的索引，以便它们对您的查询有意义。
您的最后一个选择（您需要花一些时间才能正确选择）是使用分区表。您可以有一个分区来覆盖视图上过滤的数据。全文索引整个表；您在运行时的查询将命中其中包含相关数据的分区表。
I dont know your data; why cant you put the full text index on the original table? You could add the calculated column into your table structure. That way you wouldnt have the index rebuild operation ( i think that is the cause of your scan)
If you can't do that then next easiest change is likely to create an lookup table populated with an sp or a triggerthat way you can change the table's indexing so they make sense for your query.
Your final option (and one you would need to spend some time on to get right) would be using partitioned tables. You could have a partition that covers the data filtered on the view. Full text index the entire table; your query at run time would hit the partitioned table with the relevant data in it.