Sentencia CREATE INDEX

Invocación

Esta sentencia se puede incorporar a un programa de aplicación o emitir mediante el uso de sentencias de SQL dinámico. Es una sentencia ejecutable que sólo se puede preparar de forma dinámica si el comportamiento de ejecución de DYNAMICRULES está en vigor para el paquete (SQLSTATE 42509).

Autorización

No se necesita ningún privilegio explícito para crear un índice en una tabla temporal declarada.

Sintaxis

Notes:

• ¹ In a federated system, table-name must identify a table in the federated database. It cannot identify a data source table.
• ² If nickname is specified, the CREATE INDEX statement creates an index specification. In this case, INCLUDE, xml-index-specification, CLUSTER, EXTEND USING, PCTFREE, MINPCTUSED, DISALLOW REVERSE SCANS, ALLOW REVERSE SCANS, PAGE SPLIT, or COLLECT STATISTICS cannot be specified.
• ³ The BUSINESS_TIME WITHOUT OVERLAPS clause can be specified only if UNIQUE is specified.
• ⁴ The IN tablespace-name clause can be specified only for a nonpartitioned index on a partitioned table.
• ⁵ The INCLUDE clause can be specified only if UNIQUE is specified.
• ⁶ If xml-index-specification is specified, column-name DESC, INCLUDE, or CLUSTER cannot be specified.

xml-index-specification

Notes:

• ¹ The alternative syntax GENERATE KEYS USING XMLPATTERN can be used.

xmlpattern-clause

namespace-declaration

pattern-expression

forward-axis

xmlname-test

xml-wildcard

xmlkind-test

function-step

xmltype-clause

data-type

sql-data-type

Notes:

• ¹ If you specify a function name, such as fn:upper-case, at the end of the XML pattern, the supported index data types might be a subset of the index data types shown here. You can check for valid index data types in the description for xmlpattern-clause.

Descripción

UNIQUE

Si se especifica ON nombre-tabla, UNIQUE evita que la tabla contenga dos o más filas con el mismo valor de la clave de índice. La unicidad de valores se aplica al final de la sentencia de SQL que actualiza filas o inserta nuevas filas.

La exclusividad también se comprueba durante la ejecución de la sentencia CREATE INDEX. Si la tabla ya contiene filas con valores de clave duplicados, no se crea el índice.

Si el índice se encuentra en una columna XML (se trata de un índice de datos XML), la unicidad se aplica a los valores para los que se ha especificado expresión-patrón para todas las filas de la tabla. La unicidad se aplica en cada valor después de convertir el valor al tipo-datos-sql especificado. Puesto que la conversión al tipo-datos-sql especificado puede dar lugar a una pérdida de precisión o de rango, o a que distintos valores se generen aleatoriamente en el mismo valor clave, varios valores que parecen ser exclusivos en el documento XML pueden dar lugar a errores de claves duplicadas. La unicidad de series de caracteres depende de la semántica de XQuery donde los blancos de cola son significativos. Por lo tanto, los valores que serían duplicados en SQL pero que difieren en los espacios en blanco finales se consideran valores exclusivos en un índice sobre datos XML.

Cuando se utiliza UNIQUE, los valores nulos se tratan como cualquier otro valor. Por ejemplo, si la clave es una sola columna que puede contener valores nulos, esa columna no puede contener más de un valor nulo.

Si se especifica la opción UNIQUE, y la tabla tiene una clave de distribución, las columnas de la clave de índice deben ser un superconjunto de la clave de distribución. Es decir, las columnas especificadas para una clave de índice exclusiva debe incluir todas las columnas de la clave de distribución (SQLSTATE 42997).

Las claves primarias o exclusivas no pueden ser subconjuntos de dimensiones (SQLSTATE 429BE).

Si se especifica ON apodo, sólo se debe especificar UNIQUE si los datos para la clave de índice contienen valores exclusivos para cada fila de la tabla de fuente de datos. No se comprobará la exclusividad.

Para un índice sobre datos XML, UNIQUE solo se puede incluir si el paso de contexto de la expresión-patrón especifica una única vía de acceso completa y no contiene un eje descendiente o descendiente-o-propio, "//", un comodín-xml, nodo ()o instrucción-proceso () (SQLSTATE 429BS).

En un entorno de base de datos particionada, las siguientes normas se aplican a la tabla con una o más columnas XML:

• Una tabla redistribuida no puede tener un índice exclusivo sobre datos XML.
• Sólo se da soporte a un índice exclusivo sobre datos XML en una tabla sin clave de distribución y que no es una base de datos de varias particiones.
• Si una tabla tiene un índice exclusivo sobre datos XML, la tabla no se puede modificar para añadir una clave de distribución.

INDEX nombre-índice

Nombra el índice o la especificación de índice. El nombre, (incluido el calificador implícito o explícito) no debe designar un índice o una especificación de índice que se describa en el catálogo o un índice existente de una tabla temporal declarada (SQLSTATE 42704). El calificador no debe ser SYSIBM, SYSCAT, SYSFUN ni SYSSTAT (SQLSTATE 42939).

El calificador implícito o explícito de los índices de las tablas temporales declaradas debe ser SESSION (SQLSTATE 428EK).

ON nombre-tabla o apodo

El nombre-tabla identifica una tabla en la que va a crearse un índice. La tabla debe ser una tabla base (no una vista), una tabla temporal creada, una tabla temporal declarada, una tabla de consulta materializada que exista en el servidor actual o una tabla temporal declarada. El nombre de una tabla temporal declarada debe calificarse con SESSION.

El nombre-tabla no debe identificar una tabla de catálogo (SQLSTATE 42832).

Si se especifica UNIQUE y nombre-tabla es una tabla con tipo, no debe ser una subtabla (SQLSTATE 429B3).

apodo es el apodo en el que una especificación de índice se creará. El apodo hace referencia a la tabla de fuente de datos cuyo índice se describe mediante la especificación de índice o la vista de fuente de datos que se basa en esa tabla. El apodo debe aparecer en la lista del catálogo.

Si la clave de índice contiene al menos una expresión-clave, el nombre-tabla no puede ser ninguno de los siguientes objetos:

• Una tabla de consulta materializada (MQT) (SQLSTATE 429BX)
• Una tabla base (SQLSTATE 429BX)
• Una tabla con tipo (SQLSTATE 429BX)
• Una tabla temporal declarada o creada por el usuario (SQLSTATE 42995)
• Una tabla organizada por columnas (SQLSTATE 42858)
• Una tabla que es destino del supervisor de sucesos (SQLSTATE 429BX)
• Un apodo (SQLSTATE 42601)

nombre-columna

Para un índice, nombre-columna identifica una columna que debe formar parte de la clave de índice. Para una especificación de índice, nombre-columna es el nombre que el servidor federado utiliza para hacer referencia a una columna de una tabla de fuente de datos.

El número de columnas más dos veces el número de períodos identificados no puede superar 64 (SQLSTATE 54008). Si nombre-tabla es una tabla con tipo, el número de columnas no puede superar 63 (SQLSTATE 54008). Si nombre-tabla es una subtabla, como mínimo deberá especificarse un nombre-columna en la subtabla; es decir, no deberá heredarse de una supertabla (SQLSTATE 428DS). No puede repetirse ningún nombre-columna (SQLSTATE 42711). El número máximo de columnas en un índice con orden aleatorio se reduce por uno para cada columna que se especifica con orden aleatorio

La suma de las longitudes de las columnas especificadas no debe ser superior al límite de longitud de claves de índice para el tamaño de página. Para los límites de longitud de clave, consulte Límites de SQL. Si nombre-tabla es una tabla con tipo, la longitud de la clave de índice debe ser todavía 4 bytes menos. Si el índice tiene orden aleatorio, la longitud de la clave del índice se reduce aún más por 2 bytes.

