Más

¿Insertar una fila en la tabla y luego actualizarla en la suma de cada columna usando ArcPy?


Mi problema es el siguiente:

  • Tengo una tabla con un número desconocido de columnas (n)

  • Use arcpy.ListFields para crear una lista de columnas y generar una lista de nombres de campo

  • Luego, usando arcpy.da.InsertCursor cree una nueva fila al final de la tabla…

por ejemplo

fieldNameList = [] fields = arcpy.ListFields (fc, "*") para el campo en los campos: if field.type in ("Double", "Integer", "Single"): fieldNameList.append (field.name) cur = arcpy.da.InsertCursor (fc, fieldNameList) para x en xrange (-1, 0): cur.insertRow ((SUM_FIELD1, SUM_FIELD2… SUM_FIELD n)) ????????? !!!! del cur

No sé cómo calcular la suma de cada columna y luego el resultado para actualizar en la fila creada. La suma debe calcularse por separado para cada columna ...


No uso diccionarios (no los entiendo completamente), sin embargo, la nueva fila también puede ser una lista. Comience compilando una lista de las sumas y luego insértela.

fieldNameList = [] values ​​= [] # almacena los valores de la suma aquí. fields = arcpy.ListFields (fc, "*") # obtenga el nombre del campo OID / FID para omitir desc = arcpy.Describe (fc) if desc.hasOID: OIDname = desc.OIDFieldName.upper () else: OIDname = "" for field in fields: if field.name.upper ()! = OIDname: # omita el campo OID / FID. if field.type in ("Double", "Integer", "Single"): # suma cada campo adecuado, pero no los NULL; serían malos con arcpy.da.SearchCursor (fc, field.name, field. name + "no es NULL") como sCur: thisValue = 0 para la fila en sCur: thisValue + = fila [0] valores.append (thisValue) # esta será la fila insertada fieldNameList.append (field.name) con arcpy. da.InsertCursor (fc, fieldNameList) como cur: cur.insertRow (valores)

Por supuesto, esto significa que leerá las filas de cada campo numérico ... pero eso también incluye OID / FID, ¡eso es malo! los valores de OID / FID no se pueden modificar, por lo que es mejor omitir ese. También shape_area, shape_length son de solo lectura, pero esto parece datos tabulares, así que no me preocuparé por eso (esta vez).


Para SQL Server, ¿cómo corregir las actualizaciones simultáneas de tablas paralelas?

Tengo que actualizar todos los registros (agregar Guids) en dos columnas vacías (indexadas) de 150 tablas, cada tabla con alrededor de 50k registros (usando un script para crear 40k actualizaciones a la vez en c # y publicarlo en el servidor) y exactamente 4 existentes columnas.

En mi máquina local (16GB RAM, 500GB Samsung 850, SQL Server 2014, core i5) cuando intento ejecutar 10 tablas en paralelo, se necesita un total de 13 minutos, mientras que si ejecuto 5, el proceso termina en solo 1,7 minutos.

Entiendo que algo está ocupado en el nivel del disco, pero necesito ayuda para cuantificar esta enorme diferencia en los tiempos.

¿Existe una vista exacta de la base de datos de SQL Server que pueda verificar esta discrepancia? ¿Existe una forma exacta de averiguar para un hardware dado cuántas actualizaciones de tabla puedo ejecutar en paralelo? (el servidor de prueba real tiene más RAM y discos de 10k rpm).

¿Alguien puede señalar algo que pueda mejorar en SQL Server para mejorar los tiempos de ejecución de 10 tablas en paralelo?

Ya intenté aumentar el tamaño de crecimiento automático a 100 MB de 10 MB, lo que mejora la longitud de la cola de disco (de alrededor de 5 a 0,1), pero en realidad no reduce tanto el tiempo total.

He hecho exactamente la misma pregunta sobre stackoverflow, pero hasta ahora no he obtenido ninguna respuesta útil, por lo que alguna o alguna información / ayuda sería inmensamente útil. :)


Captura de recuentos de inserciones y actualizaciones a partir de la combinación

Esta publicación muestra cómo puede capturar y almacenar la cantidad de registros insertados, actualizados o eliminados de una declaración de combinación de T-SQL.

Esto es en respuesta a una pregunta en una publicación anterior sobre el uso de Merge para cargar SCD en un almacén de datos.

Puede lograr esto utilizando la cláusula OUTPUT de una declaración de combinación, incluida la columna $ Action que devuelve OUTPUT.

Envuelve la declaración Merge como una subconsulta, agregando la cláusula OUTPUT para devolver detalles sobre lo que sucedió en la combinación. Tenga en cuenta que no puede & # 8217t simplemente seleccionar de esta subconsulta, tiene que haber una instrucción INSERT INTO.

Se devolverá una fila por cada fila tocada por el proceso de combinación.

La columna $ action contendrá INSERT, UPDATE o DELETE, para indicar qué sucedió con esa fila.

También puede incluir Source. * Para incluir los valores de la columna de origen en el conjunto de datos de salida.

También puede incluir DELETED. *, Que devuelve los valores de los registros actualizados antes de que se actualicen, e INSERTED. * Para mostrar los valores después de la actualización. En realidad, los registros no se eliminan ni se insertan, solo se actualizan, pero ELIMINADO / INSERTADO se utiliza como terminología para los valores antiguos / nuevos a ambos lados de la actualización. Al insertar un nuevo registro, los valores ELIMINADOS serán NULL.

A continuación, puede consultar este conjunto de resultados & # 8216MergeOutput & # 8217 en la parte superior de la consulta seleccionando de esta subconsulta.

Sin embargo, existe una limitación: no puede & # 8217t agregar la tabla. Entonces, si queremos resumir las acciones en una sola fila de recuentos de inserción, actualización y eliminación, tenemos que usar una tabla temporal como en el código de muestra a continuación.


Paso 4: cree un certificado de SQL Server autofirmado:

El siguiente paso es crear un certificado autofirmado que esté protegido por la clave maestra de la base de datos. Un certificado es un objeto de seguridad firmado digitalmente que contiene una clave pública (y opcionalmente una privada) para SQL Server. Un argumento opcional al crear un certificado es ENCRIPTACIÓN POR CONTRASEÑA. Este argumento define un método de protección por contraseña de la clave privada del certificado. En nuestra creación del certificado, hemos optado por no incluir este argumento, al hacerlo, estamos especificando que el certificado debe estar protegido por la clave maestra de la base de datos. Obtenga más información sobre los certificados de SQL Server.


Utiliza una instrucción DML MERGE para combinar las operaciones INSERT, UPDATE y DELETE para una tabla particionada en una instrucción y realizarlas de forma atómica.

Poda de particiones cuando se usa una instrucción MERGE

Cuando ejecuta una declaración MERGE en una tabla particionada, puede limitar las particiones involucradas en la declaración usando la pseudocolumna _PARTITIONTIME (para tablas particionadas por tiempo de ingestión) o usando la columna de fecha, marca de tiempo o fecha y hora (para tablas particionadas) . La poda de particiones reduce los costos y mejora el rendimiento de las consultas.

Puede usar condiciones de poda de partición en los siguientes lugares: en un filtro de subconsultas, un filtro de condición de búsqueda o un filtro de condición de combinación.

Cada uno de los ejemplos siguientes consulta una tabla particionada por tiempo de transferencia mediante la pseudocolumna _PARTITIONTIME.

Usar una subconsulta para filtrar datos de origen

Puede utilizar un filtro en una subconsulta para podar particiones. Por ejemplo, en la siguiente declaración MERGE, solo se escanean las filas de la partición & # 392018-01-01 & # 39 en la tabla de origen.

Usando un filtro en la condición de búsqueda de una cláusula when

El optimizador de consultas intenta utilizar un filtro en una condición de búsqueda para eliminar particiones. Por ejemplo, en la siguiente declaración MERGE, solo se escanean las filas de las siguientes particiones en la tabla de destino: & # 392018-01-01 & # 39, & # 392018-01-02 & # 39 y & # 392018-01- 03 & # 39.

