Índices en columnas de tipo de datos xml

  • Artículo

Las instancias XML se almacenan en las columnas de tipo xml como objetos binarios grandes (BLOB). Estas instancias XML pueden ser grandes, y la representación binaria almacenada de instancias de datos de tipo xml puede tener un tamaño de hasta 2 GB. Sin ningún índice, estos objetos binarios grandes se dividen en tiempo de ejecución para evaluar una consulta. Este proceso de división puede resultar lento. Por ejemplo, considere la siguiente consulta:

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

Para seleccionar instancias XML que cumplan la condición de la cláusula WHERE, el objeto binario grande (BLOB) XML de cada fila de la tabla Production.ProductModel se divide en tiempo de ejecución. A continuación, se evalúa la expresión (/PD:ProductDescription/@ProductModelID[.="19"]) en el método exist(). Esta división en tiempo de ejecución puede ser costosa, en función del tamaño y el número de instancias almacenadas en la columna.

Si las consultas de objetos binarios grandes (BLOB) XML son frecuentes en su entorno de aplicación, será útil indizar las columnas de tipo xml. No obstante, el mantenimiento del índice durante la modificación de datos lleva un costo asociado.

Los índices XML se dividen en las categorías siguientes:

  • Índice XML principal
  • Índice XML secundario

El primer índice de la columna de tipo xml debe ser el índice XML principal. Con el índice XML principal, se admiten los siguientes tipos de índices secundarios: PATH, VALUE y PROPERTY. Dependiendo del tipo de consulta, los índices secundarios pueden contribuir a mejorar el rendimiento.

Índice XML principal

El índice XML principal es una representación dividida y persistente de los objetos binarios grandes (BLOB) XML en la columna de tipo de datos xml. Para cada BLOB XML de la columna, el índice crea varias filas de datos. El número de filas del índice es prácticamente igual al número de nodos del BLOB XML.

Cada fila almacena la siguiente información acerca del nodo:

  • Nombre de etiqueta (p. ej., un nombre de elemento o atributo).
  • Valor del nodo.
  • Tipo de nodo (p. ej., un nodo de elemento, de atributo o de texto).
  • Información del pedido de documento, representado mediante un identificador de nodo interno.
  • Ruta de acceso desde cada nodo a la raíz del árbol XML. La consulta busca expresiones de ruta en esta columna.
  • Clave principal de la tabla base. La clave principal de la tabla base está duplicada en el índice XML principal para mantener la combinación con la tabla base, y el número máximo de columnas en la clave principal de la tabla base se limita a 15.

Esta información de nodo se utiliza para evaluar y crear resultados XML para una consulta específica. Con fines de optimización, la información de nombre de etiqueta y tipo de nodo se codifica en forma de valores enteros; la columna Patch utiliza la misma codificación. Asimismo, las rutas de acceso se almacenan en orden inverso para permitir rutas de acceso coincidentes cuando sólo se conoce el sufijo de la ruta de acceso. Por ejemplo:

  • //ContactRecord/PhoneNumber, donde sólo se conocen los dos últimos pasos.