Tenga en cuenta que la actividad general del sistema puede reducir esta longitud, que varía en función del tipo de datos de la columna y en función de si puede contener nulos. Para obtener más información sobre cómo la actividad general afecta a este límite, consulte Número total de bytes en CREATE TABLE.

No e pueden utilizar columnas LOB o columnas de tipo diferenciado basadas en LOB como parte de un índice, incluso cuando el atributo de longitud de la columna es suficientemente pequeño para caber en el límite de longitud de la clave de índice para el tamaño de página (SQLSTATE 54008). Las columnas de tipo estructurado sólo se pueden especificar si también se especifica la cláusula EXTEND USING (SQLSTATE 42962). Si se especifica la cláusula EXTEND USING, sólo se puede especificar una columna y el tipo de la columna debe ser un tipo estructurado o un tipo diferenciado no basado en LOB (SQLSTATE 42997).

Si un índice sólo tiene una columna, y esa columna tiene el tipo de datos XML, y también se especifica la cláusula GENERATE KEY USING XMLPATTERN, el índice es un índice sobre datos XML. Una columna con datos de tipo XML sólo puede especificarse si también se especifica la cláusula GENERATE KEY USING XMLPATTERN (SQLSTATE 42962). Si se especifica la cláusula GENERATE KEY USING XMLPATTERN, sólo se puede especificar una columna y el tipo de la columna debe ser XML.

clave-expresión

Especifica una expresión que debe evaluar a un valor escalar con las siguientes restricciones:

• La expresión debe devolver un valor escalar que se puede indexar (sin LOB, XML, LONG VARCHAR o LONG VARGRAPHIC) (SQLSTATE 429BX)
• Los siguientes tipos de datos no están soportados como entrada a la clave del índice basado en expresiones:
  • LONG VARCHAR y LONG VARGRAPHIC (tipos de datos en desuso)
  • XML
  • Tipos diferenciados definidos por el usuario en cualquiera de los tipos mencionados anteriormente
  • Tipos diferenciados de tipado débil y definidos por el usuario que incluyen una restricción del tipo de datos
  • Tipos estructurados definidos por el usuario y tipos de referencia
  • Tipos de matriz, cursor y filas
• La expresión debe incluir al menos una referencia de columna (SQLSTATE 429BX)
• La expresión no puede incluir ninguno de los siguientes (SQLSTATE 429BX):
  • Subconsultas
  • Funciones agregadas
  • Sin funciones deterministas
  • Funciones con acciones externas
  • Funciones definidas por el usuario
  • Funciones de búsqueda de texto, como SCORE, CONTAINS
  • Funciones escalares de particionamiento, como HASHEDVALUE
  • Funciones escalares de tipos de datos dinámicos, como TYPE_ID, TYPE_NAME, TYPE_SCHEMA
  • Variables del lenguaje principal
  • Marcadores de parámetro
  • Referencias de secuencia
  • Registros especiales y funciones incorporadas que dependen del valor de un registro especial
  • Variables globales y funciones incorporadas que dependen del valor de una variable global
  • Un predicado TYPE
  • Funciones de expresión regular o predicado REGEXP_LIKE
  • Un predicado LIKE
  • Funciones escalares de serie INSTR, INSTRB, LOCATE, LOCATE_IN_STRING, POSITION o POSSTR
  • Especificaciones OLAP
  • Operaciones de eliminación de referencia o funciones DEREF donde el argumento de referencia con ámbito no es el correspondiente a la columna de identificador de objeto (OID)
  • Especificaciones CAST con una cláusula SCOPE
  • Expresiones de tabla anidada que toleran errores

Si una clave de índice incluye al menos una clave-expresión, a la clave del índice se hace referencia como una clave de índice basada en expresión.

ASC

Especifica que las entradas de índice se deben mantener en el orden ascendente de los valores de columna; este es el valor predeterminado. ASC no se puede especificar para índices que están definidos con EXTEND USING (SQLSTATE 42601).

DESC

Especifica que las entradas de índice se deben mantener en el orden descendente de los valores de columna. No se puede especificar DESC para índices definidos con EXTEND USING, o si el índice es un índice sobre datos XML (SQLSTATE 42601).

RANDOM

Especifica que las entradas de índice se deben mantener en el orden aleatorio de los valores de columna. No se puede especificar RANDOM en los casos siguientes:

• Con la cláusula EXTENDED USING (SQLSTATE 42613).
• Con la cláusula SPECIFICATION ONLY (SQLSTATE 42613).
• Para un índice que se crea en una tabla temporal declarada o creada globalmente (DGTT o CGTT) (SQLSTATE 42995).
• Para un índice que se crea en una tabla organizada por columnas (SQLSTATE 42858).
• Si se especifica la opción CLUSTER (SQLSTATE 42613).
• En una columna indexada que es de tipo CHAR o VARCHAR con ordenaciones ICU, excepto cuando las columnas se declaran como FOR BIT DATA (SQLSTATE 42997).
• En una columna indexada de tipo GRAPHIC o VARGRAPHIC con ordenaciones ICU (SQLSTATE 42997).
• En una columna indexada de tipo XML (SQLSTATE 42613).
• En un índice que incluye una expresión-clave (SQLSTATE 42997).

BUSINESS_TIME WITHOUT OVERLAPS

BUSINESS_TIME WITHOUT OVERLAPS sólo se pueden especificar para un índice definido como UNIQUE (SQLSTATE 428HW) a fin de indicar que, para el resto de las claves especificadas, los valores son exclusivos con respecto de cualquier período de tiempo. BUSINESS_TIME WITHOUT OVERLAPS sólo se puede especificar como último elemento de la lista. Cuando se especifica BUSINESS_TIME WITHOUT OVERLAPS, la columna final y la columna inicial del período BUSINESS_TIME se añaden automáticamente a la clave de índice en orden ascendente e impone que no haya solapamientos en el tiempo. Cuando se especifica BUSINESS_TIME WITHOUT OVERLAPS, las columnas del período BUSINESS_TIME no deben especificarse como columnas de claves, como columnas en la clave de particionamiento ni como columnas en la clave de distribución (SQLSTATE 428HW).

PARTITIONED

Indica que debe crearse un índice particionado. El nombre-tabla debe identificar una tabla definida con las particiones de datos (SQLSTATE 42601).

Si la tabla está particionada y no se especifica ni PARTITIONED ni NOT PARTITIONED, el índice se crea como particionado (con algunas excepciones). Se crea un índice no particionado en lugar de un índice particionado si se da alguna de estas situaciones:

• Se especifique UNIQUE y la clave de índice no incluya todas las columnas de clave de particionamiento de tabla.
• Se crea un índice espacial.

Un índice particionado con una definición que duplique la definición de un índice no particionado no se considera un índice duplicado. Para obtener más detalles, consulte la sección Reglas en este tema.

La palabra clave PARTITIONED no puede especificarse para los índices siguientes:

• Un índice en una tabla no particionada (SQLSTATE 42601)
• Un índice único donde la clave de índice no incluye de forma específica todas las columnas de clave de particionamiento de la tabla (SQLSTATE 42990)
• Un índice espacial (SQLSTATE 42997)

Un índice particionado no puede crearse en una tabla particionada que tenga tablas dependientes desenlazadas, por ejemplo, MQT (SQLSTATE 55019).

La colocación del espacio de tablas para una partición de índice del índice particionado viene determinada por las normas siguientes:

• Si la tabla que se desea indexar se ha creado mediante la cláusula INDEX IN opciones-espacio-tablas-partición de la sentencia CREATE TABLE, la partición de índice se crea en el espacio de tablas especificado en esa cláusula INDEX IN.
• Si la sentencia CREATE TABLE para la tabla que se desea indexar no ha especificado la cláusula INDEX IN opciones-espacio-tablas-partición, el índice particionado de la partición de índice se crea en el mismo espacio de tablas que la partición de datos correspondiente que indexa.