En el siguiente ejemplo, la cláusula WHEN NOT MATCHED BY SOURCE necesita todos los datos de la tabla de destino. Como resultado, se escanean todas las particiones y se le cobran los bytes leídos en todas las particiones.

En general, cuando usas las cláusulas WHEN NOT MATCHED y WHEN NOT MATCHED BY SOURCE juntas, BigQuery asume una FULL OUTER JOIN entre las tablas de origen y destino. Normalmente, las particiones no se pueden podar en FULL OUTER JOIN. Sin embargo, si se usa un predicado falso constante, la condición de filtro se puede usar para la poda de particiones. La siguiente consulta utiliza la eliminación de particiones para escanear solo la partición & # 392018-01-01 & # 39 en las tablas de origen y de destino.

Usando un filtro en una merge_condition

El optimizador de consultas intenta utilizar un filtro en merge_condition para podar particiones. Por ejemplo, la siguiente consulta solo escaneará la partición & # 392018-01-01 & # 39 en las tablas de origen y de destino.

En este ejemplo, merge_condition se utiliza como predicado para unir las tablas de origen y destino. El optimizador de consultas puede o no utilizar la inserción de predicados (según el tipo de combinación).

En el siguiente ejemplo, la instrucción MERGE no permite la poda de partición porque el filtro de partición es un predicado en una condición de unión que no se puede aplicar directamente en la tabla.


Beneficios de usar WITH TABLOCK en un INSERT

En algunas circunstancias, hacer INSERT INTO & lttablename & gt (WITH TABLOCK) será más rápido debido al mínimo registro. Esas circunstancias incluyen tener la base de datos en el modelo de recuperación BULK_LOGGED.

¿Existe algún otro beneficio potencial de rendimiento al usar WITH TABLOCK en un INSERT en una tabla vacía cuando la base de datos (tempdb) está utilizando el modelo de recuperación SIMPLE?

Estoy trabajando con SQL Server 2012 Standard Edition.

Mi caso de uso es para crear y luego poblar inmediatamente una tabla temporal dentro de un procedimiento almacenado usando un INSERT. SELECT, que podría contener hasta unos pocos millones de filas. Trato de evitar ese tipo de tempdb abuso, pero a veces es necesario.

Estoy tratando de construir un caso para requerir TABLOCK. No parece que vaya a dañar nada y podría tener algún beneficio. Estoy tratando de averiguar si hay suficiente beneficio potencial para agregarlo en cualquier lugar de nuestra base de código, donde estoy seguro de que no hay otro proceso que quiera escribir en la tabla.

Por lo general, lo estoy insertando en una tabla temporal local recién creada con un PK agrupado, pero a veces uso un montón.


¿Insertar una fila en la tabla y luego actualizarla en la suma de cada columna usando ArcPy? - Sistemas de Información Geográfica

Este capítulo proporciona una introducción a los objetos de esquema y analiza las tablas, que son los tipos más comunes de objetos de esquema.

Este capítulo contiene las siguientes secciones:

Introducción a los objetos de esquema

Un esquema de base de datos es un contenedor lógico para estructuras de datos, llamados objetos de esquema. Ejemplos de objetos de esquema son tablas e índices. Crea y manipula objetos de esquema con SQL.

Una cuenta de usuario de base de datos tiene una contraseña y privilegios de base de datos específicos. Cada cuenta de usuario posee un esquema único, que tiene el mismo nombre que el usuario. El esquema contiene los datos del usuario propietario del esquema. Por ejemplo, la cuenta de usuario de recursos humanos es propietaria del esquema de recursos humanos, que contiene objetos de esquema como la tabla de empleados. En una base de datos de producción, el propietario del esquema generalmente representa una aplicación de base de datos en lugar de una persona.

Dentro de un esquema, cada objeto de esquema de un tipo particular tiene un nombre único. Por ejemplo, hr.employees se refiere a los empleados de la tabla en el esquema hr. La Figura 2-1 muestra un propietario de esquema llamado hr y objetos de esquema dentro del esquema hr.

Esta sección contiene los siguientes temas:

"Descripción general de la seguridad de la base de datos" para obtener más información sobre los usuarios y los privilegios

Tipos de objetos de esquema

Oracle SQL le permite crear y manipular muchos otros tipos de objetos de esquema.

Los principales tipos de objetos de esquema se muestran en la siguiente tabla.

Una tabla almacena datos en filas. Las tablas son los objetos de esquema más importantes en una base de datos relacional.

Los índices son objetos de esquema que contienen una entrada para cada fila indexada de la tabla o grupo de tablas y brindan acceso directo y rápido a las filas. Oracle Database admite varios tipos de índices. Una tabla organizada por índice es una tabla en la que los datos se almacenan en una estructura de índice.

Las particiones son piezas de grandes tablas e índices. Cada partición tiene su propio nombre y, opcionalmente, puede tener sus propias características de almacenamiento.

Las vistas son presentaciones personalizadas de datos en una o más tablas u otras vistas. Puede pensar en ellos como consultas almacenadas. Las vistas en realidad no contienen datos.

Una secuencia es un objeto creado por el usuario que puede ser compartido por varios usuarios para generar números enteros. Normalmente, utiliza secuencias para generar valores de clave primaria.

Una dimensión define una relación padre-hijo entre pares de conjuntos de columnas, donde todas las columnas de un conjunto de columnas deben provenir de la misma tabla. Las dimensiones se utilizan comúnmente para categorizar datos como clientes, productos y tiempo.

Un sinónimo es un alias de otro objeto de esquema. Debido a que un sinónimo es simplemente un alias, no requiere más almacenamiento que su definición en el diccionario de datos.

Subprogramas y paquetes PL / SQL

PL / SQL es la extensión de procedimiento de Oracle de SQL. Un subprograma PL / SQL es un bloque PL / SQL con nombre que se puede invocar con un conjunto de parámetros. Un paquete PL / SQL agrupa tipos, variables y subprogramas de PL / SQL relacionados lógicamente.

Otros tipos de objetos también se almacenan en la base de datos y se pueden crear y manipular con sentencias SQL pero no están contenidos en un esquema. Estos objetos incluyen cuentas de usuario de base de datos, roles, contextos y objetos de diccionario.

Guía del administrador de la base de datos Oracle para aprender a administrar objetos de esquema

Referencia del lenguaje SQL de Oracle Database para obtener más información sobre los objetos de esquema y los objetos de base de datos.

Almacenamiento de objetos de esquema

Algunos objetos de esquema almacenan datos en un tipo de estructura de almacenamiento lógica denominada segmento. Por ejemplo, una tabla organizada en montón no particionada o un índice crea un segmento.

Otros objetos de esquema, como vistas y secuencias, constan únicamente de metadatos. Este tema describe solo los objetos de esquema que tienen segmentos.

Oracle Database almacena un objeto de esquema de forma lógica dentro de un espacio de tabla. No hay relación entre esquemas y espacios de tabla: un espacio de tabla puede contener objetos de diferentes esquemas y los objetos de un esquema pueden estar contenidos en diferentes espacios de tabla. Los datos de cada objeto están contenidos físicamente en uno o más archivos de datos.

La siguiente figura muestra una posible configuración de segmentos de índice y tabla, espacios de tabla y archivos de datos. El segmento de datos de una tabla abarca dos archivos de datos, que forman parte del mismo espacio de tabla. Un segmento no puede abarcar varios espacios de tabla.

Figura 2-2 Segmentos, espacios de tabla y archivos de datos

"Estructuras de almacenamiento lógicas" para obtener información sobre los espacios de tabla y los segmentos

Guía del administrador de la base de datos de Oracle para aprender a administrar el almacenamiento de objetos de esquema

Dependencias de objetos de esquema

Algunos objetos de esquema hacen referencia a otros objetos, creando una dependencia de objeto de esquema.

Por ejemplo, una vista contiene una consulta que hace referencia a tablas o vistas, mientras que un subprograma PL / SQL invoca otros subprogramas. Si la definición del objeto A hace referencia al objeto B, entonces A es un objeto dependiente de B y B es un objeto referenciado para A.