—O bien—

  • /Book/*/Title, donde el carácter comodín (*) se especifica en mitad de la expresión.

El procesador de consultas usa el índice XML principal para las consultas que implican métodos de datos de tipo xml y devuelve valores escalares o los subárboles XML del propio índice principal. (Este índice almacena toda la información necesaria para volver a construir la instancia XML).

Por ejemplo, la siguiente consulta devuelve información de resumen almacenada en la columna de tipo xmlCatalogDescription en la tabla ProductModel. La consulta devuelve información perteneciente a <Summary> sólo para modelos de producto cuya descripción de catálogo también almacena información sobre <Features>.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

En relación con el índice XML principal, en lugar de dividir cada instancia de BLOB XML en la tabla base, las filas del índice que corresponden a cada BLOB XML se examinan secuencialmente para determinar la expresión especificada en el método exist(). Si la ruta de acceso se encuentra en la columna Path del índice, el elemento <Summary> y sus subárboles se recuperan a partir del índice XML principal y se convierten en un BLOB XML como resultado del método query().

Tenga en cuenta que el índice XML principal no se utiliza al recuperar una instancia XML completa. Por ejemplo, la consulta siguiente recupera de la tabla la instancia XML completa que describe las instrucciones de fabricación para un modelo determinado de producto.

USE AdventureWorks;

SELECT Instructions
FROM Production.ProductModel 
WHERE ProductModelID=7;

Índices XML secundarios

Para mejorar los resultados de la búsqueda, puede crear índices XML secundarios. Antes de crear índices secundarios, debe existir un índice XML principal. A continuación, se indican los tipos existentes:

  • Índice XML secundario PATH
  • Índice XML secundario VALUE
  • Índice XML secundario PROPERTY

Índice XML secundario PATH

Si sus consultas suelen especificar expresiones de ruta de acceso en columnas de tipo xml, un índice secundario PATH podría acelerar la búsqueda. Como se indicó anteriormente en este tema, el índice principal resulta útil cuando se realizan consultas que especifican el método exist() en la cláusula WHERE. Si agrega un índice secundario PATH, también puede mejorar los resultados de búsqueda en dichas consultas.

Aunque un índice XML principal evita que el BLOB XML se tenga que dividir en tiempo de ejecución, es posible que no proporcione un rendimiento óptimo con consultas basadas en expresiones de ruta de acceso. Dado que todas las filas del índice XML principal correspondientes a un BLOB XML se examinan secuencialmente para instancias XML de gran tamaño, la búsqueda secuencial puede ser lenta. En este caso, incorporar un índice secundario a los valores de ruta de acceso y de nodo del índice principal puede aumentar significativamente la velocidad de búsqueda del índice. En el índice secundario PATH, los valores de ruta de acceso y de nodo son columnas de clave que permiten exploraciones más eficaces en la búsqueda de rutas. El optimizador de consultas puede utilizar el índice PATH para expresiones como las siguientes:

  • /root/Location, que sólo especifica una ruta de acceso.

—O bien—

  • /root/Location/@LocationID[.="10"], donde se especifican los valores de ruta de acceso y de nodo.

La consulta siguiente muestra en qué lugar es útil el índice PATH:

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1

En la consulta, la expresión de ruta de acceso /PD:ProductDescription/@ProductModelID y el valor "19" del método exist() corresponden a los campos clave del índice PATH. Esto permite realizar búsquedas directas en el índice PATH y ofrece mejores resultados que la búsqueda secuencial de valores de ruta de acceso en el índice principal.

Índice XML secundario VALUE

Si las consultas se basan en valores, como, por ejemplo, /Root/ProductDescription/@*[. = "Mountain Bike"] o //ProductDescription[@Name = "Mountain Bike"], y la ruta de acceso no se especifica completamente o incluye un carácter comodín, se pueden obtener resultados más rápidos creando un índice XML secundario que se agregue a los valores de nodo en el índice XML principal.

Las columnas de clave del índice VALUE (valor de nodo y ruta de acceso) pertenecen al índice XML principal. Si la carga de trabajo requiere consultar valores de instancias XML sin conocer los nombres de elemento o atributo que contienen los valores, un índice VALUE puede resultar útil. Por ejemplo, la siguiente expresión se beneficiará del índice VALUE:

  • //author[LastName="someName"], donde se conoce el valor del elemento <LastName>, pero el elemento primario <author> puede estar en cualquier lugar.
  • /book[@* = "someValue"], donde la consulta busca el elemento <book> que contiene algún atributo con el valor "someValue".

La consulta siguiente devuelve ContactID de la tabla Contact. La cláusula WHERE especifica un filtro que busca valores en la columna de tipo xmlAdditionalContactInfo. Los Id. de contacto se devuelven sólo si el BLOB XML de información adicional de contacto correspondiente incluye un número de teléfono específico. Dado que el elemento <telephoneNumber> puede aparecer en cualquier lugar del XML, la expresión de ruta de acceso especifica el eje descendent-or-self.

WITH XMLNAMESPACES (
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT)

SELECT ContactID 
FROM   Person.Contact
WHERE  AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1

En esta situación, se conoce el valor de búsqueda para <number>, pero puede aparecer en cualquier lugar de la instancia XML como elemento secundario del elemento <telephoneNumber>. Este tipo de consulta puede beneficiarse de una búsqueda de índice basada en un valor específico.

Índice secundario PROPERTY

Las consultas que recuperan uno o varios valores de instancias XML individuales pueden beneficiarse del índice PROPERTY. Este escenario se produce al recuperar propiedades del objeto utilizando el método value() del tipo xml y cuando se conoce el valor de clave principal del objeto.

El índice PROPERTY se agrega a columnas (PK, Path y valor de nodo) del índice XML principal, donde PK es la clave principal de la tabla base.

Por ejemplo, para el modelo de producto 19, la consulta siguiente recupera los valores de los atributos ProductModelID y ProductModelName mediante el método value(). En lugar de utilizar el índice XML principal o los otros índices XML secundarios, el índice PROPERTY puede ofrecer una ejecución más rápida.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') as ModelID,
       CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') as ModelName        
FROM Production.ProductModel   
WHERE ProductModelID = 19

Salvo por las diferencias descritas más adelante en este tema, crear un índice XML en una columna de tipo xml es similar a crear un índice en una columna que no sea de tipo xml. Las siguientes instrucciones DDL Transact-SQL pueden usarse para crear y administrar índices XML:

Crear un índice XML principal

Para crear un índice XML principal, use la instrucción DDL Transact-SQL CREATE PRIMARY XML INDEX. Los índices XML no admiten todas las opciones que están disponibles para índices que no son XML.

Cuando cree un índice XML, tenga en cuenta lo siguiente:

  • Para crear un índice XML principal, la tabla que contiene la columna XML que se va a indizar, llamada tabla base, debe tener un índice agrupado en la clave principal. Esto garantiza que si la tabla base tiene particiones, se pueden crear particiones en el índice XML principal utilizando el mismo esquema y función de partición.
  • Si ya existe un índice XML, la clave principal agrupada de la tabla no puede modificarse. Antes de modificar la clave principal, deberá quitar todos los índices XML de la tabla.
  • Un índice XML principal puede crearse en una sola columna de tipo xml. No es posible crear ningún otro índice con una columna de tipo XML como columna de clave. No obstante, puede incluir la columna de tipo xml en un índice que no sea XML. Cada columna de tipo xml de una tabla puede tener su propio índice XML principal. No obstante, sólo se admite un índice XML principal por cada columna de tipo xml.
  • Los índices XML existen en el mismo espacio de nombres que los índices que no son XML. Por tanto, no puede tener un índice XML y otro que no lo sea en la misma tabla y con el mismo nombre.
  • Las opciones IGNORE_DUP_KEY y ONLINE siempre se establecen en OFF para los índices XML. Puede especificar estas opciones con el valor OFF.
  • La información de partición o grupo de archivos de la tabla de usuarios se aplica al índice XML. Los usuarios no pueden especificar dicha información por separado en un índice XML.
  • La opción de índice DROP_EXISTING permite quitar un índice XML principal y crear uno nuevo o efectuar la misma operación con un índice XML secundario. No obstante, esta opción no puede quitar un índice XML secundario para crear un índice XML principal ni viceversa.
  • Los nombres de índice XML principal tienen las mismas restricciones que los nombres de vista.

No se puede crear un índice XML en un columna de tipo xml de una vista, en una variable con el valor table con columnas de tipo xml ni en variables de tipo xml.

  • Para cambiar una columna de tipo xml de XML con tipo a XML sin tipo, o viceversa, con la opción ALTER TABLE ALTER COLUMN, la columna no debe incluir ningún índice XML. Si existe alguno, debe quitarse antes de intentar cambiar el tipo de columna.

  • Al crear un índice XML, la opción ARITHABORT debe configurarse en ON. Para consultar, insertar, eliminar o actualizar valores en la columna XML utilizando métodos de tipo de datos XML, debe establecerse la misma opción en la conexión. De lo contrario, los métodos de tipo de datos XML darán error.

    [!NOTA] La vistas de catálogo incluyen información acerca de los índices XML. No obstante, sp_helpindex no se admite. Los ejemplos que se ofrecen más adelante en este tema muestran cómo consultar las vistas de catálogo para encontrar información de índices XML.

Crear un índice XML secundario

Utilice la instrucción DDL Transact-SQL CREATE XML INDEX para crear índices XML secundarios y especificar el tipo de índice XML secundario que desea.

Cuando cree índices XML secundarios, tenga en cuenta lo siguiente:

  • Todas las opciones de indización que se aplican a un índice no agrupado, salvo IGNORE_DUP_KEY y ONLINE, se admiten para los índices XML secundarios. Las dos opciones deben establecerse siempre en OFF para los índices XML secundarios.
  • Los índices secundarios se dividen en particiones al igual que el índice XML principal.
  • DROP_EXISTING permite quitar un índice secundario de la tabla de usuario y crear otro en la misma tabla.

Puede consultar la vista de catálogo sys.xml_indexes para recuperar información de índices XML. Tenga en cuenta que la columna secondary_type_desc de la vista de catálogo sys.xml_indexes proporciona el tipo de índice secundario.

SELECT  * 
FROM    sys.xml_indexes

Los valores que se devuelven en la columna secondary_type_desc pueden ser NULL, PATH, VALUE o PROPERTY. El valor devuelto para el índice XML principal es NULL.

Modificar un índice XML

La instrucción DDL Transact-SQL ALTER INDEX puede usarse para modificar los índices XML y no XML existentes. No obstante, no todas las opciones ALTER INDEX están disponibles para índices XML. Las siguientes opciones no son válidas al modificar índices XML:

  • La opción de reconstrucción y configuración IGNORE_DUP_KEY no es válida para índices XML. La opción de reconstrucción ONLINE debe establecerse en OFF para los índices XML secundarios. La opción DROP_EXISTING no se admite en la instrucción ALTER INDEX. Al reconstruir el índice, las opciones de conexión deben establecerse del modo descrito en Configurar opciones (índice XML).
  • Las modificaciones de la restricción de clave principal en la tabla de usuario no se propagan automáticamente a los índices XML. El usuario debe quitar los índices XML y volver a crearlos después.
  • Cuando se especifica ALTER INDEX ALL, se aplica tanto a lo índices XML como a los que no lo son. Se pueden especificar opciones de indización que no sean válidas para ambos tipos de índices. En este caso, la instrucción producirá un error.

Quitar un índice XML

La instrucción Transact-SQL DROP INDEX se puede usar para quitar índices XML y no XML principales y secundarios existentes. No obstante, ninguna opción de DROP INDEX se aplica a los índices XML. Si se quita el índice XML principal, se eliminarán todos los índices secundarios existentes.

La sintaxis DROP con TableName**.**IndexName está desapareciendo y no es compatible con los índices XML.

Ejemplos

Los ejemplos siguientes muestran cómo crear, modificar y quitar índices XML.

A. Crear y quitar un índice XML principal

En el ejemplo siguiente se muestra cómo crear un índice XML en una columna de tipo xml.

DROP TABLE T
GO
CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create Primary XML index 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Verify the index creation. 
-- Note index type is 3 for xml indexes.
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol' 
-- Drop the index.
DROP INDEX PIdx_T_XmlCol ON T

Al quitar una tabla, se quitan automáticamente todos los índices XML que contiene. No obstante, una columna XML no puede quitarse desde una tabla si existe un índice XML en la columna.

En el ejemplo siguiente se muestra cómo crear un índice XML en una columna de tipo xml. Para obtener más información, vea XML con tipo y sin tipo.

CREATE TABLE TestTable(
 Col1 int primary key, 
 Col2 xml (Production.ProductDescriptionSchemaCollection)) 
GO

En este momento, puede crear un índice XML principal en Co12.

CREATE PRIMARY XML INDEX PIdx_TestTable_Col2 
ON TestTable(Col2)
GO

B. Crear índices XML secundarios

El ejemplo siguiente muestra cómo crear índices XML secundarios. En el ejemplo también se muestra información acerca de los índices XML que ha creado.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_T_XmlCol_PATH ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_T_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR VALUE
GO
CREATE XML INDEX PIdx_T_XmlCol_PROPERTY ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PROPERTY
GO

Puede consultar sys.xml_indexes para recuperar información de índices XML. La columna secondary_type_desc proporciona el tipo de índice secundario.

SELECT  * 
FROM    sys.xml_indexes

También puede consultar la vista de catálogo para obtener información de índice.

SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')

Puede agregar datos de ejemplo y, a continuación, revisar la información de índices XML.

INSERT INTO T VALUES (1,
'<doc id="123">
<sections>
<section num="2">
<heading>Background</heading>
</section>
<section num="3">
<heading>Sort</heading>
</section>
<section num="4">
<heading>Search</heading>
</section>
</sections>
</doc>')
GO
-- Check XML index information.
SELECT *
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED')
GO
-- Space usage of primary XML index
DECLARE @index_id int
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i 
WHERE   i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
--- Space usage of secondary XML index (for example PATH secondary index)  PIdx_T_XmlCol_PATH
DECLARE @index_id int
SELECT  @index_id = i.index_id 
FROM    sys.xml_indexes i 
WHERE  i.name = 'PIdx_T_XmlCol_PATH' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
 
-- Space usage of all secondary XML indexes for a particular table
SELECT i.name, object_name(i.object_id), stats.* 
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats
JOIN sys.xml_indexes i ON (stats.object_id = i.object_id and stats.index_id = i.index_id)
WHERE secondary_type is not null
-- Drop secondary indexes.
DROP INDEX PIdx_T_XmlCol_PATH ON T
GO
DROP INDEX PIdx_T_XmlCol_VALUE ON T
GO
DROP INDEX PIdx_T_XmlCol_PROPERTY ON T
GO
-- Drop primary index.
DROP INDEX PIdx_T_XmlCol ON T
-- Drop table T.
DROP TABLE T
Go

C. Modificar un índice XML

En el ejemplo siguiente se muestra cómo crear un índice XML y, a continuación, modificarlo estableciendo la opción ALLOW_ROW_LOCKS en OFF. Cuando ALLOW_ROW_LOCKS se ha establecido en OFF, las filas no se bloquean y el acceso a los índices especificados se obtiene utilizando los bloqueos de página y de tabla.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary XML index. 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'

-- Modify and set an index option.
ALTER INDEX PIdx_T_XmlCol on T 
SET (ALLOW_ROW_LOCKS = OFF)

D. Deshabilitar y habilitar un índice XML

Un índice XML está habilitado de forma predeterminada. Si un índice XML se deshabilita, las consultas que se realicen en la columna XML no utilizarán el índice XML. Para habilitar un índice XML, use ALTER INDEX con la opción REBUILD.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON T(XmlCol)
GO
ALTER INDEX PIdx_T_XmlCol on T DISABLE
Go
-- Verify index is disabled.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'
-- Rebuild the index.
ALTER INDEX PIdx_T_XmlCol on T REBUILD
Go

E. Crear un índice XML utilizando la opción de índice DROP_EXISTING

El ejemplo siguiente muestra cómo crear un índice XML en una columna (XmlColx). A continuación, se creará otro índice XML con el mismo nombre en una columna diferente (XmlColy). Dado que se ha especificado la opción DROP_EXISTING, el índice XML existente en (XmlColx) se quita y se crea un índice XML en (XmlColy).

DROP TABLE T
GO
CREATE TABLE T(Col1 int primary key, XmlColx xml, XmlColy xml)
GO
-- Create XML index on XmlColx.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColx)
GO
-- Create same name XML index on XmlColy.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColy) 
WITH (DROP_EXISTING = ON)
-- Verify the index is created on XmlColy.d.
SELECT sc.name 
FROM   sys.xml_indexes si inner join sys.index_columns sic 
ON     sic.object_id=si.object_id and sic.index_id=si.index_id
INNER  join sys.columns sc on sc.object_id=sic.object_id 
AND    sc.column_id=sic.column_id
WHERE  si.name='PIdx_T_XmlCol' 
AND    si.object_id=object_id('T')

Esta consulta devuelve el nombre de la columna en la que el índice XML especificado se ha creado.

Vea también

Conceptos

Tipo de datos xml
Aplicaciones XML de ejemplo

Otros recursos

sys.dm_db_index_physical_stats

Ayuda e información

Obtener ayuda sobre SQL Server 2005