La cláusula IN de la sentencia CREATE INDEX no está soportada para los índices particionados (SQLSTATE 42601). La cláusula INDEX IN cláusulas-espacio-tablas de la sentencia CREATE TABLE se pasa por alto para los índices particionados. Si se especifica BUSINESS_TIME WITHOUT OVERLAPS para la clave de índice, las columnas de clave de particionamiento no deben incluir la columna inicial o final del período BUSINESS_TIME (SQLSTATE 428HW).

NOT PARTITIONED

Indica que sólo debe crearse un índice no particionado que se expanda por todas las particiones de datos definidas para la tabla. El nombre-tabla debe identificar una tabla definida con las particiones de datos (SQLSTATE 42601).

Un índice no particionado con una definición que duplique la definición de un índice particionado no se considera un índice duplicado. Para obtener más detalles, consulte la sección Reglas en este tema.

La colocación del espacio de tablas para un índice no particionado viene determinada por las normas siguientes:

• Si se especifica la cláusula IN de la sentencia CREATE INDEX, el índice no particionado se coloca en el espacio de tablas especificado en esa cláusula IN.
• Si no se especifica la cláusula IN de la sentencia CREATE INDEX, las normas siguientes determinan la colocación del espacio de tablas del índice no particionado:
  • Si la tabla que se desea indexar se ha creado mediante la cláusula INDEX IN cláusulas-espacio-tablas de la sentencia CREATE TABLE, el índice no particionado se coloca en el espacio de tablas especificado en esa cláusula INDEX IN.
  • Si la tabla que se desea indexar se ha creado sin utilizar la cláusula INDEX IN cláusulas-espacio-tablas de la sentencia CREATE TABLE, el índice no particionado se crea en el espacio de tablas de la primera partición de datos visible o enlazada de la tabla. La primera partición de datos visible o enlazada de la tabla es la primera partición de la lista de particiones de datos almacenadas en la base de las especificaciones de rango. Además, el ID de autorización de la sentencia no será necesario para poseer el privilegio USE en el espacio de tablas por omisión.

IN nombre-espacio-tablas

Especifica el espacio de tablas en el que se crea el índice no particionado en una tabla particionada. Esta cláusula no puede especificarse para un índice particionado o un índice en una tabla no particionada (SQLSTATE 42601). Al especificar un espacio de tablas expresamente para el índice, se altera temporalmente la especificación realizada mediante una cláusula INDEX IN al crear la tabla.

El espacio de tablas especificado por nombre-espacio-tablas debe estar en el mismo grupo de particiones de base de datos que los espacios de tablas de datos para la tabla y gestionar el espacio del mismo modo que los demás espacios de tablas de la tabla particionada (SQLSTATE 42838); debe ser un espacio de tablas en el que el ID de autorización de la sentencia posea el privilegio USE.

Si no se especifica la cláusula IN, el índice se crea en el espacio de tablas que se ha especificado con la cláusula INDEX IN en la sentencia CREATE TABLE. Si no se ha especificado ninguna cláusula INDEX IN, se utiliza el espacio de tablas de la primera partición de datos visibles o adjunta de la tabla. Ésta es la primera partición de la lista de particiones de datos que se clasifica según las especificaciones de rango. Si no se especifica la cláusula IN, el ID de autorización de la sentencia no será necesario para poseer el privilegio USE en el espacio de tablas por omisión.

SPECIFICATION ONLY

Indica que esta sentencia será utilizada para crear una especificación de índice que se suscribe a la tabla de fuente de datos a la que se hace referencia mediante apodo. Se debe especificar SPECIFICATION ONLY si se especifica apodo (SQLSTATE 42601). No se puede especificar si se especifica nombre-tabla (SQLSTATE 42601).

Si la especificación de índice se aplica a un índice que es exclusivo, el gestor de bases de datos no verifica si los valores de columna de la tabla remota son exclusivos. Si los valores de la columna remota no son exclusivos, es posible que las consultas realizadas sobre el apodo que incluyan la columna del índice devuelvan datos incorrectos o errores.

Esta cláusula no se puede utilizar cuando se crea un índice en una tabla temporal creada o una tabla temporal declarada (SQLSTATE 42995).

INCLUDE

Esta palabra clave entra una cláusula que especifica columnas adicionales a añadir al conjunto de columnas de la clave de índice. Las columnas incluidas con esta cláusula no se utilizan para imponer la exclusividad. Estas columnas incluidas pueden mejorar el rendimiento de algunas consultas mediante el acceso de sólo índice. Las columnas deben ser diferentes de las columnas utilizadas para imponer la exclusividad (SQLSTATE 42711). Debe especificarse UNIQUE si se especifica INCLUDE (SQLSTATE 42613). Los límites para el número de columnas y la suma de los atributos de longitud se aplican a todas las columnas del índice y la clave de unicidad.

Esta cláusula no se puede utilizar con tablas temporales creadas o tablas temporales declaradas (SQLSTATE 42995).

Esta cláusula no se puede utilizar con tablas organizadas en columnas (SQLSTATE 42858).

nombre-columna

Identifica una columna que se incluye en el índice, pero que no forma parte de la clave de índice de unicidad. Se aplican las mismas normas que se han definido para las columnas de la clave de índice de unicidad. Se pueden especificar las palabras clave ASC, DESC o RANDOM a continuación de nombre-columna, pero no tienen ningún efecto en el orden.

clave-expresión

Especifica una expresión que se incluye en el índice pero que no es parte de la clave de índice de unicidad. Se aplican las mismas normas que se han definido para las expresiones de la clave de índice de unicidad. Se pueden especificar las palabras clave ASC, DESC o RANDOM a continuación de la expresión-clave, pero no tienen ningún efecto en el orden.

INCLUDE no se puede especificar para índices definidos con EXTEND USING, si se ha especificado apodo o si el índice se ha definido en una columna XML (SQLSTATE 42601).

especificación-índice-xml

Especifica cómo se generan las claves de índice a partir de documentos XML que están almacenados en una columna XML. No se puede especificar especificación-índice-xml si hay más de una columna de índice o si la columna no tiene el tipo de datos XML.

Esta cláusula sólo se aplica a columnas XML (SQLSTATE 429BS).

GENERATE KEY USING XMLPATTERN cláusula-patrón-xml

Especifica las partes de un documento XML que deben indexarse. Los valores de patrón XML son los valores indexados generados por la cláusula-patrón-xml. En el índice no se da soporte a los nodos de tipo de datos de lista. Si un nodo es calificado por la cláusula-patrón-xml y existe un esquema XML que especifica que el nodo es de tipo de datos de lista, el nodo de tipo de datos de lista no se puede indexar (SQLSTATE 23526 para sentencias CREATE INDEX o SQLSTATE 23525 para sentencias INSERT y UPDATE).

cláusula-patrón-xml

Contiene una expresión de patrón que identifica los nodos que deben indexarse. Consta de una declaración-espacio-nombres opcional y una necesaria expresión-patrón.

declaración-espacio-nombres

Si la expresión de patrón contiene nombres calificados, debe especificarse una declaración-espacio-nombres para definir prefijos de espacios de nombres. Puede definirse un espacio de nombres por omisión para nombres sin calificar.

DECLARE NAMESPACE prefijo-espacio-nombres=uri-espacio-nombres

Correlaciona el prefijo-espacio-nombres, que es un NCName, con el uri-espacio-nombres, que es un literal de cadena. La declaración-espacio-nombres puede contener varias correlaciones prefijo-espacio-nombres-a-uri-espacio-nombres. El prefijo-espacio-nombres debe ser exclusivo dentro de la lista de declaración-espacio-nombres (SQLSTATE 10503).