Oracle Database proporciona un mecanismo automático para garantizar que un objeto dependiente esté siempre actualizado con respecto a sus objetos referenciados. Cuando crea un objeto dependiente, la base de datos rastrea las dependencias entre el objeto dependiente y sus objetos referenciados. Cuando un objeto referenciado cambia de una manera que podría afectar a un objeto dependiente, la base de datos marca el objeto dependiente como inválido. Por ejemplo, si un usuario elimina una tabla, no se puede utilizar ninguna vista basada en la tabla eliminada.

Un objeto dependiente no válido debe recompilarse con la nueva definición de un objeto referenciado antes de que el objeto dependiente sea utilizable. La recompilación se produce automáticamente cuando se hace referencia al objeto dependiente no válido.

Como ilustración de cómo los objetos de esquema pueden crear dependencias, el siguiente script de muestra crea una tabla test_table y luego un procedimiento que consulta esta tabla:

La siguiente consulta del estado del procedimiento test_proc muestra que es válido:

Después de agregar la columna col3 a test_table, el procedimiento sigue siendo válido porque el procedimiento no tiene dependencias en esta columna:

Sin embargo, cambiar el tipo de datos de la columna col1, de la que depende el procedimiento test_proc, invalida el procedimiento:

Ejecutar o recompilar el procedimiento hace que sea válido nuevamente, como se muestra en el siguiente ejemplo:

Esquemas SYS y SYSTEM

Todas las bases de datos de Oracle incluyen cuentas administrativas predeterminadas.

Las cuentas administrativas tienen muchos privilegios y están destinadas solo a administradores de bases de datos autorizados para realizar tareas como iniciar y detener la base de datos, administrar la memoria y el almacenamiento, crear y administrar usuarios de la base de datos, etc.

La cuenta administrativa de SYS se crea automáticamente cuando se crea una base de datos. Esta cuenta puede realizar todas las funciones administrativas de la base de datos. El esquema SYS almacena las tablas y vistas base para el diccionario de datos. Estas vistas y tablas base son críticas para el funcionamiento de Oracle Database. Las tablas en el esquema SYS son manipuladas solo por la base de datos y nunca deben ser modificadas por ningún usuario.

La cuenta administrativa del SISTEMA también se crea automáticamente cuando se crea una base de datos. El esquema del SISTEMA almacena tablas y vistas adicionales que muestran información administrativa, y tablas y vistas internas utilizadas por varias opciones y herramientas de Oracle Database. Nunca use el esquema SYSTEM para almacenar tablas de interés para usuarios no administrativos.

Guía del administrador de la base de datos de Oracle para obtener información sobre SYS, SYSTEM y otras cuentas administrativas

Esquemas de muestra

Una base de datos de Oracle puede incluir esquemas de muestra, que son un conjunto de esquemas interconectados que permiten que la documentación de Oracle y los materiales de instrucción de Oracle ilustren las tareas comunes de las bases de datos.

El esquema de muestra de recursos humanos contiene información sobre empleados, departamentos y ubicaciones, historiales de trabajo, etc. La siguiente ilustración muestra un diagrama entidad-relación de las tablas en hr. La mayoría de los ejemplos de este manual utilizan objetos de este esquema.

Esquemas de muestra de la base de datos de Oracle para aprender a instalar los esquemas de muestra

Resumen de tablas

Una tabla es la unidad básica de organización de datos en una base de datos Oracle.

Una tabla describe una entidad, que es algo importante sobre qué información debe registrarse. Por ejemplo, un empleado podría ser una entidad.

Las tablas de Oracle Database se dividen en las siguientes categorías básicas:

Las tablas relacionales tienen columnas simples y son el tipo de tabla más común. El ejemplo 2-1 muestra una instrucción CREATE TABLE para una tabla relacional.

Las columnas corresponden a los atributos de nivel superior de un tipo de objeto. Consulte "Descripción general de las tablas de objetos".

Puede crear una tabla relacional con las siguientes características organizativas:

Una tabla organizada en montón no almacena filas en ningún orden en particular. La instrucción CREATE TABLE crea una tabla organizada en montón de forma predeterminada.

Una tabla organizada por índices ordena las filas de acuerdo con los valores de la clave principal. Para algunas aplicaciones, las tablas organizadas por índices mejoran el rendimiento y utilizan el espacio en disco de manera más eficiente. Consulte "Descripción general de las tablas organizadas por índices".

Una tabla externa es una tabla de solo lectura cuyos metadatos se almacenan en la base de datos pero cuyos datos se almacenan fuera de la base de datos. Consulte "Descripción general de tablas externas".

Una mesa es permanente o temporal. Una definición de tabla permanente y los datos persisten en todas las sesiones. Una definición de tabla temporal persiste de la misma manera que una definición de tabla permanente, pero los datos existen solo durante la duración de una transacción o sesión. Las tablas temporales son útiles en aplicaciones donde un conjunto de resultados debe mantenerse temporalmente, quizás porque el resultado se construye ejecutando varias operaciones.

Este tema contiene los siguientes temas:

Columnas

Una definición de tabla incluye un nombre de tabla y un conjunto de columnas.

Una columna identifica un atributo de la entidad descrita por la tabla. Por ejemplo, la columna employee_id en la tabla de empleados se refiere al atributo de ID de empleado de una entidad de empleado.

En general, le da a cada columna un nombre de columna, un tipo de datos y un ancho cuando crea una tabla. Por ejemplo, el tipo de datos para employee_id es NUMBER (6), lo que indica que esta columna solo puede contener datos numéricos de hasta 6 dígitos de ancho. El ancho se puede predeterminar por el tipo de datos, como con FECHA.

Columnas virtuales

Una tabla puede contener una columna virtual que, a diferencia de una columna no virtual, no consume espacio en disco.

La base de datos deriva los valores en una columna virtual bajo demanda calculando un conjunto de expresiones o funciones especificadas por el usuario. Por ejemplo, el ingreso de la columna virtual podría ser una función de las columnas salario y comisión_pct.

Guía del administrador de la base de datos Oracle para aprender a administrar columnas virtuales

Columnas invisibles

Una columna invisible es una columna especificada por el usuario cuyos valores solo son visibles cuando la columna se especifica explícitamente por nombre. Puede agregar una columna invisible a una tabla sin afectar las aplicaciones existentes y hacer que la columna sea visible si es necesario.

En general, las columnas invisibles ayudan a migrar y evolucionar las aplicaciones en línea. Un caso de uso podría ser una aplicación que consulta una tabla de tres columnas con una declaración SELECT *. Agregar una cuarta columna a la tabla rompería la aplicación, que espera tres columnas de datos. Agregar una cuarta columna invisible hace que la aplicación funcione normalmente. Luego, un desarrollador puede modificar la aplicación para manejar una cuarta columna y hacer que la columna sea visible cuando la aplicación se active.

El siguiente ejemplo crea una tabla de productos con un recuento de columnas invisibles y luego hace que la columna invisible sea visible:

Guía del administrador de la base de datos Oracle para aprender a administrar columnas invisibles

Referencia del lenguaje SQL de Oracle Database para obtener más información sobre columnas invisibles

Una fila es una colección de información de columna correspondiente a un registro en una tabla.

Por ejemplo, una fila en la tabla de empleados describe los atributos de un empleado específico: ID de empleado, apellido, nombre, etc. Después de crear una tabla, puede insertar, consultar, eliminar y actualizar filas mediante SQL.

Ejemplo: instrucciones CREATE TABLE y ALTER TABLE

La instrucción SQL de Oracle para crear una tabla es CREATE TABLE.

Ejemplo 2-1 empleados de CREATE TABLE

El siguiente ejemplo muestra la instrucción CREATE TABLE para la tabla de empleados en el esquema de muestra hr. La declaración especifica columnas como employee_id, first_name, etc., especificando un tipo de datos como NUMBER o DATE para cada columna.

Ejemplo 2-2 empleados de ALTER TABLE

