BLOB如何存储在索引视图中? [英] How is BLOB stored in an indexed view?

问题描述

问题

假设我在包含varbinary(max)列的表上建立索引视图，是将二进制内容物理复制到索引视图的B-Tree中，还是以某种方式引用"原始字段，而无需实际复制它们内容?

换句话说，如果我在包含BLOB的表上建立索引视图，那将复制BLOB所需的存储吗?

更多详细信息

在二进制数据(例如varbinary(max))上使用全文本索引时，我们需要一个附加的过滤器类型"列来指定如何从该二进制数据中提取文本，以便对其进行索引，如下所示:/p>

CREATE FULLTEXT INDEX ON <table or indexed view> (
    <data column> TYPE COLUMN <type column>
)
...

在我的特殊情况下，这些字段位于不同的表中，并且我试图使用索引视图将它们连接在一起，以便可以在全文索引中使用它们.

当然，我可以将类型字段复制到BLOB表中并手动进行维护(使其与原始表保持同步)，但是我想知道是否可以让DBMS自动为我做这件事，这是更好的选择<

此外，将这两个表合并为一个表本身也会产生负面影响，而不是在这里过多介绍...

解决方案

是否会复制BLOB所需的存储空间?

是的.索引视图将拥有自己的副本.

您可以从

看到此内容

CREATE TABLE dbo.T1
  (
     ID   INT IDENTITY PRIMARY KEY,
     Blob VARBINARY(MAX)
  );

DECLARE @vb VARBINARY(MAX) = CAST(REPLICATE(CAST('ABC' AS VARCHAR(MAX)), 1000000) AS VARBINARY(MAX));

INSERT INTO dbo.T1
VALUES      (@vb),
            (@vb),
            (@vb);

GO

CREATE VIEW dbo.V1
WITH SCHEMABINDING
AS
  SELECT ID,
         Blob
  FROM   dbo.T1

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON dbo.V1(ID)

SELECT o.NAME       AS object_name,
       p.index_id,
       au.type_desc AS allocation_type,
       au.data_pages,
       partition_number,
       au.total_pages,
       au.used_pages
FROM   sys.allocation_units AS au
       JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
       JOIN sys.objects AS o
         ON p.object_id = o.object_id
WHERE  o.object_id IN ( OBJECT_ID('dbo.V1'), OBJECT_ID('dbo.T1') ) 

返回哪个

+-------------+----------+-----------------+------------+------------------+-------------+------------+
| object_name | index_id | allocation_type | data_pages | partition_number | total_pages | used_pages |
+-------------+----------+-----------------+------------+------------------+-------------+------------+
| T1          |        1 | IN_ROW_DATA     |          1 |                1 |           2 |          2 |
| T1          |        1 | LOB_DATA        |          0 |                1 |        1129 |       1124 |
| V1          |        1 | IN_ROW_DATA     |          1 |                1 |           2 |          2 |
| V1          |        1 | LOB_DATA        |          0 |                1 |        1129 |       1124 |
+-------------+----------+-----------------+------------+------------------+-------------+------------+

The Question

Assuming I make an indexed view on a table containing a varbinary(max) column, will the binary content be physically copied into the indexed view's B-Tree, or will the original fields just be "referenced" somehow, without physically duplicating their content?

In other words, if I make an indexed view on a table containing BLOBs, will that duplicate the storage needed for BLOBs?

More Details

When using a full-text index on binary data, such as varbinary(max), we need an additional "filter type" column to specify how to extract text from that binary data so it can be indexed, something like this:

CREATE FULLTEXT INDEX ON <table or indexed view> (
    <data column> TYPE COLUMN <type column>
)
...

In my particular case, these fields are in different tables, and I'm trying to use indexed view to join them together, so they can be used in a full-text index.

Sure, I could copy the type field into the BLOB table and maintain it manually (keeping it synchronized with the original), but I'm wondering if I can make the DBMS do it for me automatically, which would be preferable unless there is a steep price to pay in terms of storage.

Also, merging these two tables into one would have negative consequences of its own, not to go into too much details here...

解决方案

will that duplicate the storage needed for BLOBs?

Yes. The indexed view will have its own copy.

You can see this from

CREATE TABLE dbo.T1
  (
     ID   INT IDENTITY PRIMARY KEY,
     Blob VARBINARY(MAX)
  );

DECLARE @vb VARBINARY(MAX) = CAST(REPLICATE(CAST('ABC' AS VARCHAR(MAX)), 1000000) AS VARBINARY(MAX));

INSERT INTO dbo.T1
VALUES      (@vb),
            (@vb),
            (@vb);

GO

CREATE VIEW dbo.V1
WITH SCHEMABINDING
AS
  SELECT ID,
         Blob
  FROM   dbo.T1

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON dbo.V1(ID)

SELECT o.NAME       AS object_name,
       p.index_id,
       au.type_desc AS allocation_type,
       au.data_pages,
       partition_number,
       au.total_pages,
       au.used_pages
FROM   sys.allocation_units AS au
       JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
       JOIN sys.objects AS o
         ON p.object_id = o.object_id
WHERE  o.object_id IN ( OBJECT_ID('dbo.V1'), OBJECT_ID('dbo.T1') ) 

Which returns

+-------------+----------+-----------------+------------+------------------+-------------+------------+
| object_name | index_id | allocation_type | data_pages | partition_number | total_pages | used_pages |
+-------------+----------+-----------------+------------+------------------+-------------+------------+
| T1          |        1 | IN_ROW_DATA     |          1 |                1 |           2 |          2 |
| T1          |        1 | LOB_DATA        |          0 |                1 |        1129 |       1124 |
| V1          |        1 | IN_ROW_DATA     |          1 |                1 |           2 |          2 |
| V1          |        1 | LOB_DATA        |          0 |                1 |        1129 |       1124 |
+-------------+----------+-----------------+------------+------------------+-------------+------------+

这篇关于BLOB如何存储在索引视图中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！