DECLARE DEFAULT ELEMENT NAMESPACE uri-espacio-nombres

Declara el URI de espacio de nombres por omisión para los tipos o los nombres de elemento sin calificar. Si no se ha declarado ningún espacio de nombres por omisión, los tipos y los nombres de elemento sin calificar no se encuentran en ningún espacio de nombres. Sólo se puede declarar un espacio de nombres por omisión (SQLSTATE 10502).

expresión-patrón

Especifica los nodos de un documento XML que están indexados. La expresión-patrón puede contener caracteres de coincidencia de patrón (*). Es similar a una expresión de vía de acceso en XQuery, pero representa un subconjunto del lenguaje XQuery que soporta esta base de datos.

/ (barra inclinada)

Separa los pasos de la expresión de vía de acceso.

// (dos barras inclinadas)

Es la sintaxis abreviada para /descendant-or-self::node ()/. No puede utilizar // (barra inclinada doble) si también especifica UNIQUE.

eje-avance

child::

Especifica los hijos del nodo de contexto. Es el valor predeterminado, si no se especifica ningún otro eje de avance.

@

Especifica los atributos del nodo de contexto. Ésta es la sintaxis abreviada del atributo::.

attribute::

Especifica los atributos del nodo de contexto.

descendant::

Especifica los descendientes del nodo de contexto. No se puede utilizar descendant:: si también se especifica UNIQUE.

self::

Especifica el nodo de contexto propiamente dicho.

descendant-or-self::

Especifica el nodo de contexto y los descendientes del nodo de contexto. No se puede utilizar descendant-or-self:: si también se especifica UNIQUE.

prueba-nombre-xml

Especifica el nombre del nodo para el paso en la vía utilizando un nombre XML calificado (nombre-xml-c) o un comodín (comodín-xml).

nombre-nc-xml

Nombre XML según define XML 1.0. No puede incluir el carácter de dos puntos.

nombre-xml-c

Especifica un nombre XML calificado (conocido por QName) que puede tener dos formas posibles:

• prefijo-ns-xml:nombre-nc-xml, donde prefijo-ns-xml es un nombre-nc-xml que identifica un espacio de nombres del ámbito.
• nombre-nc-xml, que indica que el espacio de nombres por omisión debe aplicarse como prefijo-ns-xml implícito.

comodín-xml

Especifica un nombre-xml-c como un comodín que puede tener tres formas posibles:

• * (un único carácter de asterisco) indica cualquier nombre-xml-c
• prefijo-ns-xml:* indica cualquier nombre-nc-xml dentro del espacio de nombres especificado
• *:nombre-nc-xml indica un nombre XML específico dentro de cualquier espacio de nombres del ámbito

No se puede utilizar comodín-xml en el paso de contexto de una expresión de patrón si también se especifica UNIQUE.

prueba-tipo-xml

Utilice estas opciones para especificar con qué tipos de nodo coincide el patrón. Las opciones disponibles son:

nodo ()

Coincide con cualquier nodo. No se puede utilizar node() si también se especifica UNIQUE.

text ()

Coincide con cualquier nodo de texto.

comment()

Coincide con cualquier nodo de comentario.

processing-instruction()

Coincide con cualquier nodo de instrucción de proceso. No se puede utilizar processing-instruction() si también se especifica UNIQUE.

paso-función

Utilice estas llamadas de función para especificar índices con propiedades especiales, como que no se tiene en cuenta las mayúsculas y minúsculas. Sólo se permite un paso de función por cada cláusula XMLPATTERN. Los pasos de función sólo se pueden aplicar en elementos o atributos. No se puede colocar ninguna opción prueba-tipo-xml justo antes del paso de función. La función no se puede utilizar en medio del XMLPATTERN y debe aparecer sólo en el paso final. Actualmente, sólo se da soporte a las funciones fn:upper-case y fn:exists.

Tenga en cuenta que, en lugar de especificar el prefijo fn: para el nombre de función, puede especificar otro espacio de nombres válido o puede completamente fn:.

fn:upper-case

Hace que los valores de índice se almacenen en formato de mayúsculas. El primer parámetro de fn:upper-case es obligatorio y debe ser una expresión de elemento de contexto ('. '); el segundo parámetro es opcional y es el entorno local. Si fn:upper-case aparece en el patrón, VARCHAR y VARCHAR HASHED son los únicos tipos de índice soportados.

fn:exists

Comprueba si existe un elemento o un elemento de atributo en el documento XML. Si el elemento existe, este predicado devuelve true. El parámetro de fn:exists es obligatorio y debe ser un elemento o un atributo. Si esta función se utiliza en la vía de acceso de índice, el tipo de índice debe definirse como VARCHAR(1).

cláusula-tipo-xml

AS tipo-datos

Especifica el tipo de datos a los que se convierten los valores indexados antes de almacenarlos. Los valores se convierten al tipo de datos de índice XML que corresponde al tipo de datos de índice SQL especificado.

Tabla 1. Tipos de datos de índice correspondientes

Tipo de datos de índice XML	Tipo de datos de índice SQL
xs:string	VARCHAR(entero), VARCHAR HASHED
xs:double	DOUBLE
xs:int	INTEGER
xs:decimal	DECIMAL
xs:date	FECHA
xs:dateTime	TIMESTAMP

Para VARCHAR(entero) y VARCHAR HASHED, el valor se convierte a un valor xs:string utilizando la función XQuery fn:string. El atributo de longitud de VARCHAR(entero) se aplica como restricción al valor xs:string resultante. Un tipo de datos de índice SQL de VARCHAR HASHED aplica un algoritmo hash al valor xs:string resultante para generar un código hash que se inserta en el índice.

Para índices que utilizan los tipos de datos DOUBLE, DATE, INTEGER, DECIMAL y TIMESTAMP, el valor se convierte al tipo de datos de índice XML mediante la expresión de conversión de XQuery.

Si el índice es exclusivo, la unicidad del valor se aplica después de convertir el valor al tipo indexado.

tipo-datos

Se da soporte al tipo de datos siguiente:

tipo-datos-sql

Los tipos de datos SQL son:

VARCHAR(entero[OCTETS])

Si se especifica este formato de VARCHAR, se utiliza entero como restricción. Si los nodos de documento que deben indexarse tienen valores superiores al entero, los documentos no se insertan en la tabla si el índice ya existe. Si el índice no existe, el índice no se crea. entero es un valor entre 1 y un máximo que depende del tamaño de la página. La Tabla 2 muestra el valor máximo para cada tamaño de página.

Tabla 2. Longitud máxima de los nodos del documento por tamaño de página

Tamaño de página	Longitud máxima del nodo de documento (bytes)
4KB	817
8KB	1841
16KB	3889
32KB	7985

La semántica XQuery se utiliza para comparaciones de series, donde los blancos de cola son significativos. Esto difiere de la semántica SQL, donde los blancos de cola no son significantes durante las comparaciones.

OCTETS

Especifica que las unidades del atributo de longitud es bytes.

Cuando no se especifican unidades de secuencia para un tipo de datos de una secuencia de caracteres, las unidades de secuencia están implícitas y se determinan mediante el valor de la variable global NLS_STRING_UNITS o parámetro de configuración de la base de datos unidades_serie. Cuando las unidades de serie implícitas son CODEUNITS32, debe especificarse la palabra clave OCTETS (SQLSTATE 42601).

En una base de datos que no sea Unicode, las unidades de secuencia de los tipos de datos de la secuencia de caracteres son OCTETS.

VARCHAR HASHED

Especifique VARCHAR HASHED para gestionar la indexación de series de caracteres de longitud arbitraria. La longitud de una serie indexada no tiene límite. Se genera un código hash de ocho bytes para toda la serie. Los índices que utilizan estas series de caracteres con códigos hash sólo pueden utilizarse para búsquedas de igualdad. La semántica XQuery se utiliza para comparaciones de igualdad de series, donde los blancos de cola son significativos. Esto difiere de la semántica SQL, donde los blancos de cola no son significantes durante las comparaciones. El hash de la serie preserva la igualdad de la semántica de XQuery y no la semántica de SQL.