El siguiente ejemplo muestra una instrucción ALTER TABLE que agrega restricciones de integridad a la tabla de empleados. Las restricciones de integridad hacen cumplir las reglas comerciales y evitan la entrada de información no válida en las tablas.

Ejemplo 2-3 Filas en la tabla de empleados

El siguiente resultado de muestra muestra 8 filas y 6 columnas de la tabla hr.employees.

El resultado anterior ilustra algunas de las siguientes características importantes de tablas, columnas y filas:

Una fila de la tabla describe los atributos de un empleado: nombre, salario, departamento, etc. Por ejemplo, la primera fila de la salida muestra el registro del empleado llamado Steven King.

Una columna describe un atributo del empleado. En el ejemplo, la columna employee_id es la clave principal, lo que significa que cada empleado está identificado de forma única por su ID de empleado. Se garantiza que dos empleados no tendrán la misma identificación de empleado.

Una columna sin clave puede contener filas con valores idénticos. En el ejemplo, el valor del salario para los empleados 101 y 102 es el mismo: 17000.

Una columna de clave externa se refiere a una clave principal o única en la misma tabla o en una tabla diferente. En este ejemplo, el valor de 90 en department_id corresponde a la columna department_id de la tabla de departamentos.

Un campo es la intersección de una fila y una columna. Puede contener solo un valor. Por ejemplo, el campo para el ID de departamento del empleado 103 contiene el valor 60.

Un campo puede carecer de valor. En este caso, se dice que el campo contiene un valor nulo. El valor de la columna Commission_pct para el empleado 100 es nulo, mientras que el valor del campo para el empleado 149 es 0,2. Una columna permite valores nulos a menos que se haya definido una restricción de integridad de clave principal o NOT NULL en esta columna, en cuyo caso no se puede insertar ninguna fila sin un valor para esta columna.

Referencia del lenguaje SQL de Oracle Database para la sintaxis y semántica de CREATE TABLE

Tipos de datos de Oracle

Cada columna tiene un tipo de datos, que está asociado con un formato de almacenamiento específico, restricciones y rango válido de valores. El tipo de datos de un valor asocia un conjunto fijo de propiedades con el valor.

Estas propiedades hacen que Oracle Database trate los valores de un tipo de datos de manera diferente a los valores de otro. Por ejemplo, puede multiplicar valores del tipo de datos NUMBER, pero no valores del tipo de datos RAW.

Cuando crea una tabla, debe especificar un tipo de datos para cada una de sus columnas. Cada valor que se inserta posteriormente en una columna asume el tipo de datos de la columna.

Oracle Database proporciona varios tipos de datos integrados. Los tipos de datos más utilizados se dividen en las siguientes categorías:

Otras categorías importantes de tipos integrados incluyen sin formato, objetos grandes (LOB) y colecciones. PL / SQL tiene tipos de datos para constantes y variables, que incluyen BOOLEAN, tipos de referencia, tipos compuestos (registros) y tipos definidos por el usuario.

Referencia del lenguaje SQL de Oracle Database para aprender sobre los tipos de datos SQL integrados

Guía de desarrollo de bases de datos de Oracle para aprender a utilizar los tipos de datos integrados

Tipos de datos de caracteres

Los tipos de datos de caracteres almacenan datos alfanuméricos en cadenas. El tipo de datos de caracteres más común es VARCHAR2, que es la opción más eficaz para almacenar datos de caracteres.

Los valores de bytes corresponden al esquema de codificación de caracteres, generalmente llamado juego de caracteres. El juego de caracteres de la base de datos se establece en la creación de la base de datos. Algunos ejemplos de juegos de caracteres son ASCII de 7 bits, EBCDIC y Unicode UTF-8.

La semántica de longitud de los tipos de datos de caracteres se puede medir en bytes o caracteres. El tratamiento de las cadenas como una secuencia de bytes se denomina semántica de bytes. Este es el valor predeterminado para los tipos de datos de caracteres. El tratamiento de las cadenas como una secuencia de caracteres se denomina semántica de caracteres. Un carácter es un punto de código del juego de caracteres de la base de datos.

Oracle Database 2 Day Developer's Guide para una breve introducción a los tipos de datos

Guía de desarrollo de bases de datos Oracle para aprender a elegir un tipo de datos de caracteres

Tipos de datos VARCHAR2 y CHAR

Por ejemplo, 'LILA', 'St. George Island 'y' 101 'son todos literales de caracteres 5001 es un literal numérico. Los literales de caracteres se incluyen entre comillas simples para que la base de datos pueda distinguirlos de los nombres de objetos de esquema.

Este manual utiliza los términos literal de texto, literal de carácter y cadena de manera intercambiable.

Cuando crea una tabla con una columna VARCHAR2, especifica una longitud máxima de cadena. En el ejemplo 2-1, la columna last_name tiene un tipo de datos de VARCHAR2 (25), lo que significa que cualquier nombre almacenado en la columna tiene un máximo de 25 bytes.

Para cada fila, Oracle Database almacena cada valor en la columna como un campo de longitud variable a menos que un valor exceda la longitud máxima, en cuyo caso la base de datos devuelve un error. Por ejemplo, en un juego de caracteres de un solo byte, si ingresa 10 caracteres para el valor de la columna last_name en una fila, entonces la columna en la parte de la fila almacena solo 10 caracteres (10 bytes), no 25. El uso de VARCHAR2 reduce el consumo de espacio.

A diferencia de VARCHAR2, CHAR almacena cadenas de caracteres de longitud fija. Cuando crea una tabla con una columna CHAR, la columna requiere una longitud de cadena. El valor predeterminado es 1 byte. La base de datos utiliza espacios en blanco para rellenar el valor hasta la longitud especificada.

Oracle Database compara los valores VARCHAR2 utilizando semántica de comparación sin relleno y compara los valores CHAR utilizando semántica de comparación con relleno en blanco.

Referencia del lenguaje SQL de Oracle Database para obtener detalles sobre la semántica de comparación con espacios en blanco y sin relleno

Tipos de datos NCHAR y NVARCHAR2

Unicode es un juego de caracteres codificado universal que puede almacenar información en cualquier idioma usando un solo juego de caracteres. NCHAR almacena cadenas de caracteres de longitud fija que corresponden al juego de caracteres nacional, mientras que NVARCHAR2 almacena cadenas de caracteres de longitud variable.

Especifica un juego de caracteres nacional al crear una base de datos. El juego de caracteres de los tipos de datos NCHAR y NVARCHAR2 debe ser AL16UTF16 o UTF8. Ambos juegos de caracteres utilizan codificación Unicode.

Cuando crea una tabla con una columna NCHAR o NVARCHAR2, el tamaño máximo siempre está en la semántica de longitud de caracteres. La semántica de longitud de caracteres es la semántica de longitud predeterminada y única para NCHAR o NVARCHAR2.

Guía de soporte de globalización de la base de datos de Oracle para obtener información sobre la función de soporte de globalización de Oracle

Tipos de datos numéricos

Los tipos de datos numéricos de Oracle Database almacenan números fijos y de punto flotante, cero e infinito. Algunos tipos numéricos también almacenan valores que son el resultado indefinido de una operación, que se conoce como "no es un número" o NaN.

Oracle Database almacena datos numéricos en formato de longitud variable. Cada valor se almacena en notación científica, con 1 byte utilizado para almacenar el exponente. La base de datos utiliza hasta 20 bytes para almacenar la mantisa, que es la parte de un número de punto flotante que contiene sus dígitos significativos. Oracle Database no almacena ceros iniciales y finales.

NUMBER Tipo de datos

El tipo de datos NUMBER almacena números de coma flotante y fija. La base de datos puede almacenar números de prácticamente cualquier magnitud. Se garantiza que estos datos serán portátiles entre diferentes sistemas operativos que ejecutan Oracle Database. El tipo de datos NUMBER se recomienda para la mayoría de los casos en los que debe almacenar datos numéricos.

Usted especifica un número de coma fija en la forma NÚMERO (p, s), donde pys se refieren a las siguientes características:

La precisión especifica el número total de dígitos. Si no se especifica una precisión, la columna almacena los valores exactamente como los proporciona la aplicación sin ningún redondeo.

La escala especifica el número de dígitos desde el punto decimal hasta el dígito menos significativo. La escala positiva cuenta los dígitos a la derecha del punto decimal hasta el dígito menos significativo inclusive. La escala negativa cuenta los dígitos a la izquierda del punto decimal hasta el dígito menos significativo, pero sin incluirlo. Si especifica una precisión sin escala, como en NÚMERO (6), entonces la escala es 0.

En el ejemplo 2-1, la columna de salario es de tipo NÚMERO (8,2), por lo que la precisión es 8 y la escala es 2. Por lo tanto, la base de datos almacena un salario de 100.000 como 100000,00.

Números de punto flotante

Oracle Database proporciona dos tipos de datos numéricos exclusivamente para números de punto flotante: BINARY_FLOAT y BINARY_DOUBLE.

Estos tipos admiten toda la funcionalidad básica proporcionada por el tipo de datos NUMBER. Sin embargo, mientras que NUMBER usa precisión decimal, BINARY_FLOAT y BINARY_DOUBLE usan precisión binaria, lo que permite cálculos aritméticos más rápidos y generalmente reduce los requisitos de almacenamiento.

BINARY_FLOAT y BINARY_DOUBLE son tipos de datos numéricos aproximados. Almacenan representaciones aproximadas de valores decimales, en lugar de representaciones exactas. Por ejemplo, el valor 0.1 no puede ser representado exactamente por BINARY_DOUBLE o BINARY_FLOAT. Se utilizan con frecuencia para cálculos científicos. Su comportamiento es similar al de los tipos de datos FLOAT y DOUBLE en Java y XMLSchema.

Referencia del lenguaje SQL de Oracle Database para aprender sobre precisión, escala y otras características de los tipos numéricos

Tipos de datos de fecha y hora

Los tipos de datos de fecha y hora son DATE y TIMESTAMP. Oracle Database proporciona soporte completo de zona horaria para marcas de tiempo.

FECHA Tipo de datos

El tipo de datos DATE almacena la fecha y la hora. Aunque las fechas y horas se pueden representar en tipos de datos de caracteres o números, FECHA tiene propiedades asociadas especiales.

La base de datos almacena las fechas internamente como números. Las fechas se almacenan en campos de longitud fija de 7 bytes cada uno, correspondientes al siglo, año, mes, día, hora, minuto y segundo.

Dates fully support arithmetic operations, so you add to and subtract from dates just as you can with numbers.

The database displays dates according to the specified format model . A format model is a character literal that describes the format of a datetime in a character string. The standard date format is DD-MON-RR , which displays dates in the form 01-JAN-11 .

RR is similar to YY (the last two digits of the year), but the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Assume that in 1999 the database displays 01-JAN-11 . If the date format uses RR , then 11 specifies 2011 , whereas if the format uses YY , then 11 specifies 1911 . You can change the default date format at both the database instance and session level.

Oracle Database stores time in 24-hour format— HH:MI:SS . If no time portion is entered, then by default the time in a date field is 00:00:00 A.M . In a time-only entry, the date portion defaults to the first day of the current month.

Oracle Database Development Guide for more information about centuries and date format masks

Oracle Database SQL Language Reference for information about datetime format codes

Oracle Database Development Guide to learn how to perform arithmetic operations with datetime data types

TIMESTAMP Data Type

The TIMESTAMP data type is an extension of the DATE data type.

TIMESTAMP stores fractional seconds in addition to the information stored in the DATE data type. The TIMESTAMP data type is useful for storing precise time values, such as in applications that must track event order.

The DATETIME data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are time-zone aware. When a user selects the data, the value is adjusted to the time zone of the user session. This data type is useful for collecting and evaluating date information across geographic regions.

Oracle Database SQL Language Reference for details about the syntax of creating and entering data in time stamp columns

Rowid Data Types

Every row stored in the database has an address. Oracle Database uses a ROWID data type to store the address (rowid) of every row in the database.

Rowids fall into the following categories:

Physical rowids store the addresses of rows in heap-organized tables, table clusters, and table and index partitions.

Logical rowids store the addresses of rows in index-organized tables.

Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids.

A data type called the universal rowid , or urowid, supports all types of rowids.

Use of Rowids

A B-tree index , which is the most common type, contains an ordered list of keys divided into ranges. Each key is associated with a rowid that points to the associated row's address for fast access.

End users and application developers can also use rowids for several important functions:

Rowids are the fastest means of accessing particular rows.

Rowids provide the ability to see how a table is organized.

Rowids are unique identifiers for rows in a given table.

You can also create tables with columns defined using the ROWID data type. For example, you can define an exception table with a column of data type ROWID to store the rowids of rows that violate integrity constraints. Columns defined using the ROWID data type behave like other table columns: values can be updated, and so on.

ROWID Pseudocolumn

Every table in an Oracle database has a pseudocolumn named ROWID .

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a SQL function without arguments. Functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

Values of the ROWID pseudocolumn are strings representing the address of each row. These strings have the data type ROWID . This pseudocolumn is not evident when listing the structure of a table by executing SELECT or DESCRIBE , nor does the pseudocolumn consume space. However, the rowid of each row can be retrieved with a SQL query using the reserved word ROWID as a column name.

The following example queries the ROWID pseudocolumn to show the rowid of the row in the employees table for employee 100:

Oracle Database Development Guide to learn how to identify rows by address

Format Models and Data Types

A format model is a character literal that describes the format of datetime or numeric data stored in a character string. A format model does not change the internal representation of the value in the database.

When you convert a character string into a date or number, a format model determines how the database interprets the string. In SQL, you can use a format model as an argument of the TO_CHAR and TO_DATE functions to format a value to be returned from the database or to format a value to be stored in the database.

The following statement selects the salaries of the employees in Department 80 and uses the TO_CHAR function to convert these salaries into character values with the format specified by the number format model '$99,990.99' :

The following example updates a hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the string '1998 05 20' to a DATE value:

Integrity Constraints

An integrity constraint is a named rule that restrict the values for one or more columns in a table.

Data integrity rules prevent invalid data entry into tables. Also, constraints can prevent the deletion of a table when certain dependencies exist.

If a constraint is enabled, then the database checks data as it is entered or updated. Oracle Database prevents data that does not conform to the constraint from being entered. If a constraint is disabled, then Oracle Database allows data that does not conform to the constraint to enter the database.

In Example 2-1, the CREATE TABLE statement specifies NOT NULL constraints for the last_name , email , hire_date , and job_id columns. The constraint clauses identify the columns and the conditions of the constraint. These constraints ensure that the specified columns contain no null values. For example, an attempt to insert a new employee without a job ID generates an error.

You can create a constraint when or after you create a table. You can temporarily disable constraints if needed. The database stores constraints in the data dictionary .

"Data Integrity" to learn about integrity constraints

"Overview of the Data Dictionary" to learn about the data dictionary

Oracle Database SQL Language Reference to learn about SQL constraint clauses

Table Storage

Oracle Database uses a data segment in a tablespace to hold table data.

A segment contains extents made up of data blocks. The data segment for a table (or cluster data segment, for a table cluster ) is located in either the default tablespace of the table owner or in a tablespace named in the CREATE TABLE statement.

"User Segments" to learn about the types of segments and how they are created

Table Organization

By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows.

Index-organized tables use a different principle of organization.

As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.

The hr.departments table is a heap-organized table. It has columns for department ID, name, manager ID, and location ID. As rows are inserted, the database stores them wherever they fit. A data block in the table segment might contain the unordered rows shown in the following example:

The column order is the same for all rows in a table. The database usually stores columns in the order in which they were listed in the CREATE TABLE statement, but this order is not guaranteed. For example, if a table has a column of type LONG , then Oracle Database always stores this column last in the row. Also, if you add a new column to a table, then the new column becomes the last column stored.