DOUBLE

Especifica que se utiliza el tipo de datos DOUBLE para indexar valores numéricos. Los tipos de decimal no definido y los enteros de 64 bits pueden perder precisión cuando se almacenan como un valor DOUBLE. Los valores para DOUBLE pueden incluir los valores numéricos especiales NaN, INF, -INF, +0 y -0, aunque el tipo de datos de SQL DOUBLE no soporte estos valores.

INTEGER

Especifica que se utiliza el tipo de datos INTEGER para indexar valores XML. Tenga en cuenta que el tipo de datos de esquema XML xs:integer permite un mayor rango de valores que el tipo de datos SQL de entero. Si se encuentra un valor fuera de rango, se devuelve un error. Si un valor se ajusta al formato léxico de xs:double, pero no se ajusta al formato léxico de xs:int, por ejemplo 3.5, 3.0 o 3E1, también se devuelve un error.

DECIMAL(entero, entero)

Especifica que se utiliza el tipo de datos DECIMAL para indexar valores XML. El tipo DECIMAL adopta dos parámetros: precisión y escala. El primer parámetro, precisión, es una constante entera con un valor incluido en el rango de 1 a 31 que especifica el número total de dígitos. El segundo parámetro, escala, es una constante entera mayor o igual que cero e inferior o igual al valor de la precisión. La escala especifica el número de dígitos situados a la derecha de la coma decimal.

Los dígitos no se truncan a partir del final de un número decimal. Se devuelve un error si el número de dígitos situados a la derecha del carácter separador de decimales es superior al valor de la escala. Además, se devuelve un error si el número de dígitos significativos situados a la izquierda del carácter decimal (la parte entera del número) es superior al valor de la precisión.

FECHA

Especifica que se utiliza el tipo de datos DATE para indexar valores XML. Tenga en cuenta que el tipo de datos de esquema XML para xs: date permite un rango de valores mayor que el tipo de datos pureXML® xs: date que corresponde al tipo de datos SQL. Si se encuentra un valor fuera de rango, se devuelve un error.

TIMESTAMP

Especifica que se utiliza el tipo de datos TIMESTAMP para indexar valores XML. Tenga en cuenta que el tipo de datos de esquema XML para xs:dateTime permite un rango mayor de valores y una precisión de segundos fraccionarios que el tipo de datos pureXML xs:dateTime que corresponde al tipo de datos SQL. Si se encuentra un valor fuera de rango, se devuelve un error.

IGNORE INVALID VALUES

Especifica que los valores de patrón XML que no son formatos léxicos válidos para el tipo de datos XML de índice de destino se ignoran y que la sentencia CREATE INDEX no indexa los valores correspondientes de los documentos XML almacenados. Por omisión, los valores no válidos se ignoran. Durante las operaciones de inserción y actualización, los valores de patrón XML no válidos no se indexan, pero los documentos XML se siguen insertando en la tabla. No se genera ningún error o aviso, porque la especificación de estos tipos de datos no es una restricción en los valores de patrón XML (las expresiones XQuery que buscan el tipo de datos de índice XML específico no tendrán en cuenta estos valores).

Las reglas para establecer los valores de patrón XML que se pueden ignorar se determina mediante el tipo de datos SQL especificado.

• Si el tipo de datos SQL es VARCHAR(entero) o VARCHAR HASHED, los valores de patrón XML no se ignoran nunca porque cualquier secuencia de caracteres es válida.
• Si el tipo de datos SQL es DOUBLE, DECIMAL o INTEGER, los valores de patrón XML que no se ajusten al formato léxico del tipo de datos XML xs:double se ignoran. Si el tipo de datos SQL es DECIMAL o INTEGER y el valor de patrón XML se ajusta al formato léxico del tipo de datos XML xs:double, pero no al formato léxico de xs:decimal o xs:int, respectivamente, se devuelve un error. Por ejemplo, si el tipo de datos SQL es INTEGER, los valores de patrón XML 3.5, 3.0 y 3e0 se ajustan al formato léxico de xs:double, pero devuelven un error (SQLSTATE 23525) porque no se ajustan al formato léxico de xs:int. Los valores de patrón XML del tipo 'A123' o 'hello' se ignoran para el mismo índice.
• Si el tipo de datos SQL es un tipo de datos de indicación de fecha y hora, cualquier valor de patrón XML que no se ajuste al formato léxico del tipo de datos XML correspondiente (xs:date o xs:dateTime) se ignora.

Si un valor de patrón XML no se ajusta al formato léxico adecuado, se devuelve un error si el valor se sale del espacio de valores del tipo de datos o supera la longitud o la precisión y la escala máxima del tipo de datos SQL especificado. Si el índice no existe, no se crea ningún índice (SQLSTATE 23526).

REJECT INVALID VALUES

Todos los valores de patrón XML deben ser válidos en el contexto de la definición léxica del tipo de datos XML de índice. Además del valor debe incluirse en el rango del espacio de valores del tipo de datos XML de índice. Consulte la sección sobre referencia relacionada más adelante para acceder a enlaces con información detallada sobre la definición léxica y el espacio de valores de cada tipo de datos. Por ejemplo, cuando se especifica la cláusula REJECT INVALID VALUES, si se crea un índice del tipo INTEGER, los valores de patrón XML del tipo 3.5, 3.0, 3e0, 'A123' y 'hello' devolverán un error (SQLSTATE 23525). Los datos XML no se insertan ni se actualizan en la tabla si el índice ya existe (SQLSTATE 23525). Si el índice no existe, no se crea ningún índice (SQLSTATE 23526).

CLÚSTER

Especifica que el índice es el índice de clústeres de la tabla. El factor de clúster de un índice de clústeres se mantiene o se mejora dinámicamente cuando los datos se insertan en la tabla asociada al intentar insertar filas nuevas físicamente cerca de las filas para las que los valores de clave de este índice están en el mismo rango. Sólo puede existir un índice de clústeres para una tabla, por lo que no puede especificarse CLUSTER si se utiliza en la definición de cualquier índice existente en la tabla (SQLSTATE 55012). No puede crearse un índice de clústeres en una tabla que esté definida para utilizar la modalidad APPEND (SQLSTATE 428D8).

CLUSTER no está permitido si se especifica apodo , o si el índice es un índice sobre datos XML (SQLSTATE 42601).

Esta cláusula no se puede utilizar con los siguientes tipos de tablas:

• Tablas temporales creadas o tablas temporales declaradas (SQLSTATE 42995)
• Tablas agrupadas en clúster de rangos (SQLSTATE 429BG)
• Tablas organizadas en columnas (SQLSTATE 42858)

EXTEND USING nombre-extensión-índice

Designa la extensión-índice utilizada para gestionar el índice. Si se especifica esta cláusula, debe haber un sólo nombre-columna especificado y esa columna debe ser un tipo estructurado o un tipo diferenciado (SQLSTATE 42997). El nombre-extensión-índice debe ser una extensión de índice descrita en el catálogo (SQLSTATE 42704). Para un tipo diferenciado, la columna debe coincidir exactamente con el tipo del correspondiente parámetro de clave fuente de la extensión de índice. Para una columna de tipo estructurado, el tipo del correspondiente parámetro de clave fuente debe ser el mismo tipo o un supertipo del tipo de columna (SQLSTATE 428E0).

Esta cláusula no se puede utilizar con tablas temporales creadas o tablas temporales declaradas (SQLSTATE 42995).

Esta cláusula no se puede utilizar con tablas organizadas en columnas (SQLSTATE 42858).

A partir de IBM® Db2® 10.5, esta cláusula también está soportada en entornos Db2 pureScale® . Para versión 10.5 Fixpack 3 y releases de fixpack anteriores, esta cláusula no está soportada en entornos Db2 pureScale (SQLSTATE 56038).

Esta cláusula no se puede utilizar si la clave de índice contiene al menos una expresión-clave (SQLSTATE 42601).

expresión-constante

Designa valores para los argumentos necesarios de la extensión de índice. Cada expresión debe ser un valor constante cuyo tipo de datos coincide exactamente con el tipo de datos definido de los correspondientes parámetros de la extensión de índice, incluidas la longitud o precisión, y la escala (SQLSTATE 428E0). Esta cláusula no debe superar los 32768 bytes de longitud en la página de códigos de la base de datos (SQLSTATE 22001).

PCTFREE entero

Especifica qué porcentaje de cada página de índice se va a dejar como espacio libre cuando se cree el índice. La primera entrada de una página se añade sin restricciones. Cuando se colocan entradas adicionales en una página de índice, en cada página se deja, como mínimo, un entero como porcentaje de espacio libre. El valor de entero entra en un rango que va de 0 a 99. Si se especifica un valor superior a 10, sólo se dejará un 10 por ciento de espacio libre en las páginas que no son hojas.

Si no se proporciona un valor explícito para PCTFREE y no se ha establecido DB2_INDEX_PCTFREE_DEFAULT, PCTFREE tendrá un valor predeterminado de 10.

PCTFREE no está permitido si apodo está especificado (SQLSTATE 42601). Esta cláusula no se puede utilizar con tablas temporales creadas o tablas temporales declaradas (SQLSTATE 42995).

LEVEL2 PCTFREE entero

Especifica qué porcentaje de cada página de nivel 2 de índice se va a dejar como espacio libre cuando se cree el índice. El valor de entero entra en un rango que va de 0 a 99. Si LEVEL2 PCTFREE no está establecido, se deja un mínimo de 10 o PCTFREE por ciento de espacio libre en todas las páginas que no son hojas. Si LEVEL2 PCTFREE está establecido, se deja entero por ciento de espacio libre en páginas intermedias de nivel 2 y se deja un mínimo de 10 o entero por ciento de espacio libre en páginas intermedias de nivel 3 y superiores.

LEVEL2 PCTFREE no está permitido si se especifica apodo (SQLSTATE 42601). Esta cláusula no se puede utilizar con tablas temporales creadas o tablas temporales declaradas (SQLSTATE 42995).

MINPCTUSED entero

Indica si las páginas de hoja de índice deben fusionarse en línea y el umbral del porcentaje mínimo de espacio utilizado en una página de hoja de índice. Si, después de eliminarse una clave de una página de hoja de índice, el porcentaje de espacio utilizado en la página es el porcentaje de entero o está por debajo de este, se intentarán fusionar las claves restantes de esta página con las de una página vecina. Si hay espacio suficiente en una de estas páginas, se realiza la fusión y se elimina una de las páginas. El valor de entero puede ir de 0 a 99. Se recomienda un valor de 50 o inferior por motivos de rendimiento. La especificación de esta opción influirá en el rendimiento de la actualización y de la supresión. La fusión solo se realiza durante las operaciones de actualización y supresión cuando se mantiene un bloqueo de tabla exclusivo. Si no existe un bloqueo de tabla exclusivo, las claves se marcan como claves a las que se ha aplicado una supresión falsa durante las operaciones de actualización y supresión y no se realiza ninguna fusión. Considere la utilización de la opción CLEANUP ONLY ALL de REORG INDEXES para fusionar páginas de hoja en lugar de utilizar la opción MINPCTUSED de CREATE INDEX.

MINPCTUSED no está permitido si apodo está especificado (42601). Esta cláusula no se puede utilizar con tablas temporales creadas o tablas temporales declaradas (SQLSTATE 42995).

DISALLOW REVERSE SCANS

Especifica que el índice sólo permite búsquedas o exploraciones hacia delante del índice en el orden definido en el momento de crear el índice.

DISALLOW REVERSE SCANS no puede especificarse junto con apodo (SQLSTATE 42601).

ALLOW REVERSE SCANS

Especifica que un índice permite búsquedas hacia delante y hacia atrás; es decir, permite explorar el índice en el orden definido en el momento de su creación y en el orden opuesto.

ALLOW REVERSE SCANS no puede especificarse junto con apodo (SQLSTATE 42601).

PAGE SPLIT

Especifica el comportamiento de división de páginas cuando se insertan valores en un índice. El valor predeterminado es SYMMETRIC.

SYMMETRIC

Especifica que las páginas se deben dividir por la mitad. Utilice esta opción en las siguientes situaciones:

• Cuando la inserción en un índice es aleatoria
• Cuando la inserción en un índice no sigue los patrones direccionados por las opciones PAGE SPLIT HIGH y PAGE SPLIT LOW

HIGH

Especifica un comportamiento de división de páginas de índice que utiliza el espacio en las páginas de índice de forma eficiente cuando son valores en constante aumento. El aumento de valores en el índice se puede producir cuando se cumplen las siguientes condiciones:

• Hay un índice con varias partes clave y hay varias páginas de índice de valores donde todas excepto la última parte de clave tiene el mismo valor.
• Todas las operaciones de inserción de la tabla constan de un valor nuevo, que tiene el mismo valor que las claves existentes para todas excepto para la última parte de clave.
• La última parte de clave del valor insertado es mayor que los valores de las claves existentes.

Por ejemplo, suponga que un índice tiene los siguientes valores de clave:

   (1,1),(1,2),(1,3), ... (1,n),
   (2,1),(2,2),(2,3), ... (2,n),
   ...
   (m,1),(m,2),(m,3), ... (m,n)

La clave siguiente que se va a insertar tendrá el valor (x,y) donde 1 <= x <= m y y > n.

En tales casos, utilice la cláusula PAGE SPLIT HIGH para que la división de páginas no da como resultado muchas páginas que están vacías al 50 por ciento.

LOW

Especifica un comportamiento de división de páginas de índice que utiliza el espacio en las páginas de índice de forma eficiente cuando son valores en constante descenso. El descenso de valores en el índice se puede producir cuando se cumplen las siguientes condiciones:

• Hay un índice con varias partes clave y hay varias páginas de índice de valores donde todas excepto la última parte de clave tiene el mismo valor.
• Todas las operaciones de inserción de la tabla constan de un valor nuevo, que tiene el mismo valor que las claves existentes para todas excepto para la última parte de clave.
• La última parte de clave del valor insertado es menor que los valores de las claves existentes.

En tales casos, utilice la cláusula PAGE SPLIT LOW para que la división de páginas no da como resultado muchas páginas que están vacías al 50 por ciento.

COLLECT STATISTICS

Especifica que van a recopilarse estadísticas de índice básicas durante la creación del índice.

SAMPLED

Especifica que se debe utilizar una técnica de muestreo cuando se procesen entradas de índice para recopilar estadísticas de índice ampliadas. Esta opción se utiliza para equilibrar las consideraciones sobre rendimiento con la necesidad de precisión de las estadísticas. Esta opción es el valor predeterminado cuando se especifica DETAILED inmediatamente después de la palabra clave COLLECT.

UNSAMPLED

Especifica que no se debe utilizar el muestreo cuando se procesen entradas de índice para recopilar estadísticas de índice ampliadas. Cada entrada de índice, en cambio, se examina de forma individual. Esta opción puede aumentar considerablemente el consumo de CPU y memoria.

DETAILED

Especifica que también van a recopilarse estadísticas de índice ampliadas (CLUSTERFACTOR y PAGE_FETCH_PAIRS) durante la creación del índice.

COMPRESS