A table can contain a virtual column , which unlike normal columns does not consume space on disk. The database derives the values in a virtual column on demand by computing a set of user-specified expressions or functions. You can index virtual columns, collect statistics on them, and create integrity constraints. Thus, virtual columns are much like nonvirtual columns.

Row Storage

The database stores rows in data blocks. Each row of a table containing data for less than 256 columns is contained in one or more row pieces.

If possible, Oracle Database stores each row as one row piece . However, if all of the row data cannot be inserted into a single data block, or if an update to an existing row causes the row to outgrow its data block, then the database stores the row using multiple row pieces.

Rows in a table cluster contain the same information as rows in nonclustered tables. Additionally, rows in a table cluster contain information that references the cluster key to which they belong.

"Data Block Format" to learn about the components of a data block

Rowids of Row Pieces

Every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.

Oracle Database uses rowids internally for the construction of indexes. For example, each key in a B-tree index is associated with a rowid that points to the address of the associated row for fast access. Physical rowids provide the fastest possible access to a table row, enabling the database to retrieve a row in as little as a single I/O.

"Rowid Format" to learn about the structure of a rowid

"Overview of B-Tree Indexes" to learn about the types and structure of B-tree indexes

Storage of Null Values

A null is the absence of a value in a column. Nulls indicate missing, unknown, or inapplicable data.

Nulls are stored in the database if they fall between columns with data values. En estos casos, requieren 1 byte para almacenar la longitud de la columna (cero). Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, then no data is stored for these columns.

Table Compression

The database can use table compression to reduce the amount of storage required for the table.

Compression saves disk space, reduces memory use in the database buffer cache, and in some cases speeds query execution. Table compression is transparent to database applications.

Basic Table Compression and Advanced Row Compression

Dictionary-based table compression provides good compression ratios for heap-organized tables.

Oracle Database supports the following types of dictionary-based table compression:

This type of compression is intended for bulk load operations. The database does not compress data modified using conventional DML. You must use direct path INSERT operations, ALTER TABLE . . . MOVE operations, or online table redefinition to achieve basic table compression.

This type of compression is intended for OLTP applications and compresses data manipulated by any SQL operation. The database achieves a competitive compression ratio while enabling the application to perform DML in approximately the same amount of time as DML on an uncompressed table.

For the preceding types of compression, the database stores compressed rows in row major format . All columns of one row are stored together, followed by all columns of the next row, and so on. The database replaces duplicate values with a short reference to a symbol table stored at the beginning of the block. Thus, information that the database needs to re-create the uncompressed data is stored in the data block itself.

Compressed data blocks look much like normal data blocks. Most database features and functions that work on regular data blocks also work on compressed blocks.

You can declare compression at the tablespace, table, partition, or subpartition level. If specified at the tablespace level, then all tables created in the tablespace are compressed by default.

Example 2-4 Table-Level Compression

The following statement applies advanced row compression to the orders table:

Example 2-5 Partition-Level Compression

The following example of a partial CREATE TABLE statement specifies advanced row compression for one partition and basic table compression for the other partition:

"Row Format" to learn how values are stored in a row

"Data Block Compression" to learn about the format of compressed data blocks

"SQL*Loader" to learn about using SQL*Loader for direct path loads

Hybrid Columnar Compression

With Hybrid Columnar Compression, the database stores the same column for a group of rows together. The data block does not store data in row-major format, but uses a combination of both row and columnar methods.

Storing column data together, with the same data type and similar characteristics, dramatically increases the storage savings achieved from compression. The database compresses data manipulated by any SQL operation, although compression levels are higher for direct path loads. Database operations work transparently against compressed objects, so no application changes are required.

Hybrid Column Compression and In-Memory Column Store (IM column store) are closely related. The primary difference is that Hybrid Column Compression optimizes disk storage, whereas the IM column store optimizes memory storage.

"In-Memory Area" to learn more about the IM column store

Types of Hybrid Columnar Compression

If your underlying storage supports Hybrid Columnar Compression, then you can specify different types of compression, depending on your requirements.

The compression options are:

This type of compression is optimized to save storage space, and is intended for data warehouse applications.

This type of compression is optimized for maximum compression levels, and is intended for historical data and data that does not change.

Hybrid Columnar Compression is optimized for data warehousing and decision support applications on Oracle Exadata storage. Oracle Exadata maximizes the performance of queries on tables that are compressed using Hybrid Columnar Compression, taking advantage of the processing power, memory, and Infiniband network bandwidth that are integral to the Oracle Exadata storage server.

Other Oracle storage systems support Hybrid Columnar Compression, and deliver the same space savings as on Oracle Exadata storage, but do not deliver the same level of query performance. For these storage systems, Hybrid Columnar Compression is ideal for in-database archiving of older data that is infrequently accessed.

Compression Units

Hybrid Columnar Compression uses a logical construct called a compression unit to store a set of rows.

When you load data into a table, the database stores groups of rows in columnar format, with the values for each column stored and compressed together. After the database has compressed the column data for a set of rows, the database fits the data into the compression unit.

For example, you apply Hybrid Columnar Compression to a daily_sales table. At the end of every day, you populate the table with items and the number sold, with the item ID and date forming a composite primary key. The following table shows a subset of the rows in daily_sales .

Table 2-2 Sample Table daily_sales

Assume that this subset of rows is stored in one compression unit. Hybrid Columnar Compression stores the values for each column together, and then uses multiple algorithms to compress each column. The database chooses the algorithms based on a variety of factors, including the data type of the column, the cardinality of the actual values in the column, and the compression level chosen by the user.

As shown in the following graphic, each compression unit can span multiple data blocks. The values for a particular column may or may not span multiple blocks.

Figure 2-4 Compression Unit

If Hybrid Columnar Compression does not lead to space savings, then the database stores the data in the DBMS_COMPRESSION.COMP_BLOCK format. In this case, the database applies OLTP compression to the blocks, which reside in a Hybrid Columnar Compression segment.

Oracle Database Licensing Information User Manual to learn about licensing requirements for Hybrid Columnar Compression

Oracle Database Administrator’s Guide to learn how to use Hybrid Columnar Compression

Oracle Database SQL Language Reference for CREATE TABLE syntax and semantics

DML and Hybrid Columnar Compression

Hybrid Columnar Compression has implications for row locking in different types of DML operations.

Direct Path Loads and Conventional Inserts

When loading data into a table that uses Hybrid Columnar Compression, you can use either conventional inserts or direct path loads. Direct path loads lock the entire table, which reduces concurrency.

Oracle Database 12c Release 2 (12.2) adds support for conventional array inserts into the Hybrid Columnar Compression format. The advantages of conventional array inserts are:

Inserted rows use row-level locks, which increases concurrency.

Automatic Data Optimization (ADO) and Heat Map support Hybrid Columnar Compression for row-level policies. Thus, the database can use Hybrid Columnar Compression for eligible blocks even when DML activity occurs on other parts of the segment.

When the application uses conventional array inserts, Oracle Database stores the rows in compression units when the following conditions are met:

The table is stored in an ASSM tablespace.

The compatibility level is 12.2.0.1 or later.

The table definition satisfies the existing Hybrid Columnar Compression table constraints, including no columns of type LONG , and no row dependencies.

Conventional inserts generate redo and undo. Thus, compression units created by conventional DML statement are rolled back or committed along with the DML. The database automatically performs index maintenance, just as for rows that are stored in conventional data blocks.

By default, the database locks all rows in the compression unit if an update or delete is applied to any row in the unit. To avoid this issue, you can choose to enable row-level locking for a table. In this case, the database only locks rows that are affected by the update or delete operation.

Oracle Database Administrator’s Guide to learn how to perform conventional inserts

Overview of Table Clusters

A table cluster is a group of tables that share common columns and store related data in the same blocks.

When tables are clustered, a single data block can contain rows from multiple tables. For example, a block can store rows from both the employees and departments tables rather than from only a single table.

The cluster key is the column or columns that the clustered tables have in common. For example, the employees and departments tables share the department_id column. You specify the cluster key when creating the table cluster and when creating every table added to the table cluster.

The cluster key value is the value of the cluster key columns for a particular set of rows. All data that contains the same cluster key value, such as department_id=20 , is physically stored together. Each cluster key value is stored only once in the cluster and the cluster index, no matter how many rows of different tables contain the value.

For an analogy, suppose an HR manager has two book cases: one with boxes of employee folders and the other with boxes of department folders. Users often ask for the folders for all employees in a particular department. To make retrieval easier, the manager rearranges all the boxes in a single book case. She divides the boxes by department ID. Thus, all folders for employees in department 20 and the folder for department 20 itself are in one box the folders for employees in department 100 and the folder for department 100 are in another box, and so on.

Consider clustering tables when they are primarily queried (but not modified) and records from the tables are frequently queried together or joined. Because table clusters store related rows of different tables in the same data blocks, properly used table clusters offer the following benefits over nonclustered tables:

Disk I/O is reduced for joins of clustered tables.

Access time improves for joins of clustered tables.

Less storage is required to store related table and index data because the cluster key value is not stored repeatedly for each row.

Typically, clustering tables is not appropriate in the following situations:

The tables are frequently updated.

The tables frequently require a full table scan .

The tables require truncating.

Oracle Database SQL Tuning Guide for guidelines on when to use table clusters

Overview of Indexed Clusters

An index cluster is a table cluster that uses an index to locate data. The cluster index is a B-tree index on the cluster key. A cluster index must be created before any rows can be inserted into clustered tables.

Example 2-6 Creating a Table Cluster and Associated Index

Assume that you create the cluster employees_departments_cluster with the cluster key department_id , as shown in the following example:

Because the HASHKEYS clause is not specified, employees_departments_cluster is an indexed cluster. The preceding example creates an index named idx_emp_dept_cluster on the cluster key department_id .

Example 2-7 Creating Tables in an Indexed Cluster

You create the employees and departments tables in the cluster, specifying the department_id column as the cluster key, as follows (the ellipses mark the place where the column specification goes):

Assume that you add rows to the employees and departments tables. The database physically stores all rows for each department from the employees and departments tables in the same data blocks. The database stores the rows in a heap and locates them with the index.

Figure 2-5 shows the employees_departments_cluster table cluster, which contains employees and departments . The database stores rows for employees in department 20 together, department 110 together, and so on. If the tables are not clustered, then the database does not ensure that the related rows are stored together.

Figure 2-5 Clustered Table Data

The B-tree cluster index associates the cluster key value with the database block address (DBA) of the block containing the data. For example, the index entry for key 20 shows the address of the block that contains data for employees in department 20:

The cluster index is separately managed, just like an index on a nonclustered table, and can exist in a separate tablespace from the table cluster.

Oracle Database Administrator’s Guide to learn how to create and manage indexed clusters

Oracle Database SQL Language Reference for CREATE CLUSTER syntax and semantics

Overview of Hash Clusters

A hash cluster is like an indexed cluster, except the index key is replaced with a hash function . No separate cluster index exists. In a hash cluster, the data is the index.

With an indexed table or indexed cluster, Oracle Database locates table rows using key values stored in a separate index. To find or store a row in an indexed table or table cluster, the database must perform at least two I/Os:

One or more I/Os to find or store the key value in the index

Another I/O to read or write the row in the table or table cluster

To find or store a row in a hash cluster, Oracle Database applies the hash function to the cluster key value of the row. The resulting hash value corresponds to a data block in the cluster, which the database reads or writes on behalf of the issued statement.

Hashing is an optional way of storing table data to improve the performance of data retrieval. Hash clusters may be beneficial when the following conditions are met:

A table is queried much more often than modified.

The hash key column is queried frequently with equality conditions, for example, WHERE department_id=20 . For such queries, the cluster key value is hashed. The hash key value points directly to the disk area that stores the rows.

You can reasonably guess the number of hash keys and the size of the data stored with each key value.

Hash Cluster Creation

To create a hash cluster, you use the same CREATE CLUSTER statement as for an indexed cluster, with the addition of a hash key. The number of hash values for the cluster depends on the hash key.

The cluster key, like the key of an indexed cluster, is a single column or composite key shared by the tables in the cluster. A hash key value is an actual or possible value inserted into the cluster key column. For example, if the cluster key is department_id , then hash key values could be 10, 20, 30, and so on.

Oracle Database uses a hash function that accepts an infinite number of hash key values as input and sorts them into a finite number of buckets. Each bucket has a unique numeric ID known as a hash value . Each hash value maps to the database block address for the block that stores the rows corresponding to the hash key value (department 10, 20, 30, and so on).

In the following example, the number of departments that are likely to exist is 100, so HASHKEYS is set to 100 :

After you create employees_departments_cluster , you can create the employees and departments tables in the cluster. You can then load data into the hash cluster just as in the indexed cluster.

Oracle Database Administrator’s Guide to learn how to create and manage hash clusters

Hash Cluster Queries

In queries of a hash cluster, the database determines how to hash the key values input by the user.

For example, users frequently execute queries such as the following, entering different department ID numbers for p_id :

If a user queries employees in department_id =20 , then the database might hash this value to bucket 77. If a user queries employees in department_id = 10 , then the database might hash this value to bucket 15. The database uses the internally generated hash value to locate the block that contains the employee rows for the requested department.

The following illustration depicts a hash cluster segment as a horizontal row of blocks. As shown in the graphic, a query can retrieve data in a single I/O.

Figure 2-6 Retrieving Data from a Hash Cluster

A limitation of hash clusters is the unavailability of range scans on nonindexed cluster keys. Assume no separate index exists for the hash cluster created in Hash Cluster Creation. A query for departments with IDs between 20 and 100 cannot use the hashing algorithm because it cannot hash every possible value between 20 and 100. Because no index exists, the database must perform a full scan.

Hash Cluster Variations

A single-table hash cluster is an optimized version of a hash cluster that supports only one table at a time. A one-to-one mapping exists between hash keys and rows.

A single-table hash cluster can be beneficial when users require rapid access to a table by primary key. For example, users often look up an employee record in the employees table by employee_id .

A sorted hash cluster stores the rows corresponding to each value of the hash function in such a way that the database can efficiently return them in sorted order. The database performs the optimized sort internally. For applications that always consume data in sorted order, this technique can mean faster retrieval of data. For example, an application might always sort on the order_date column of the orders table.

Oracle Database Administrator’s Guide to learn how to create single-table and sorted hash clusters

Hash Cluster Storage

Oracle Database allocates space for a hash cluster differently from an indexed cluster.

In the example in Hash Cluster Creation, HASHKEYS specifies the number of departments likely to exist, whereas SIZE specifies the size of the data associated with each department. The database computes a storage space value based on the following formula:

Thus, if the block size is 4096 bytes in the example shown in Hash Cluster Creation, then the database allocates at least 200 blocks to the hash cluster.

Oracle Database does not limit the number of hash key values that you can insert into the cluster. For example, even though HASHKEYS is 100 , nothing prevents you from inserting 200 unique departments in the departments table. However, the efficiency of the hash cluster retrieval diminishes when the number of hash values exceeds the number of hash keys.

To illustrate the retrieval issues, assume that block 100 in Figure 2-6 is completely full with rows for department 20. A user inserts a new department with department_id 43 into the departments table. The number of departments exceeds the HASHKEYS value, so the database hashes department_id 43 to hash value 77, which is the same hash value used for department_id 20. Hashing multiple input values to the same output value is called a hash collision .

When users insert rows into the cluster for department 43, the database cannot store these rows in block 100, which is full. The database links block 100 to a new overflow block, say block 200, and stores the inserted rows in the new block. Both block 100 and 200 are now eligible to store data for either department. As shown in Figure 2-7, a query of either department 20 or 43 now requires two I/Os to retrieve the data: block 100 and its associated block 200. You can solve this problem by re-creating the cluster with a different HASHKEYS value.

Figure 2-7 Retrieving Data from a Hash Cluster When a Hash Collision Occurs