Especifica si la compresión de índice está habilitada. De forma predeterminada, la compresión de índice se habilitará si la compresión de filas de datos está habilitada o si la tabla es una tabla temporal global declarada (DGTT) o una tabla temporal global creada (CGTT); la compresión de índice se inhabilitará si la compresión de filas de datos está inhabilitada. Esta opción se puede utilizar para alterar temporalmente el comportamiento por omisión. COMPRESS no está permitido si apodo está especificado (SQLSTATE 42601).

SÍ

Especifica que la compresión de índice está habilitada. Las operaciones de inserción y actualización del índice estarán sujetas a compresión.

NO

Especifica que la compresión de índice está inhabilitada.

INCLUDE NULL KEYS

Especifica que se crea una entrada de índice cuando todas las partes de la clave de índice contienen el valor nulo. Este es el valor predeterminado.

EXCLUDE NULL KEYS

Especifica que no se crea una entrada de índice cuando todas las partes de la clave de índice contienen el valor nulo. Cuando cualquier parte de la clave del índice no es un valor nulo, se crea una entrada de índice. No puede especificar EXCLUDE NULL KEYS con los siguientes elementos de sintaxis:

• Un apodo
• La cláusula GENERATE KEY USING XMLPATTERN
• La cláusula EXTEND USING.

Si se define un índice como único, las filas con claves nulas no se consideran al hacer cumplir la exclusividad.

Esta cláusula no se puede utilizar con tablas organizadas en columnas (SQLSTATE 42858).

Reglas

• La sentencia CREATE INDEX fallará (SQLSTATE 01550) cuando se intenta crear un índice que coincida con un índice ya existente.
  Los factores siguientes se utilizan para determinar si dos índices coinciden. Estos factores se combinan de varias maneras diferentes en las normas que determinan si dos índices coinciden. Los factores siguientes se utilizan para determinar si dos índices coinciden:

  1. Los conjuntos de columnas de índice y expresiones clave, incluida cualquier columna INCLUDE y expresiones clave, son iguales en ambos índices.
  2. El orden de las columnas de claves de índice y expresiones clave, incluida cualquier columna INCLUDE, es el mismo en ambos índices.
  3. Las columnas de claves y expresiones clave del nuevo índice son las mismas o un superconjunto de las columnas de claves y expresiones clave en el índice existente.
  4. El orden de los atributos de las columnas y expresiones de claves es el mismo en ambos índices.
  5. El índice existente es exclusivo.
  6. Ambos índices no son únicos.
  Las combinaciones siguientes de estos factores forman las normas que determinan cuando dos índices se consideran duplicados:

  • 1 + 2 + 4 + 5
  • 1 + 2 + 4 + 6
  • 1 + 2 + 3 + 5
  Excepciones:

  • Si uno de los índices comparados está particionado y el otro no está particionado, los índices no se consideran duplicados si tienen nombres distintos, aunque se cumplan otras condiciones de índices coincidentes.
  • Para los índices de datos XML, dos descripciones de índice no se consideran duplicadas si los nombres del índice son distintos, aun cuando la columna XML indexada, los patrones XML y los tipos de datos, incluidas sus opciones, sean idénticos.
• Los índices exclusivos de las MQT mantenidas por el sistema no reciben soporte (SQLSTATE 42809).
• Las opciones COLLECT STATISTICS no reciben soporte si se ha especificado un apodo (SQLSTATE 42601).
• La creación de un índice con una clave basada en expresiones en un entorno de base de datos particionada se admite sólo a partir de la partición de la base de datos de catálogo (SQLSTATE 42997).
• Restricciones para índices en tablas organizadas en columnas :
  • Las cláusulas siguientes no están soportadas al crear un índice en una tabla organizada por columnas (SQLSTATE 42858):
    • RANDOM
    • CLÚSTER
    • EXTEND USING
    • INCLUDE
    • EXCLUDE NULL KEYS
    • clave-expresión
• Los índices no se pueden crear en tablas temporales organizadas en columnas.

Notas

• El acceso de lectura/grabación simultáneo durante la creación de índices y el comportamiento de creación de índices por omisión difiere para los índices en tablas no particionadas, índices no particionados, índices particionadose índices en un entorno Db2 pureScale:
  • En el caso de índices no particionados, se permite el acceso de lectura/grabación simultáneo a la tabla mientras se está creando un índice, salvo cuando se especifica una cláusula EXTEND USING. Una vez creado el índice, los cambios que se han realizado en la tabla durante la creación del índice se reenvían e incorporan al nuevo índice. El acceso de grabación a la tabla se bloquea mientras se completa la creación del índice, después de lo cual el nuevo índice pasa a estar disponible.
  • En el caso de índices particionados, se permite el acceso de lectura/grabación simultáneo a la tabla mientras se está creando un índice, salvo cuando se especifica una cláusula EXTEND USING. Una vez creada la partición de índice, los cambios que se han realizado en la partición durante la creación de esa partición de índice se reenvían e incorporan a la nueva partición de índice. El acceso de grabación a la partición de datos queda bloqueado mientras se completa la creación del índice en el resto de particiones de datos. Después de crear la partición de índice para la última partición de datos y confirmar la transacción, todas las particiones de datos están disponibles para acceso de lectura y grabación.
  • En un entornoDb2 pureScale, el acceso de lectura simultáneo es el comportamiento predeterminado. Puede habilitar el acceso de lectura simultáneo estableciendo la variable de registro DB2_INDEX_CREATE_ALLOW_WRITE en ON en el entorno pureScale. Para obtener más información, consulte DB2_INDEX_CREATE_ALLOW_WRITE.

  CREATE INDEX intenta reenviar e incorporar los cambios simultáneos antes de bloquear grabadores. Sin embargo, si hay mucha actividad de base de datos simultánea de forma que CREATE INDEX no puede mantener el ritmo con los cambios que llegan, bloqueará los nuevos grabadores rápidamente para poder completar el reenvío e incorporación. CREATE INDEX realiza todo el trabajo de finalización de creación que puede antes de que se bloqueen los grabadores, para que el periodo de no disponibilidad sea lo más corto posible. El tamaño del índice y la cantidad de actividad simultánea afectan a este periodo de tiempo.

  Para eludir este comportamiento por omisión, utilice la sentencia LOCK TABLE para bloquear explícitamente la tabla antes de emitir una sentencia CREATE INDEX. (La tabla puede bloquearse en modalidad SHARE o EXCLUSIVE, en función de si va a estar permitido el acceso de lectura.)