Oracle Database Administrator’s Guide to learn how to manage space in hash clusters

Overview of Attribute-Clustered Tables

An attribute-clustered table is a heap-organized table that stores data in close proximity on disk based on user-specified clustering directives. The directives specify columns in single or multiple tables.

The directives are as follows:

The CLUSTERING . BY LINEAR ORDER directive orders data in a table according to specified columns.

Consider using BY LINEAR ORDER clustering, which is the default, when queries qualify the prefix of columns specified in the clustering clause. For example, if queries of sh.sales often specify either a customer ID or both customer ID and product ID, then you could cluster data in the table using the linear column order cust_id , prod_id .

The CLUSTERING . BY INTERLEAVED ORDER directive orders data in one or more tables using a special algorithm, similar to a Z-order function, that permits multicolumn I/O reduction.

Consider using BY INTERLEAVED ORDER clustering when queries specify a variety of column combinations. For example, if queries of sh.sales specify different dimensions in different orders, then you can cluster data in the sales table according to columns in these dimensions.

Attribute clustering is only available for direct path INSERT operations. It is ignored for conventional DML.

Esta sección contiene los siguientes temas:

Advantages of Attribute-Clustered Tables

The primary benefit of attribute-clustered tables is I/O reduction, which can significantly reduce the I/O cost and CPU cost of table scans. I/O reduction occurs either with zones or by reducing physical I/O through closer physical proximity on disk for the clustered values.

An attribute-clustered table has the following advantages:

You can cluster fact tables based on dimension columns in star schemas.

In star schemas, most queries qualify dimension tables and not fact tables, so clustering by fact table columns is not effective. Oracle Database supports clustering on columns in dimension tables.

I/O reduction can occur in several different scenarios:

When used with Oracle Exadata Storage Indexes, Oracle In-Memory min/max pruning, or zone maps

In OLTP applications for queries that qualify a prefix and use attribute clustering with linear order

On a subset of the clustering columns for BY INTERLEAVED ORDER clustering

Attribute clustering can improve data compression, and in this way indirectly improve table scan costs.

When the same values are close to each other on disk, the database can more easily compress them.

Oracle Database does not incur the storage and maintenance cost of an index.

Oracle Database Data Warehousing Guide for more advantages of attribute-clustered tables

Join Attribute Clustered Tables

Attribute clustering that is based on joined columns is called join attribute clustering . In contrast with table clusters, join attribute clustered tables do not store data from a group of tables in the same database blocks.

For example, consider an attribute-clustered table, sales , joined with a dimension table, products . The sales table contains only rows from the sales table, but the ordering of the rows is based on the values of columns joined from products table. The appropriate join is executed during data movement, direct path insert, and CREATE TABLE AS SELECT operations. In contrast, if sales and products were in a standard table cluster, the data blocks would contain rows from both tables.

Oracle Database Data Warehousing Guide to learn more about join attribute clustering

I/O Reduction Using Zones

A zone is a set of contiguous data blocks that stores the minimum and maximum values of relevant columns. When a SQL statement contains predicates on columns stored in a zone, the database compares the predicate values to the minimum and maximum stored in the zone to determine which zones to read during SQL execution.

I/O reduction is the ability to skip table or index blocks that do not contain data that the database needs to satisfy the query. This reduction can significantly reduce the I/O and CPU cost of table scans.

Zone Maps

A zone map is an independent access structure that divides data blocks into zones. Oracle Database implements each zone map as a type of materialized view .

Whenever CLUSTERING is specified on a table, the database automatically creates a zone map on the specified clustering columns. The zone map correlates minimum and maximum values of columns with consecutive data blocks in the attribute-clustered table. Attribute-clustered tables use zone maps to perform I/O reduction.

You can create attribute-clustered tables that do not use zone maps. You can also create zone maps without attribute-clustered tables. For example, you can create a zone map on a table whose rows are naturally ordered on a set of columns, such as a stock trade table whose trades are ordered by time. You execute DDL statements to create, drop, and maintain zone maps.

Zone Maps: Analogy

For a loose analogy of zone maps, consider a sales manager who uses a bookcase of pigeonholes, which are analogous to data blocks.

Each pigeonhole has receipts (rows) describing shirts sold to a customer, ordered by ship date. In this analogy, a zone map is like a stack of index cards. Each card corresponds to a "zone" (contiguous range) of pigeonholes, such as pigeonholes 1-10. For each zone, the card lists the minimum and maximum ship dates for the receipts stored in the zone.

When someone wants to know which shirts shipped on a certain date, the manager flips the cards until she comes to the date range that contains the requested date, notes the pigeonhole zone, and then searches only pigeonholes in this zone for the requested receipts. In this way, the manager avoids searching every pigeonhole in the bookcase for the receipts.

Zone Maps: Example

This example illustrates how a zone map can prune data in a query whose predicate contains a constant.

Assume you create the following lineitem table:

The table lineitem contains 4 data blocks with 2 rows per block. Table 2-3 shows the 8 rows of the table.


Create a PostgreSQL Dataset

Before we can start practicing SQL queries, we’ll need a database to query against. In this case, we’ll create a PostgreSQL database from publicly available movie rental data using pgadmin for easier SQL administration and query execution.

Step 1 – Create the database by executing the following query:

Here we can see first hand the CREATE DATABASE command, which is used to create a database with a provided name.

Step 2 – Create the schema by downloading the schema definition and executing it with the query tool options:

Once loaded in pgadmin, we can use the UI to execute the script by clicking on the right-pointing forward arrow or by pressing F5.

Step 3 – Load the data into the database. Download the data using the curl tool, and insert it into the database using the psql tool:

Once loaded, we can use the pgAdmin UI to inspect the dataset tables. Here is an Entity Relationship Diagram (ERD) of the database, showing how each element in the database is related:


Indexing Columns

Typically developers index columns for three major reasons:

  1. To enforce unique values within a column
  2. To improve data access performance
  3. To prevent lock escalation when updating rows of tables that use declarative referential integrity

When a table is created and a PRIMARY KEY is specified an index is automatically created to enforce the primary key constraint. If you specific UNIQUE for a column when creating a column a unique index is also created. To see the indexes that already exist for a given table you can run the following dictionary query.

It is typically good form to index foreign keys, foreign keys are columns in a table that reference another table. In our EMPLOYEES and DEPARTMENTS table example the DEPTNO column in the EMPLOYEE table references the primary key of the DEPARTMENTS table.

We may also determine that the EMPLOYEE table will be frequently searched by the NAME column. To improve the performance searches and to ensure uniqueness we can create a unique index on the EMPLOYEE table NAME column.

Oracle provides many other indexing technologies including function based indexes which can index expressions, such as an upper function, text indexes which can index free form text, bitmapped indexes useful in data warehousing. You can also create indexed organized tables, you can use partition indexes and more. Sometimes it is best to have fewer indexes and take advantage of in memory capabilities. All of these topics are beyond the scope of this basic introduction.


6 respuestas 6

Here are a few ways, each of which operates upon the individual component associations. In the following discussion, recall that when a key name is not a valid symbol we can write, for example, #["col_name"] instead of #col .

We can explicitly construct a new association that includes all of the old columns and adds a new one:

This has the disadvantage that we have to list all of the existing columns. To avoid this, we can use Append :

Should we wish to add multiple computed columns, we can use Join :

By exploiting the fact that <| . |> syntax can be nested:

. we can append columns to the dataset's associations using a shorter form:

2017 Update: It has been observed that the shorter form is not explictly mentioned in the documentation for Association (as of V11.1, see comments 1 and 2 for example). The documentation does mention that lists are "flattened out":

. and that all but the last occurrence of repeated keys are ignored:

The documentation also frequently says that associations can be used in place of lists in many functions. It should come as no surprise that Association itself allows us to use an association in place of a list:

This last expression is the "shorter form" from above.

Notwithstanding that the documentation strongly suggests that the short form is valid, I agree with commentators that it would be better if the documentation explicitly discussed the construction.