• Si la tabla nombrada ya contiene datos, CREATE INDEX crea las entradas de índice de la misma. Si la tabla todavía no contiene datos, CREATE INDEX crea una descripción del índice; las entradas del índice se crean al insertar los datos en la tabla.
• Una vez que se ha creado el índice y se han cargado los datos en la tabla, es aconsejable emitir el mandato RUNSTATS. El mandato RUNSTATS actualiza las estadísticas que se reúnen en las tablas de bases de datos, las columnas y los índices. Estas estadísticas sirven para determinar la mejor vía de acceso a las tablas. Mediante la emisión del mandato RUNSTATS, el gestor de bases de datos puede determinar las características del nuevo índice. Si se habían cargado datos antes de la emisión de la sentencia CREATE INDEX, se recomienda utilizar la opción COLLECT STATISTICS de la sentencia CREATE INDEX como alternativa a la utilización del mandato RUNSTATS.
• Si recopila estadísticas durante la creación del índice, las estadísticas resultantes puede ser incoherentes. Si se ha modificado la tabla desde la última recopilación de estadísticas en la tabla y sus índices existentes, debe ejecutar posteriormente el mandato RUNSTATS para proporcionar un conjunto de estadísticas coherentes a través de la tabla y todos sus índices.
• La creación de un índice con un nombre de esquema que todavía no existe dará como resultado la creación implícita de ese esquema siempre que el ID de autorización de la sentencia disponga de autorización IMPLICIT_SCHEMA. El propietario del esquema es SYSIBM. El privilegio CREATEIN sobre el esquema se otorga a PUBLIC.
• El optimizador puede recomendar índices antes de crear el índice real.
• Si una especificación de índice se está definiendo para una tabla de fuente de datos, el nombre de la especificación de índice no tiene que coincidir con el nombre del índice.
• El recurso Explain, el Asesor de diseño o la opción EXPLAIN de Data Server Manager se pueden utilizar para recomendar índices antes de crear el índice real. Sin embargo, ninguno de estos métodos recomendará índices que contengan claves basadas en expresiones.
• Recopilación de estadísticas de índice: la opción UNSAMPLED DETAILED está disponible para cambiar el modo en que se recopilan las estadísticas de índice. No obstante, sólo debe utilizarse en los casos donde quede claro que DETAILED no genera estadísticas precisas.
• Objetos generados: si se creó un índice con claves basadas en expresiones, también se creará una vista estadística generada por el sistema y un paquete generado por el sistema y estará asociado con el índice.
• Alternativas de sintaxis: Se tolera y se pasa por alto la sintaxis siguiente:
  • CLOSE
  • DEFINE
  • FREEPAGE
  • GBPCACHE
  • PIECESIZE
  • TYPE 2
  • using-block
  La sintaxis siguiente se acepta como comportamiento por omisión:

  • COPY NO
  • DEFER NO

ejemplos

• Ejemplo 1:  Cree un índice denominado UNIQUE_NAM en la tabla PROJECT. La finalidad de este índice consiste en garantizar que en la misma tabla no habrá dos entradas que tengan el mismo valor para el nombre del proyecto (PROJNAME). Las entradas de índice deben estar en orden ascendente.

     CREATE UNIQUE INDEX UNIQUE_NAM
        ON PROJECT(PROJNAME)

• Ejemplo 2: Cree un índice denominado JOB_BY_DPT en la tabla EMPLOYEE. Disponga las entradas de índice en orden ascendente por el título de los trabajos (JOB) dentro de cada departamento.

     CREATE INDEX JOB_BY_DPT
        ON EMPLOYEE (WORKDEPT, JOB)

• Ejemplo 3: El apodo EMPLOYEE hace referencia a una tabla de origen de datos denominada CURRENT_EMP. Tras la creación de este apodo, se ha definido un índice en CURRENT_EMP. Las columnas escogidas para la clave de índice fueron WORKDEBT y JOB. Cree una especificación de índice que describa a este índice. Mediante esta especificación, el optimizador sabrá que el índice existe y cuál es su clave. Con esta información, el optimizador puede mejorar su estrategia de acceso a la tabla.

     CREATE UNIQUE INDEX JOB_BY_DEPT
       ON EMPLOYEE (WORKDEPT, JOB)
       SPECIFICATION ONLY        

• Ejemplo 4:  Cree un tipo de índice ampliado denominado SPATIAL_INDEX en una ubicación de columna de tipo estructurado. La descripción de la extensión de índice GRID_EXTENSION se utiliza para mantener SPATIAL_INDEX. El literal se proporciona a GRID_EXTENSION para crear el tamaño de cuadrícula de índice.

     CREATE INDEX SPATIAL_INDEX ON CUSTOMER (LOCATION)
       EXTEND USING (GRID_EXTENSION (x'000100100010001000400010'))

• Ejemplo 5: Cree un índice denominado IDX1 en una tabla denominada TAB1 y recopile estadísticas de índice básicas en el índice IDX1.

     CREATE INDEX IDX1 ON TAB1 (col1) COLLECT STATISTICS

• Ejemplo 6: Cree un índice denominado IDX2 en una tabla denominada TAB1 y recopile estadísticas de índice detalladas en el índice IDX2.

     CREATE INDEX IDX2 ON TAB1 (col2) COLLECT DETAILED STATISTICS

• Ejemplo 7: Cree un índice denominado IDX3 en una tabla denominada TAB1 y recopile estadísticas de índice detalladas en el índice IDX3 utilizando el muestreo.

     CREATE INDEX IDX3 ON TAB1 (col3) COLLECT SAMPLED DETAILED STATISTICS

• Ejemplo 8: Cree un índice exclusivo denominado A_IDX en una tabla particionada denominada MYNUMBERDATA en el espacio de tablas IDX_TBSP.

     CREATE UNIQUE INDEX A_IDX ON MYNUMBERDATA (A) IN IDX_TBSP

• Ejemplo 9: Cree un índice no exclusivo denominado B_IDX en una tabla particionada denominada MYNUMBERDATA en el espacio de tablas IDX_TBSP.

     CREATE INDEX B_IDX ON MYNUMBERDATA (B)
       NOT PARTITIONED IN IDX_TBSP

• Ejemplo 10: Cree un índice sobre datos XML en una tabla denominada COMPANYINFO, que contiene una columna XML denominada COMPANYDOCS. La columna XML COMPANYDOCS contiene un número elevado de documentos XML parecidos al siguiente:

  <company name="Company1">
    <emp id="31201" salary="60000" gender="Female">
      <name>
        <first>Laura</first>
        <last>Brown</last>
      </name>
      <dept id="M25">
        Finance
      </dept>
    </emp>
  </company>

  Los usuarios de la tabla COMPANYINFO a menudo deben recuperar información de los empleados utilizando el ID de empleado. Un índice como el siguiente puede hacer que la recuperación de datos sea más eficiente.

     CREATE INDEX EMPINDEX ON COMPANYINFO(COMPANYDOCS)
       GENERATE KEY USING XMLPATTERN '/company/emp/@id'
         AS SQL DOUBLE

• Ejemplo 11: Por lo general, el índice siguiente es equivalente desde el punto de vista lógico al índice creado en el ejemplo anterior, excepto que utiliza una sintaxis abreviada.

     CREATE INDEX EMPINDEX ON COMPANYINFO(COMPANYDOCS)
       GENERATE KEY USING XMLPATTERN '/child::company/child::emp/attribute::id'
         AS SQL DOUBLE

• Ejemplo 12: Cree un índice en una columna denominada DOC, indexando solamente el título del manual como VARCHAR(100). Puesto que el libro del manual debe ser exclusivo entre todos los manuales, el índice también debe ser exclusivo.

     CREATE UNIQUE INDEX MYDOCSIDX ON MYDOCS(DOC)
       GENERATE KEY USING XMLPATTERN '/book/title'
         AS SQL VARCHAR(100)

• Ejemplo 13: Cree un índice en una columna denominada DOC, indexando el número del capítulo como DOUBLE. Este ejemplo incluye declaraciones de espacios de nombres.

     CREATE INDEX MYDOCSIDX ON MYDOCS(DOC)
       GENERATE KEY USING XMLPATTERN
         'declare namespace b="http://www.example.com/book/";
           declare namespace c="http://acme.org/chapters";
             /b:book/c:chapter/@number'
         AS SQL DOUBLE

• Ejemplo 14: Cree un índice exclusivo denominado IDXPROJEST en la tabla PROJECT e incluir la columna PRSTAFF para permitir el acceso sólo a índice de la información de dotación media de personal estimada.

     CREATE UNIQUE INDEX IDXPROJEST ON PROJECT (PROJNO) INCLUDE (PRSTAFF)

• Ejemplo 15: Cree un índice único en una columna llamada USER_ID y excluya las claves nulas de ese índice.

     CREATE UNIQUE INDEX IDXUSERID ON CUSTOMER (USER_ID) EXCLUDE NULL KEYS

• Ejemplo 16: Cree un índice con una clave basada en expresiones usando mayúsculas para el nombre y el ID de empleado:

     CREATE INDEX EMP_UPPERNAME ON EMPLOYEE (UPPER(NAME), ID)