CAPTULO 7

Generar un esquema relacional bsico

Cada aplicacin de base de datos se crea sobre un conjunto de objetos de base de datos relacionados que almacenan los datos de la aplicacin y permiten funcionar a la aplicacin. Este captulo introduce los objetos de base de datos de Oracle8i, como las tablas y explica los conceptos lgicos de los objetos de base de datos. En siguientes captulos se tratar el almacenamiento de los datos (parmetros de almacenamiento, particin, etc.). Los temas que se explican en este captulo son:

Esquemas. Tablas. Restricciones de integridad. Vistas. Secuencias. Sinnimos. ndices.

Requisitos esenciales de este captulo

Para practicar los ejercicios de este captulo, es necesario iniciar SQL*Plus y ejecutar la siguiente secuencia de comandos:

ubicacin\8iStarterKit\Sql\chap07.sql

Donde ubicacin es el directorio de archivos donde se expandi el archivo de soporte que acompaa a este libro. Por ejemplo. Despus de iniciar SQL*Plus y conectarse como SCOTT, puede ejecutar la secuencia de comandos SQL de este captulo usando el comando SQL*Plus @, como en el siguiente ejemplo (suponiendo que el archivo chap07.sql est en C:\temp\8iStarterKit\Sql).

SQL> @C:\temp\8iStarterKit\Sql;

Una vez se haya ejecutado correctamente la secuencia de comandos, abandone la sesin SQL*Plus actualmente abierta y sela para realizar los ejercicios de este captulo en el orden en que aparecen.

Esquema

Es ms fcil solucionar la mayora de los problemas de la vida real cuando se est organizado y se tiene un plan bien diseado para conseguir los objetivos. Si esta desorganizado, es ms probable que no tenga claros los objetivos, si es que sabe cules son. Disear un sistema de administracin de informacin que use Oracle no es diferente.

Las bases de datos organizan objetos relacionados dentro de un esquema de base de datos. Por ejemplo, es normal organizar dentro de un solo esquema de bases de datos todas las tablas y los dems objetos de base de datos necesarios para soportar una aplicacin. De esta forma, est claro que el propsito de una cierta tabla o de otro objeto de base de datos es soportar el correspondiente sistema de la aplicacin. La Figura 7.1 muestra la idea de un esquema de aplicacin.

Figura 7.1. Un esquema es una organizacin lgica de objetos de base de datos relacionados.

Esquemas, un concepto completamente lgicoEs importante comprender que los esquemas no organizan fsicamente el almacenamiento de los objetos. Por el contrario, los esquemas organizan lgicamente objetos de base de datos relacionados. En otras palabras, la organizacin lgica de objetos de base de datos dentro de esquemas se realiza simplemente para beneficio de la organizacin y no tiene absolutamente nada que ver con el almacenamiento fsico de objetos de base de datos.

La organizacin lgica que ofrecen los esquemas puede tener ventajas prcticas. Por ejemplo, considere una base de datos de Oracle con dos esquemas, S1 Y S2. Cada esquema puede tener una tabla llamada T1. Incluso aunque las dos tablas compartan el mismo nombre, se pueden identificar unvocamente porque estn dentro de esquemas de base diferentes. Usando la notacin estndar de puntos, los nombres completos para las diferentes tablas serian S1.T1 y S2.T1.

Si el concepto de organizacin lgica, en contraposicin con la organizacin fsica, le confunde, considere la forma en que los sistemas operativos organizan los archivos en el disco. La representacin de las carpetas y de los archivos en una utilidad de administracin de archivos grafica, como el Explorador de Microsoft Windows, no se corresponde necesariamente con la ubicacin fsica de las carpetas y de los archivos en una unidad de disco particular. Las carpetas de archivos representan la organizacin lgica de los archivos del sistema operativo. El sistema operativo subyacente decide donde almacenar fsicamente los bloques de cada archivo del sistema, independiente de la organizacin lgica de las carpetas que los contienen.

Los captulos de siguientes de este libro explican mas sobre como Oracle puede organizar fsicamente el almacenamiento de objetos de base de datos usando estructuras de almacenamiento fsicas.

Correlacin de esquemas y cuentas de acceso de usuariosCon Oracle, el concepto de un esquema de base de datos esta ligado directamente al concepto de un usuario de base de datos. Es decir, un esquema de una base de datos de Oracle se corresponde unvocamente con una cuenta de usuario de forma que un usuario y el esquema asociado tienen el mismo nombre. El resultado es, que las personas que trabajan con Oracle, a menudo confunden la distincin entre usuarios y esquemas, diciendo cosas como las tablas EMP y DEPT pertenecen al usuario SCOTT en vez de decir el esquema SCOTT contiene las tablas EMP y DEPT. Aunque estas dos sentencias son ms o menos equivalentes, comprenda que puede haber una distincin clara entre usuarios y esquemas con implementaciones de base de datos relacionales diferentes de la de Oracle. Por lo tanto, mientras que la separacin entre usuarios y esquemas puede parecer trivial para Oracle, la distincin puede ser muy importante si piensa trabajar con otros sistemas de base de datos.

NOTA: Las secuencias de comandos que se ejecutaron para poder realizar los ejercicios prcticos de este captulo y los captulos anteriores crean nuevos usuarios/esquemas de base de datos (practice03, practice04, etc) que contienen conjuntos similares de tablas y otros objetos de base de datos (PARTS, CUSTOMERS, etc.)

Tablas de base de datos

Las tablas son la estructura de datos bsica en cualquier base de datos relacional. Una tabla no es nada ms que una coleccin organizada de registros, o filas, todas ellas con los mismos atributos, o columnas. La Figura 7.2 muestra una tabla CUSTOMERS tpica de una base de datos relacional.

Figura 7.2. Una tabla es un conjunto de registros con los mismos atributos.

Cada registro de cliente en una tabla CUSTOMERS de ejemplo tiene los mismos atributos, incluyendo un ID, un nombre de organizacin, un apellido, un nombre, etc.Cuando se crean tablas, las dos cosas principales que debe considerar son las siguientes: Las columnas de la tabla, que describen la estructura de la tabla. Las restricciones de integridad de la tabla, que describen los datos que son validos dentro de la tablaLas siguientes secciones explican ms sobre columnas y restricciones de integridad.

Columnas y tipos de datosCuando se crea una tabla para una base de datos de Oracle, se establece la estructura de la tabla mediante la identificacin de las columnas que describen los atributos de la tabla. Adems, todas las columnas de una tabla tienen un tipo de datos, que describe el tipo bsico de datos que aceptara esa columna, igual que cuando se declara, el tipo de datos de una variable en un programa PL/SQL o Java. Por ejemplo, la columna ID de la tabla CUSTOMERS usa el tipo de datos bsico de Oracle NUMBER por que la columna almacena nmeros de ID.

Oracle soporta muchos tipos de datos fundamentales que se pueden usar a la hora de crear una tabla y sus columnas de una base de datos relacional. La Tabla 7.1 y las siguientes secciones describen los tipos de datos de Oracle que ms se utilizan.

Tabla 7.1. Tipos de datos de Oracle que ms se utilizan

TIPO DE DATOSDESCRIPCION

CHAR(tamao)Almacena cadenas de caracteres de tamao fijo, hasta 2,000 bytes.

VARCHAR(tamao)Almacena cadenas de caracteres de tamao fijo, hasta 2,000 bytes.

NUMBER(precisin, escala)Almacena cualquier tipo de nmero.

DATEAlmacena fechas y horas.

CLOBAlmacena objetos grandes de caracteres de un nico byte (CLOB, character large objects) hasta 4 gigabytes.

BLOBAlmacena objetos grandes binarios (BLOB, binary large objects) hasta 4 gigabytes.

CHAR y VARCHAR2: tipos de datos de caracteres de OracleCHAR y VARCHAR2 de Oracle son los tipos de datos que ms se usan para columnas que almacenan cadenas de caracteres. El tipo de datos CHAR de Oracle es adecuado para columnas que almacenan cadenas de caracteres de longitud fija, como los cdigos de estado de dos letras de Estados Unidos. De igual forma, el tipo de datos VARCHAR2 de Oracle es til para las columnas que almacenan cadenas de caracteres de longitud variable. Las principales diferencias entre estos tipos de datos de caracteres tienen que ver con la forma en que Oracle almacena las cadenas que son ms cortas que la longitud mxima de una columna.

Cuando una cadena de una columna CHAR es menor que el tamao de las columna, Oracle rellena (anexa) el final de la cadena con espacios en blanco para crear una cadena que coincida con el tamao de la columna. Cuando una cadena de una columna VARCHAR2 es menor que el tamao mximo de la columna, Oracle almacena solo la cadena y no anexa blancos a la cadena.

Es decir, cuando las cadenas de una columna tienen una longitud variable, Oracle puede almacenarlas de forma ms eficaz en una columna VARCHAR2 que es una columna CHAR. Oracle tambin usa diferentes tcnicas para comparar entre s cadenas CHAR y VARCHAR2, con el fin que las expresiones de comparacin se evalen como se espera.

NUMBER: tipo de datos numrico de OraclePara declarar columnas que acepten nmeros se puede usar el tipo de datos NUMBER de Oracle. En vez de tener varios tipos de datos numricos, el tipo de datos NUMBER de Oracle soporta el almacenamiento de todos los tipos de nmeros, incluyendo enteros, nmeros en coma flotante, nmeros reales, etc. Puede limitar el dominio de nmeros validos en una columna especificando una precisin y una escala para una columna NUMBER.

DATE: tipo de datos relacionado con el tiempo de OracleCuando se declara una columna de la tabla con el tipo de datos DATE, la columna puede almacenar tipo de informacin relacionada con la fecha, incluyendo fechas y horas asociadas.

CLOB, BLOB y ms: tipos de datos multimedia de OracleComo las bases de datos son reas de almacenamiento seguras, rpidas y aseguradas para los datos, se emplean como almacenes de datos para aplicaciones multimedia. Para soportar estas aplicaciones ricas en contenido, Oracle8i soporta diferentes tipos de datos de objeto grande (LOB, large object) que pueden almacenar la informacin sin estructurar, como documentos de texto, imgenes estticas, video, sonido y muchos ms.

Una columna CLOB almacena objetos carcter, como documentos. Una columna BLOB almacena objetos binarios grandes, como grficos, secuencias de video o archivos de sonido. Una columna BFILE almacena punteros de archivo a LOB administrados por sistemas de archivos externos a la base de datos. Por ejemplo, una columna BFILE puede ser una lista de referencias de nombre de archivo para fotos almacenadas en un CD-ROM.

Comparacin de LOB con tipos de datos antiguos de objetos grandes de OraclePara mantener la compatibilidad hacia atrs, Oracle8i sigue soportando los tipos de datos de Oracle ms antiguos diseados para objetos grandes, como LONG y LONG RAW. Sin embargo, los tipos de datos LOB ms recientes de Oracle8i tienen varias ventajas sobre los tipos de datos grandes ms antiguos de Oracle.

Una tabla puede tener varias columnas CLOB, BLOB y BFILE. Por el contrario, una tabla solo puede tener una columna LONG o LONG RAW.

Una tabla solo almacena localizadores (punteros) para los LOB de una columna, en lugar de los propios objetos grandes. Por el contrario, una tabla almacena datos para una columna LONG dentro de la propia tabla.

Una columna LOB puede tener caractersticas de almacenamiento independientes de las que tiene la tabla que lo engloba, facilitando la asignacin de los grandes requisitos de disco que suelen ir asociados con los LOB. Por ejemplo, es posible separar el almacenamiento de los datos de la tabla principal y los LOB relacionados en diferentes ubicaciones fsicas (por ejemplo, unidades de disco). Por el contrario, una tabla almacena fsicamente los datos para una columna LONG en la misma rea de almacenamiento que contiene todos los dems datos de la tabla.

Las aplicaciones pueden acceder eficazmente y manipular partes de un LOB. Por el contrario, las aplicaciones deben acceder a todo un campo LONG como si fuera una pieza de datos atmica (invisible).

Antes de migrar o disear nuevas aplicaciones multimedia para Oracle, considere las ventajas de los tipos de datos LOB ms recientes de Oracle en contraposicin con los tipos de datos de objetos grandes ms antiguos.

Tipos de datos de caracteres National Language Support (N.T.: Soporte Nacional de Idioma) de OracleLas caractersticas de National Language Support (NLS) de Oracle permiten a las bases de datos almacenar y manipular datos de caracteres en muchos idiomas. Algunos idiomas tienen conjuntos de caracteres que requieren varios bytes para cada carcter los tipos de datos especiales de Oracle NCHAR, NVARCHAR2 y NCLOB son tipos de datos equivalentes a los tipos de datos CHAR, VARCHAR2 y CLOB, respectivamente.

Tipos de datos ANSI y otrosOracle 8i tambin soporta la especificacin de los tipos de datos de Oracle que usan otros tipos de datos estndar. Por ejemplo, la Tabla 7.2 lista los tipos de datos estndar de la ANSI/ISO (American National Standards Institute/International Organization for Standarization) que soporta Oracle.

Tabla 7.2. Oracle soporta la especificacin de tipos de datos de Oracle utilizando los tipos de datos estndar ANSI/ISO.

Este tipo de datos ANSI/ISO se convierte a este tipo de datos de Oracle

CHARACTERCHAR

CHARACTER

CHARACTER VARYINGVARCHAR2

CHAR VARYING

NATIONAL CHARACTERNCHAR

NATIONAL CHAR

NCHARNVARCHAR2

NATIONAL CHARACTER VARYING

NATIONAL CHAR VARYING

NCHAR VARYING

NUMERICNUMBER

DECIMAL

INTEGER

INTEGER

SMALLINT

FLOAT

DOUBLE PRECISION

REAL

Valores predeterminados de columnaCuando se declara una columna para una tabla, tambin se declara su correspondiente valor de la columna predeterminado. Oracle usa el valor predeterminado de una columna cuando una aplicacin inserta una nueva fila en la tabla, pero omite un valor para la columna. Por ejemplo, puede indicar que el valor predeterminado para la columna ORDERDATE de la tabla ORDERS ser la hora actual del sistema cuando una aplicacin cree un nuevo pedido.

NOTA: A no ser que se indique lo contrario, el valor predeterminado para una columna es nulo (ausencia de valor).

Crear y administrar tablasAhora que se sabe que la estructura de una tabla la definen sus columnas y que cada columna de una tabla tiene un tipo de datos, es hora de aprender los fundamentos de cmo crear y administrar la estructura de las tablas de una base de datos Oracle. Los siguientes ejercicios prcticos introducen los comandos CREATE TABLE y ALTER TABLE de SQL.

EJERCICIO 7.1: Crear una tablaUna tabla se crea usando el comando CREATE TABLE de SQL. Para el propsito de este simple ejercicio, la sintaxis bsica para crear una tabla de una base de datos relacional con el comando CREATE TABLE es la siguiente:

CREATE TABLE |esquema.) tabla( tipo de datos de la columna [DEFAULT expresin][, tipo de datos de la columna [DEFAULT expresin] ][ otras columnas ])

Usando la sesin SQL*Plus actual, introduzca el siguiente comando para crear la tabla ya conocida PARTS en el esquema practico de la leccin.

CREATE TABLE parts ( id INTEGER, description VARCHAR2(250), unitprice NUMBER(10, 2), onhand INTEGER, reorder INTEGER);

El esquema actual (practice07) tiene ahora una nueva tabla, PARTS, que puede consultar, insertar registros en ella, etc. Tenga en cuenta que la tabla PARTS tiene cinco columnas.

La sentencia declara las columnas ID, ONHAND y REORDER con el tipo de datos INTEGER de ANSI/ISO, que Oracle convierte automticamente al tipo de datos NUMER de Oracle con 38 dgitos de precisin. La sentencia declara la columna DESCRIPTION con el tipo de datos VARCHAR2 de Oracle para aceptar cadenas de longitud variable de hasta 250 bytes de longitud. La sentencia declara la columna UNITPRICE con el tipo de datos NUMBER de Oracle para almacenar nmeros de hasta diez dgitos de precisin y para redondear nmero de dos dgitos a partir del punto decimal

Antes de continuar, cree la conocida tabla CUSTOMERS usando la siguiente sentencia CREATE TABLE:

CREATE TABLE customers ( id INTEGER, lastname VARCHAR2(100), firstname VARCHAR2(50), companyname VARCHAR2(100), street VARCHAR2(100), city VARCHAR2(100), state VARCHAR2(50), zipcode VARCHAR2(10), phone VARCHAR2(30), fax VARCHAR2(30), email VARCHAR2(100));

Cuando se estn diseando las tablas de un esquema de base de datos, a veces puede ser difcil elegir el tipo de datos correcto para una columna. Por ejemplo, considere la columna ZIPCODE de la tabla CUSTOMERS del ejemplo anterior, declarado con el tipo de datos NUMBER. Considere que ocurrira cuando inserte un registro de cliente con el ZIPCODE 01003, Oracle guardar este nmero como 1003, ciertamente no es lo que se quiere. Adems, considere que ocurrir si inserta un registro de cliente con un cdigo postal y una extensin como 91222-0299, Oracle evaluara esta expresin numrica y almacenara el numero resultante 90923. Estos dos sencillos ejemplos muestran que la seleccin del tipo de datos de una columna es ciertamente una decisin importante, y no se debe tomar a la ligera. En el siguiente ejercicio prctico, aprender como cambiar el tipo de datos de la columna ZIPCODE para almacenar los cdigos postales correctamente.

Para completar este ejercicio, cree la tabla SALESREPS con la siguiente sentencia CREATE TABLE:

CREATE TABLE salesreps ( id INTEGER, lastname VARCHAR2(100), firstname VARCHAR2(50), commission NUMBER(38));

NOTA: Los ejemplos de esta seccin introducen los fundamentos del comando CREATE TABLE. Los siguientes ejercicios de este y otros captulos explican clausulas y parmetros ms avanzados del comando CREATE TABLE.

EJERCICIO 7.2: Modificar y agregar columnas a una tabla

Despus de crear una tabla, se puede modificar su estructura usando el comando ALTER TABLE de SQL. Por ejemplo, puede que se quiera cambiar el tipo de datos de una columna, cambiar el valor de la columna de la columna predeterminada o agregar una columna completamente nueva. En este ejercicio, la sintaxis bsica del comando ALTER TABLE para agregar o modificar una columna en una tabla de una base de datos relacional es la siguiente:

ALTER TABLE [esquema.] tabla[ADD tipo de datos de la columna [DEFAULT expresin]][MODIFY columna [tipo_de_datos] [DEFAULT expresin]]

Por ejemplo, introduzca la siguiente sentencia ALTER TABLE, que modifica el tipo de datos de la columna ZIPCODE en la tabla CUSTOMERS que cre en el Ejercicio 7.1.

ALTER TABLE customers MODIFY zipcode VARCHAR2(50);

NOTA: Puede cambiar el tipo de datos de una columna, la precisin o la escala de una columna NUMBER, o el tamao de una columna CHAR o VARCHAR2 solo cuando la tabla no contenga ninguna fila, o cuando la columna destino sea nula para todos los registros de la tabla.

Suponga que se da cuenta de que la tabla CUSTOMERS debe poder registrar el representante de ventas de cada cliente. Introduzca la siguiente sentencia ALTER TABLE, que agrega la columna S_ID para registrar el ID del representante de ventas de un cliente.

ALTER TABLE customers ADD s_id INTEGER;

NOTA: Los ejemplos de esta seccin explican los fundamentos del comando ALTER TABLE. Los siguientes ejercicios de este captulo muestran clausulas y parmetros ms avanzados del comando ALTER TABLE.

Integridad de datos y restricciones de integridadLa integridad de los datos es un principio fundamental del modelo de la base de datos relacional. Decir que una base de datos tiene integridad es otra forma de decir que la base de datos contiene solo informacin exacta y aceptable. Por razones obvias, la integridad de los datos es un atributo deseable para una base de datos.

En menor medida, un tipo de datos de una columna establece un dominio ms limitado de valores aceptables para la columna, este limita el tipo de datos que puede almacenar la columna. Por ejemplo, una columna DATE puede contener fechas y horas validas, pero no nmeros o cadenas carcter. Aunque los tipos de datos de columna simples son tiles para hacer cumplir un nivel bsico de integridad de datos inherentes que debe conservar un sistema de administracin de base de datos relacional (RDBMS). Las siguientes secciones describen estas reglas de integridad comunes y las cuestiones relacionadas con ellas.

Integridad de dominio, nulos y dominios complejosLa integridad de dominio define el dominio de valores aceptables para una columna. Por ejemplo, puede tener una regla que diga que un registro de clientes no es vlido a menos que el cdigo abreviado del estado del cliente sea uno de los 50 o ms cdigos de estado de los Estados Unidos.

Adems de usar los tipos de datos de las columnas, Oracle soporta dos tipos de restricciones de integridad que permiten limitar todava ms el domino de una columna:

Una columna puede tener una restriccin no nulo para eliminar la posibilidad de valores nulos (ausencia de valores) en la columna. Puede usar una restriccin de comprobacin para declarar una regla compleja de integridad de dominio como parte de una tabla. Una restriccin de comprobacin suele contener una lista explicita de los valores aceptables para una columna. Por ejemplo, M y F en una columna que contenga informacin del sexo; AL, AK, ... WY en una columna que contenga los cdigos de estado Norteamericanos; etc.

Integridad de entidad, claves principales y claves alternativasLa integridad de entidad asegura que cada fila de una tabla es nica. Como resultado, la integridad de entidad elimina la posibilidad de duplicar registros en la tabla y posibilita identificar unvocamente cada tabla.

La clave principal de una tabla asegura su integridad de entidad. Una clave principal es una columna que identifica unvocamente cada fila de una tabla. Normalmente, las tablas de una base de datos relacional utilizan columnas de tipo ID como claves principales. Por ejemplo, una tabla de clientes puede incluir una columna ID para identificar unvocamente los registros cliente dentro de ella. De esta forma, incluso si dos clientes, por ejemplo John Smith y su hijo John Smith (Jr.), tienen el mismo nombre, la misma direccin, el mismo nmero de telfono, etc., tienen distintos nmeros de ID que los diferencian.

La clave principal de una tabla es a veces una clave compuesta, es decir, est compuesta por ms de una columna. Por ejemplo, la clave principal de una tabla tpica de elementos de un sistema de entrada de pedidos puede tener una clave principal compuesta que consista en las columnas ORDER_ID y ITEM_ID. En este ejemplo de clave principal compuesta, muchos registros de elementos pueden tener el mismo ID de elemento (1, 2, 3, ), pero dos registros de elementos no pueden tener la misma combinacin de ID de pedido e ID de elemento (ID pedido 1, ID de elemento 1, 2, 3, ; ID de pedido 2, ID de elemento 1, 2, 3, ; etc.).

Opcionalmente, puede que una tabla necesite niveles secundarios de integridad de entidad. Las claves alternativas son columnas o conjuntos de columnas que no contienen valores duplicados dentro de ellas. Por ejemplo, la columna EMAIL de una tabla de empleados puede convertirse en una clave alternativa para garantizar que todos los empleados tienen una direccin de correo electrnico nica.

Integridad referencial, claves externas y acciones referencialesLa integridad referencial, a veces llamada integridad de relaciones, establece las relaciones entre varias columnas y tablas de una base de datos. La integridad referencial asegura que cada valor de columna en una clave externa de una tabla hija (o detalle) coincide con un valor en la clave principal o en una clave alternativa de la tabla padre (o maestra) relacionada. Por ejemplo, una fila de la tabla CUSTOMERS (hija) no es vlida a menos que el campo ID de clientes se refiera a un ID de representante de ventas valido en la tabla SALESREPS (padre). Cuanto las tablas padre e hija son la misma, esto se llama integridad auto referencial. La figura 7.3 muestra la terminologa y los conceptos relacionados con la integridad referencial.

Figura 7.3. La integridad referencial describe las relaciones entre las columnas y tablas de una base de datos relacional.

Acciones referenciadasLa integridad referencial asegura que cada valor de una clave externa siempre tiene un valor clave padre que coincide. Para garantizar la integridad referencial, un RFBMS desde tambin ser capaz de direccionar las operaciones de bases de datos que manipulan clavas padre. Por ejemplo, cuando un usuario elimina en pedido de ventas, qu sucede con los elementos dependientes de dicho pedido? Las acciones referenciales describen que hacer en los casos en que una aplicacin actualice o elimine una clave padre que tiene registros hijos dependientes.

El modelo de base de datos relacional describe varias acciones referenciales:

Restringir actualizacin / eliminacin: El RDBMS no permite que una aplicacin actualice una clave padre o elimine una fila padre que tenga uno o ms registros hijos dependientes. Por ejemplo, no puede eliminar un pedido de ventas de la tabla ORDERS si este tiene asociados elementos en la tabla ITEMS. Eliminacin en cascada: Cuando una aplicacin elimina una fila de la tabla padre, el RDBMS realiza una eliminacin en cascada, eliminado todo los registros de una tabla hija. Por ejemplo, cuando se elimina un pedido de la tabla ORDERS, el RDBMS quita automticamente todos los elementos correspondientes de la tabla ITEMS. Actualizacin en cascada: Cuando una aplicacin actualiza una clave padre, el RDBMS actualiza en cascada las claves externas dependientes. Por ejemplo, cuando se cambia el ID de un pedido en la tabla ORDERS, el RDBMS actualizar automticamente el ID del pedido de todos los registros del elemento correspondiente en la taba ITEMS. Esta accin referencial rara vez es til, porque las aplicaciones no suelen permitir las actualizaciones de valores clave. Establecer la actualizacin / eliminacin de nulos: Cuando una aplicacin actualiza o elimina una clave padre, todas las claves dependientes se establecen a nulo. Establecer la actualizacin / eliminacin predeterminada: Cuando una aplicacin actualiza o elimina una clave padre, todas las claves dependientes reciben un valor significativo predeterminado.

Por defecto, Oracle8i impone las acciones referenciales Restringir actualizacin/eliminacin para todas las restricciones de integridad referencial. Opcionalmente, Oracle puede realizar la accin referencial Eliminacin en cascada o Establecer la eliminacin de nulos para una restriccin de identidad referencial.

Cundo impone Oracle las reglas de restricciones de integridad?Oracle puede imponer una restriccin de integridad en dos ocasiones diferentes:

Por defecto, Oracle impone todas las restricciones de integridad inmediatamente despus de que una aplicacin ejecute una sentencia SQL para insertar, actualizar o eliminar filas en una tabla. Cuando una sentencia provoca una violacin de la integridad de los datos, Oracle restaura automticamente los efectos de la sentencia. Opcionalmente, Oracle puede retrasar el cumplimiento de una restriccin de integridad aplazable hasta justo antes de la confirmacin de la transaccin. Cuando se confirma una transaccin y la transaccin ha modificado los datos de la tabla de forma que estos cambios no cumplen todas las restricciones de integridad, Oracle restaura automticamente toda la transaccin (es decir, los efectos de todas las sentencias de la transaccin).

Las aplicaciones de la base de datos tpicas deberan comprobar inmediatamente la integridad de los datos a medida que se ejecuta cada sentencia SQL. Sin embargo, ciertas aplicaciones, como cargas de datos grandes, puede que necesiten actualizar muchas tablas y violar, temporalmente, reglas de integridad hasta casi el final de la transaccin.

Crear y administrar restricciones de integridadSe pueden definir restricciones de integridad para una tabla cuando se crea una tabla, o posteriormente modificando la tabla. Los siguientes ejercicios prcticos muestran cmo usar los comandos CREATE TABLE y ALTER TABLE de SQL para crear restricciones de integridad referencial, nicas, de clave principal y no nulas.

EJERCICIO 7.3: Crear una tabla con restricciones de integridad

Una forma de declarar restricciones de integridad para una tabla es hacerlo cuando se crea la tabla. Para crear una tabla con restricciones de integridad, se usa la clusula CONSTRAINT del comando CREATE TABLE. El siguiente listado es un listado parcial de las opciones disponibles para la clusula CONSTRAINT del comando CREATE TABLE.

CREATE TABLE [esquema.] tabla (( tipo de datos de columna [DEFAULT expresin] [CONSTRAINT resticcin] [ [NOT] NULL | (UNIQUE | PRIMARY KEY) | REFERENCES [esquema.] tabla [(columna)] [ON DELETE CASCADE] | CHECK (condicin) }| [CONSTRAINT restriccin] { (UNIQUE | PRIMARY KEY} (columa [, columna] ) | FOREIGN KEY (columna [, columna] ) REFERENCES [esquema.] tabla [(columna [, columna] ... )] [ON DELETE (CASCADE|SET NULL)] | CHECK (condici n) )[, otras columnas/restricciones o restricciones])

Tenga en cuenta que se puede declarar una restriccin de integridad a la vez que se declara una columna, o puede declarar una restriccin de integridad independiente de la declaracin de una columna especifica. En general, siempre puede elegir la opcin que quiera para crear una restriccin, excepto en las siguientes situaciones:

Para declarar una columna con una restriccin no nula, debe realizarlo como parte de la declaracin de la columna Para declarar una restriccin de integridad de clave principal compuesta, nica o referencial, debe declarar la restriccin de forma independiente de la declaracin de una columna especfica.

Introdzcala siguiente sentencia CREATE TABLE para crear la tabla ya conocida ORDERS con algunas restricciones de integridad.

CREATE TABLE orders ( id INTEGER CONSTRAINT orders_pk PRIMARY KEY, orderdate DATE DEFAULT SYSDATE NOT NULL, shipdate DATE, paiddate DATE, status CHAR(1) DEFAULT F CONSTRAINT status_ck CHECK (status IN (F,B)));

Esta sentencia crea la tabla ORDERS y declara tres restricciones de integridad como parte de las declaraciones de las columnas (consulte las clusulas CONSTRAINT que apareen en negrita arriba).

La columna ID es la clave principal de la tabla ORDERS, todo registro debe tener un ID (no se permiten implcitamente los nulos) que es nico para cada registro. La sentencia nombra la restriccin de clave principal como ORDERS_PK. La sentencia declara la columna ORDERDATE como nula como la sentencia no nombra explcitamente la restriccin no nula, Oracle genera un nombre nico para la restriccin. Posteriormente en este captulo, veremos cmo mostrar informacin sobre los objetos y las restricciones se seguridad del esquema, incluyendo los nombres de restriccin generados. La restriccin de comprobacin STATUS_CK asegura que el valor del campo STATUS es F o B para todos los registros de la tabla ORDERS. Como la sentencia no declara la columna STATUS con una restriccin no nula, la columna STATUS tambin puede contener nulos.

La siguiente seccin ofrece ms ejemplos de restricciones de integridad con el comando ALTER TABLE, incluyendo cmo declarar restricciones de integridad nicas y referenciales.

EJERCICIO 7.4: Agregar una restriccin no nula a una columna existente

Despus de crear una tabla, puede que sea necesario agregar (o eliminar) una restriccin de integridad no nula a (o de) una columna existente. Es posible hacerlo usando la siguiente sintaxis del comando ALTER TABLE:

ALTER TABLE [esquema.] tabla MODIFY columna [NOT] NULL

Por ejemplo, introduzca el siguiente comando para agregar una restriccin de integridad no nula a la columna DESCRIPTION de la tabla PARTS:

ALTER TABLE parts MODIFY description NOT NULL;

NOTA: Para quitar posteriormente la restriccin no nula de la columna STATUS, ser necesario usar la sentencia anterior, pero omitiendo la palabra clave NOT

EJERCICIO 7.5: Agregar restricciones de clave principal nica a una tabla

Tambin se puede declarar una restriccin de integridad despus de crear una tabla usando el comando ALTER TABLE, de la siguiente forma:

ALTER TABLE [esquema.]tablaADD [CONSTRAINT restriccin] { (UNIQUE|PRIMARY KEY) (columna [, columna] ... ) | FOREIGN KEY (columna [, columna] ) REFERENCES [esquema.] tabla [(columna [, columna] ...)] [ON DELETE (CASCADE|SET NULL)] | CHECK (condicin) }

Por ejemplo, las tablas PARTS y CUSTOMERS que se crearon en el Ejercicio 7.1 no tienen claves principales, introduzca los siguientes comandos para agregar restricciones de clave principal para estas tablas.

ALTER TABLE parts ADD CONSTRAINT parts_pk PRIMARY KEY (id);

ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY (id);

Introduzca el siguiente comando para agregar una restriccin nica compuesta a la tabla CUSTOMERS que evite las combinaciones duplicadas LASTNAME/FIRSTNAME. Como la sentencia no nombra explcitamente la restriccin nica, Oracle genera un identificador de sistema nico para la nueva restriccin.

ALTER TABLE customers ADD UNIQUE (lastname, firstname);

EJERCICIO 7.6: Agregar restricciones referenciales a una tabla

Tambin se puede usar la sintaxis del comando ALTER TABLE del ejercicio anterior para agregar una restriccin de integridad referencial a la taba. Por ejemplo, introduzca la siguiente sentencia para agregar una restriccin de integridad referencial a la tabla CUSTOMERS que asegure que el S_ID de cada registro hace referencia a un ID de la tabla SALESREPS.

ALTER TABLE customers ADD CONSTRAINT salesreps_fk FOREIGN KEY (s_id) REFERENCES salesreps (id);

La sentencia anterior debera devolver los siguientes nmeros y mensajes de error:

ORA-02270: no matching unique or primary key for this column-list

Por qu?. Recuerde que cuando se declara una restriccin de integridad referencial, la clave externa debe hacer referencia a una clave principal o a una clave nica e una tabla. Como la tabla SALESREPS no tiene una clave principal, la sentencia anterior devuelve un error. Para remediar esta situacin, agregue primero la clave principal a la tabla SALESREPS y luego vuelva a ejecutar la sentencia anterior para agregar la restriccin de integridad referencial a la tabla CUSTOMERS.

ALTER TABLE salesreps ADD CONSTRAINT salesreps_pk PRIMARY KEY (id);

ALTER TABLE customers ADD CONSTRAINT salesreps_fk FOREIGN KEY (s_id) REFERENCES salesreps (id);

Observe que la especificacin de la restriccin referencial de SALESREPS_FK no especifica una accin referencial para las eliminaciones. Debido a esta omisin, la restriccin de integridad referencial impone la accin referencial de restringir la eliminacin. Un ejercicio posterior de este captulo muestra como declarar una restriccin de integridad referencial con la accin referencial de eliminacin en cascada.

EJERCICIO 7.7: Agregar una columna con restricciones

Cuando se agrega una columna a una tabla, tambin es posible agregar a la vez una restriccin a la tabla, usando la siguiente sintaxis del comando ALTER TABLE:

ALTER TABLE [esquema.]tablaADD ( columna [tipo_de_datos] [DEFAULT expresin] [ [CONSTRAINT restriccin] { NOT NULL | {UNIQUE|PRIMARY KEY} | REFERENCES Tabla [ (columna) ] [ON DELETE|CASCADE|SET NULL)] | CHECK (condicin ) } ] [otra especificacin de restriccin][, otras columnas y otras restricciones ...])

Por ejemplo, cada registro de la tabla ORDERS necesita un campo para hacer un seguimiento del ID del cliente que hace el pedido. Para agregar esta columna, introduzca la siguiente sentencia, que agrega la columna C_ID a la tabla ORDERS, junto con una restriccin de integridad ni nula y referencial.

ALTER TABLE orders ADD c_id INTEGER CONSTRAINT c_id_nn NOT NULL CONSTRAINT customers_fk REFERENCES customers (id);

La combinacin de las restricciones de integridad no nula y referencial de este ejercicio asegura que cada registro de la tabla ORDERS debe tener un C_ID (ID del cliente) que haga referencia a un ID de la tabla CUSTOMERS.

EJERCICIO 7.8: Declarar una restriccin referencial con una accin de eliminar

Es necesaria una tabla ms para completar las especificaciones de la tabla del sencillo esquema de prcticas, introduzca la siguiente sentencia CREATE TABLE, que crea la tabla ITEMS, junto con varias restricciones de integridad (que se resaltan en negrita).

CREATE TABLE items ( o_id INTEGER CONSTRAINT orders_fk REFERENCES orders ON DELETE CASCADE, id INTEGER, p_id INTEGER CONSTRAINT parts_fk REFERENCES parts, quantity INTEGER DEFAULT 1 CONSTRAINT quantity_nn NOT NULL, CONSTRAINT items_pk PRIMARY KEY (o_id, id));

La siguiente lista describe las restricciones de integridad declaradas para la tabla ITEMS.

La restriccin de integridad referencial ORDERS_FK asegura que el campo O_ID de cada registro de la tabla ITEMS hace referencia a un ID en la tabla ORDERS. Esta restriccin de integridad referencial tambin especifica la accin referencial de eliminacin en cascada, siempre que una transaccin elimine un registro de la tabla ORDERDS, Oracle automticamente eliminar en cascada los registros asociados de la tabla ITEMS. La restriccin no nula QUANTITY_NN evita que se introduzca nulos en la columna QUANTITY. La restriccin de integridad referencial PARTS_FK asegura que el campo P_ID de cada registro de la tabla ITEMS hace referencia a un ID de la tabla PARTS. La restriccin de clave principal ITEMS_PK es una clave principal compuesta. Esta restriccin asegura que ninguna de las columnas O_ID o ID contienen nulos, y que la combinacin O_ID/ID de cada registro es diferente de las dems de la tabla ITEMS.

EJERCICIO 7.9: Probar una restriccin de integridad

En este momento, ya se han creado todas las tablas. Las sentencias de este ejercicio le indican que inserte algunas filas en varias tablas para confirmar que las restricciones de integridad creadas en el ejercicio anterior realmente imponen nuestras reglas de negocios. Primero, observe qu sucede cuando se introducen las siguientes sentencias, que insertan tres nuevos representantes de ventas en la tabla SALESREPS.

INSERT INTO salesreps (id, lastname, firstname, commission) VALUES (1,Pratt,Nick,5);

INSERT INTO salesreps (id, lastname, firstname, commission) VALUES (2,Jonah,Suzanne,5);

INSERT INTO salesreps (id, lastname, firstname, commission) VALUES (2,Greenberg,Bara,5);

La primera y la segunda sentencia INSERT deberan ejecutarse sin errores. Sin embargo, cuando intente la tercera sentencia INSERT, Oracle devolver el siguiente nmero y mensaje de error:

ORA-00001: unique constraint (PRACTICE.SALESREPS_PK) violated

La restriccin de clave principal de la tabla SALESREPS prohbe que dos registro tengan el mismo ID. En este ejemplo, la tercera sentencia INSERT intenta insertar un nuevo registro con un ID igual a 2, que ya utiliza otro registro. Si vuelve a escribir la tercera sentencia INSERT con un ID diferente, la fila se insertara sin errores.

INSERT INTO salesreps (id, lastname, firstname, commission) VALUES (3,Greenberg, Bara,5);

Puede confirmar permanentemente la transaccin actual ejecutando una sentencia COMMIT.

COMMIT;

Ahora, vamos a probar una restriccin de integridad referencial y ver qu sucede. Introduzca las siguientes sentencias, que insertan algunos registros en la tabla CUSTOMERS.

INSERT INTO customers ( id, lastname, firstname, companyname, street, city, state, zipcode, phone, fax, email, s_id) VALUES (1,Joy,Harold,McDonald Co., 4458 Stafford St.,Baltimore,MD,21209, 410-983-5789,NULL,harold_joy@mcdonald.com,3);

INSERT INTO customers ( id, lastname, firstname, companyname, street, city, state, zipcode, phone, fax, email, s_id) VALUES (2,Musial,Bil,Car Audio Center, 12 Donna Lane,Reno,NV, 89501,775-859-2121, 775-859-2121,musial@car-audio.net,5);

La primera sentencia INSERT debera ejecutarse sin errores, teniendo en cuenta que se ha ejecutado correctamente la anterior sentencia INSERT de este ejercicio (insertando el registro para el representante de ventas con un ID igual a 3). Sin embargo, cuando intente ejecutar la segunda sentencia INSERT, Oracle devolver el siguiente nmero y mensaje de error.

ORA-02291: integrity constraint (PRACTICE.SALESREPS_FK) violated parent key not found

La restriccin de integridad referencial SALESREPS_FK de la tabla CUSTOMERS no permite un registro cliente con un S_ID que no coincide con un ID de la tabla SALESREPS. En este caso, la sentencia INSERT intenta insertar un registro que hace referencia a un representante de ventas con ID igual a 5, que no existe. Si vuelve a escribir la segunda sentencia INSERT como sigue, debera proceder sin errores:

INSERT INTO customers ( id, lastname, firstname, companyname, street, city, state, zipcode, phone, fax, email, s_id) VALUES (2,Musial, Bill, Car Audio Center, 12 Donna Lane, Reno, NV, 89501, 775-859-2121, 775-859-2121, musial@car-audio.net,1);

COMMIT;

EJERCICIO 7.10: Declarar y usar una restriccin diferida

Todas las restricciones que se han especificado en los Ejercicios 7.3 hasta el 7.8 se imponen inmediatamente conforme se ejecuta cada sentencia SQL. El ejercicio anterior muestra esta imposicin inmediata de las restricciones, cuando se intenta insertar una fila que no tiene un valor de clave principal nica en la tabla PARTS, Oracle impone inmediatamente la restriccin restaurando la sentencia INSERT y devolviendo un error.

Tambin se puede crear una restriccin aplazable, si lo exige la lgica de la aplicacin. Si lo hace, en el inicio de una nueva transaccin, puede indicar a Oracle que retrase el cumplimiento de las restricciones aplazables seleccionadas o no seleccionadas hasta que se confirme la transaccin. Para crear una transaccin aplazable, incluya la palabra clave opcional DEFERRABLE cuando especifique la restriccin.

Para mostrar las restricciones aplazables, vamos a convertir la restriccin de comprobacin STATUS_CK de la tabla ORDERS en una restriccin aplazable. Esto permitir a un representante de ventas retrasar la decisin de si un nuevo pedido debe repetirse por falta de inventario para una pieza en particular que se pide. Primero, tendr que eliminar la restriccin de comprobacin existente, como sigue:

ALTER TABLE orders DROP CONSTRAINT status_ck;

A continuacin, introduzca el siguiente comando para volver a crear la restriccin de comprobacin STATUS_CK como una restriccin aplazable:

ALTER TABLE orders ADD CONSTRAINT status_ck CHECK (status IN (F,B)) DEFERRABLE;

Ahora vamos a probar la restriccin aplazable. Para retrasar el cumplimiento de una restriccin aplazable, inicie una transaccin con el comando SQL SET CONSTRAINTS, que tiene la siguiente sintaxis.

SET CONSTRAINTS [S]{[esquema.]restriccin[,[esquema.]restriccin] ...| ALL }{IMMEDIATE | DEFERRED}

Observe que el comando SET CONSTRAINTS permite establecer explcitamente el cumplimiento de restricciones especificas o de todas las restricciones. Para retrasar el cumplimiento de la restriccin STATUS_CK, inicie la nueva transaccin con la siguiente sentencia:

SET CONSTRAINTS status_CK DEFERRED;

A continuacin introduzca la siguiente sentencia para insertar un registro en la tabla ORDERS que no cumple la condicin de la restriccin de comprobacin STATUS_CK, el cdigo de STATUS para el pedido es U en vez de B o F.

INSERT INTO orders (id, c_id, orderdate, shipdate, paiddate, status) VALUES (1,1,18-JUN-99,18-JUN-99,30-JUN-99,U);

Ahora, confirme la transaccin con una sentencia COMMIT, para ver qu ocurre. Oracle debera devolver los siguientes mensajes de error:

COMMIT;

ORA-02091: transaction rolled backORA-02290: check constraint 8PRACTICE07.STATUS_CK) violated

Cuando confirme la transaccin, Oracle har cumplir la regla de la restriccin aplazable STATUS_CK y avisara que la nueva fila de la tabla ORDERS no cumple con la regla de negocios asociados. Por lo tanto, Oracle restaura todas las sentencias de la transaccin actual.

VistasUna vez definidas las tablas de una base de datos, puede centrarse en otros aspectos relativos a la mejora del uso del esquema de la aplicacin. Puede comenzar definiendo vistas de las tablas del esquema. Una vista es un objeto de base de datos que presenta datos en la tabla Por qu y cmo usar las vistas para presentar los datos de la tabla?

Puede usar una vista simple para mostrar todas las filas y todas las columnas de una tabla, y ocultar el nombre de la tabla subyacente por motivos de seguridad. Por ejemplo, puede crear una vista llamada CUST que muestre todos los registros de cliente de la tabla CUSTOMERS. Puede usar una vista para proteger la seguridad de datos especficos de la tabla mostrando solo un subconjunto de las filas y/o de las columnas de una tabla. Por ejemplo, puede crear una vista llamada CUST_CA que muestre solo las columnas LASTNAME, FIRSTNAME y PONE de la tabla CUSTOMERS para clientes que residen en el estado de California. Puede usar una vista para simplificar la codificacin de aplicaciones. Una visa compleja puede unir los datos de tablas padre e hija relacionadas para hacerlas aparecer como si existiese una tabla diferente en la base de datos. Por ejemplo, puede crear una vista llamada ORDER_ITEMS que une registros relacionados en las tablas ORDERS e ITEMS. Puede usar una vista para mostrar datos derivados que no estn realmente almacenados en una tabla. Por ejemplo, puede crear una vista de la tabla ITEMS con una columna llamada TOTAL que calcula el total de cada registro.

Como se puede ver en esta lista, las vistas proporcionan unos medios flexibles de mostrar los datos de la tabla en una base de datos. De hecho, puede crear una vista de cualquier dato que pueda representar con una peticin SQL. Esto es as porque una vista es realmente slo una peticin que Oracle almacena como un objeto e esquema. Cuando una aplicacin usa una vista para hacer algo, Oracle deriva los datos de la visa basndose en la consulta de definicin de la vista. Por ejemplo, cuando una aplicacin hace una peticin a la vista CUST_CA que se describe en la lista anterior, Oracle procesa la peticin con los datos descritos por la consulta de definicin de la vista.

Creacin de vistasPara crear una vista, use el comando CREATE VIEW de SQL. A continuacin se muestra un listado abreviado de la sintaxis del comando CREATE VIEW:

CREATE [OR REPLACE] VIEW [esquema.] vista AS subconsulta [WTH READ ONLY]

Las siguientes secciones y ejercicios prcticos explican algo ms sobre los tipos especficos de vistas que soporta Oracle8i, y proporcionan ejemplo de uso de varias clusulas, parmetros y opciones del comando CREATE VIEW.

Vistas de slo lecturaUn tipo de vista que Oracle8i soporta es una vista de slo lectura. Como podra esperar, las aplicaciones de base de datos pueden usar una vista de slo lectura para recuperar los datos correspondientes a una tabla, pero no pueden insertar, actualizar o eliminar datos de la tabla mediante una vista de slo lectura.

EJERCICO 7.11: Crear una vista de slo lectura

Introduzca la siguiente sentencia para crear una vista de slo lectura de la tabla ORDERS que se corresponden con los pedidos que estn pendientes de ser enviados.

CREATE VIEW backlogged_orders AS SELECT * FROM orders WHERE status = B WITH READ ONLY;

Tenga en cuenta los siguientes aspectos sobre este primer ejemplo del comando CREATE VIEW La clusula AS del comando CREATE VIEW especifica la consulta de definicin de la vista. El conjunto resultado de una consulta de definicin de la vista determina la estructura de la vista (columnas y filas). Para crear una vista de slo lectura, se debe especificar la opcin UIT READ ONLY del comando CREATE VIEW para declarar explcitamente que la vista es de solo lectura; en caso contrario, Oracle crea la vista como una vista actualizable.

Vistas actualizablesOracle8i tambin permite definir vistas actualizables que puede usar una aplicacin para insertar, actualizar y eliminar datos de la tabla o hacer peticiones de datos.

EJERCICIO 7.12: Crear una vista actualizable

Para crear una vista como una vista actualizable, simplemente omita la opcin UIT READ ONLY del comando CREATE VIEW cuando se crea la vista. Por ejemplo, introduzca la siguiente sentencia CREATE VIEW, que crea una vista de unin actualizable de las tablas ORDERS y PARTS.

CREATE VIEW orders_items AS SELECT o.id AS orderid, o.orderdate AS orderdate, o.c_id AS customerid, i.id AS itemid, i.quantity AS quantity, i.p_id AS partid FROM orders o, items i WHERE o.id = i.o_id;

Incluso aunque declare una vista como actualizable, Oracle no soporta automticamente las sentencias INSERT, UPDATE y DELETE para la vista a menos que la definicin de la vista sea compatible con el principio de vista materializado. En concreto el principio de vista materializado asegura que el servidor puede asignar correctamente una operacin insert, update o delete a travs de una vista a los datos de la tabla subyacente de la vista.

La vista ORDERS_ITEMS es un ejemplo de una vista que no es compatible con el principio de vista materializado porque la vista une dato a partir de dos tablas. Por lo tanto, aunque incluso, se cre la vita ORDERS_ITEMS como actualizable, Oracle no soporta las sentencias INSERT, UPDATE y DELETE con las vista hasta que se creen uno o ms disparadores INSTEAD OF para la vista actualizable.

Vistas actualizables y disparadores INSTEAD OFIncluso aunque los tributos de una vista violen el principio de una vista materializando, se puede hacer la vista actualizable si se definen disparadores INSTEAD OF para la vista. Un disparador INSTEAD OF es un tipo especial de disparador de fila que se define para una vista. Un disparador INSTEAD OF explica lo que debera suceder cuando las sentencias INSERT, UPDATE o DELETE apuntan a la vista que, en caso contrario, no sera actualizable.

EJERCICIO 7.13: Crear un disparador INSTEAD OF para una vista actualizable

Para crear un disparador INSTEAD OF, se usa la siguiente sintaxis del comando CREATE TRIGGER:

CREATE [OR REPLACE] TRIGGER disparador INSTEAD OF {DELETE |INSERT |UPDATE [OF columna [,columna ]]} [OR{DELETE|INSERT|UPDATE [OF columna [,columna]]} ] ON tabla | vista } ...bloque PL/SQL...END [disparador]

Por ejemplo, introduzca la siguiente sentencia, que crea un disparador INSTEAD OF que define la lgica para manejar una sentencia INSERT que apunta a al vista ORDERS_ITEMS.

CREATE OR REPLACE TRIGGER orders_items_insertINSTEAD OF INSERT ON orders_itemsDECLARE currentOrderId INTEGER; currentOrderDate DATE;BEGIN- Determinar si todava existe el orden. SELECT id, orderdate INTO currentOrderId, currentOrderDate FROM orders WHERE id = :new.orderid;- Si se lanza la excepcin NO_DATA_FOUND,- Insertar un elemento nuevo en la tabla ITEMS. INSERT INTO items (o_id, id, quantity, p_id) VALUES (:new.orderid, :new.itemid, :new.quantity, :new.partid);EXCEPTION WHEN no_data_found THEN INSERT INTO orders (id, orderdate, c_id) VALUES (:new.orderid, :new.orderdate, :new.customerid); INSERT INTO items (o_id, id, quantity, p_id) VALUES (:new.orderid, :new.itemid, :new.quantity, :new.partid);END orders_items_insert;/

Ahora cuando una sentencia INSERT apunte a la vista ORDER_ITEMS, Oracle traducir la sentencia usando la lgica del disparador ORDERS_ITEMS_INSERT para insertar filas en las tablas subyacentes ORDERS e ITEMS. Por ejemplo, introduzca la siguiente sentencia INSERT:

INSERT INTO orders_items (orderid, orderdate, customerid, itemid, quantity) VALUES (1, 18-JUN-99, 1, 1, 1);

Ahora, haga peticiones a las tablas ORDERS e ITEMS para ver que el disparador funciona como se pens

SELECT * FROM orders;

ID C_ID ORDERDATE SHIPDATE PAIDDATE S---------- ---------- --------- --------- --------- - 1 1 18-JUN-99 18-JUN-99 30-JUN-99 F

SELECT * FROM items; O_ID ID P_ID QUANTITY---------- ---------- ---------- ---------- 1 1 3 1

VISTAS ACTUALIZABLES ORACLE VERSION 10g

UPDATE ( SELECT a.ID_N a_ID_N, a.C_TXT a_C_TXT, b.ID_N b_ID_N, b.C_TXT b_C_TXT FROM PRUEBA01 a, PRUEBA02 b WHERE a.ID_N = b.ID_N) SET b_C_TXT = a_C_TXT;

Vemoslo con un ejemplo:

SQL*Plus: Release 10.2.0.1.0 - Production on Jue Oct 25 14:31:19 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Introduzca el nombre de usuario: carlos@db01.xxxxxxIntroduzca la contrasea:

Conectado a:Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options

carlos@db01.xxxxxx> CREATE TABLE PRUEBA01(ID_N NUMBER NOT NULL, 2 C_TXT VARCHAR2(25));Tabla creada.carlos@db01.xxxxxx> CREATE TABLE PRUEBA02(ID_N NUMBER NOT NULL, 2 C_TXT VARCHAR2(25));Tabla creada.carlos@db01.xxxxxx> INSERT INTO PRUEBA01 VALUES (1,'UNO');1 fila creada.carlos@db01.xxxxxx> INSERT INTO PRUEBA02 VALUES (1,NULL);1 fila creada.carlos@db01.xxxxxx> COMMIT;Confirmacin terminada.

carlos@db01.xxxxxx> SELECT * FROM PRUEBA01; ID_N C_TXT---------- ------------------------- 1 UNOcarlos@db01.xxxxxx> SELECT * FROM PRUEBA02; ID_N C_TXT---------- ------------------------- 1carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N, 2 a.C_TXT a_C_TXT, 3 b.ID_N b_ID_N, 4 b.C_TXT b_C_TXT 5 FROM PRUEBA01 a, 6 PRUEBA02 b 7 WHERE a.ID_N = b.ID_N) 8 SET b_C_TXT = a_C_TXT; SET b_C_TXT = a_C_TXT *ERROR en lnea 8:ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave

Lo segundo que hay que entender es que una tabla es key-preserved si todas las claves de dicha tabla podran ser claves del resultado del join. Esto, dicho as, puede no parecer tan fcil de entender, pero la traduccin a romn paladino sera que una fila de una tabla no puede ser actualizada ms de una vez como resultado de la condicin del join (esto es: que coincida con ms de una fila de la segunda tabla).Y dnde mira el optimizador? A la estructura de la tabla (las PRIMARY KEYS):

carlos@db01.xxxxxx> ALTER TABLE PRUEBA01 2 ADD CONSTRAINT PRUEBA01_PK PRIMARY KEY(ID_N);

Tabla modificada.

carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N, 2 a.C_TXT a_C_TXT, 3 b.ID_N b_ID_N, 4 b.C_TXT b_C_TXT 5 FROM PRUEBA01 a, 6 PRUEBA02 b 7 WHERE a.ID_N = b.ID_N) 8 SET b_C_TXT = a_C_TXT;

1 fila actualizada.

Y, por ltimo: el mensaje de error es un poco confuso ya que:

carlos@db01.xxxxxx> ALTER TABLE PRUEBA01 DROP CONSTRAINT PRUEBA01_PK;

Tabla modificada.

carlos@db01.xxxxxx> ALTER TABLE PRUEBA02 2 ADD CONSTRAINT PRUEBA02_PK PRIMARY KEY(ID_N);Tabla modificada.carlosdb01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N, 2 a.C_TXT a_C_TXT, 3 b.ID_N b_ID_N, 4 b.C_TXT b_C_TXT 5 FROM PRUEBA01 a, 6 PRUEBA02 b 7 WHERE a.ID_N = b.ID_N) 8 SET b_C_TXT = a_C_TXT; SET b_C_TXT = a_C_TXT *ERROR en lnea 8:ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave

La columna que estamos modificando pertenece a una tabla que S tiene PK, pero NO ES key-preserved (ya que si hubiese dos filas en PRUEBA01 con ID_N=1 provocara que la misma fila en PRUEBA02 fuese actualizada con valores de ms de una fila de PRUEBA01).

Pero hay que tener en cuenta que una fila de una key-preserved table S PUEDE actualizar ms de una fila de la otra tabla:

carlos@db01.xxxxxx> ALTER TABLE PRUEBA02 DROP CONSTRAINT PRUEBA02_PK;

Tabla modificada.

carlos@db01.xxxxxx> ALTER TABLE PRUEBA01 2 ADD CONSTRAINT PRUEBA01_PK PRIMARY KEY(ID_N);

Tabla modificada.

carlos@db01.xxxxxx> INSERT INTO PRUEBA02 VALUES (1,'ONE');

1 fila creada.

carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;

ID_N C_TXT---------- ------------------------- 1 UNO 1 ONE

carlos@db01.xxxxxx> UPDATE ( SELECT a.ID_N a_ID_N, 2 a.C_TXT a_C_TXT, 3 b.ID_N b_ID_N, 4 b.C_TXT b_C_TXT 5 FROM PRUEBA01 a, 6 PRUEBA02 b 7 WHERE a.ID_N = b.ID_N) 8 SET b_C_TXT = a_C_TXT;

2 filas actualizadas.

carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;

ID_N C_TXT---------- ------------------------- 1 UNO 1 UNO

Nota: El hint BYPASS_UJVC evita toda la comprobacin por parte del optimizador de las key-preserved tables, pero es un hint indocumentado y -en teora- los mortales no deberamos utilizarlo, o hacerlo bajo nuestra propia responsabilidad

carlos@db01.xxxxxx> SELECT * FROM PRUEBA01;

ID_N C_TXT---------- ------------------------- 1 UNO

carlos@db01.xxxxxx> SELECT * FROM PRUEBA02;

ID_N C_TXT---------- ------------------------- 1

carlos@db01.xxxxxx> ALTER TABLE PRUEBA01 DROP CONSTRAINT PRUEBA01_PK;

Tabla modificada.

carlos@db01.xxxxxx> ALTER TABLE PRUEBA02 DROP CONSTRAINT PRUEBA02_PK;

Tabla modificada.

carlos@db01.xxxxxx> UPDATE /*+ BYPASS_UJVC */ ( SELECT a.ID_N a_ID_N, 2 a.C_TXT a_C_TXT, 3 b.ID_N b_ID_N, 4 b.C_TXT b_C_TXT 5 FROM PRUEBA01 a, 6 PRUEBA02 b 7 WHERE a.ID_N = b.ID_N) 8 SET b_C_TXT = a_C_TXT;

1 fila actualizada.

Secuencias Una aplicacin OLTP, como un sistema de reservas de lneas areas, suele soportar un gran nmero de usuarios concurrentes. A medida que la transaccin de cada usuario inserta una o ms filas nuevas en varias tablas de la base de datos, coordinar la generacin de claves principales nicas entre mltiples transacciones concurrentes puede ser un reto importante para la aplicacin.Afortunadamente, Oracle8i tiene una caracterstica que hace la generacin de valores nicos sea una cuestin trivial. Una secuencia es un objeto de esquema que genera una serie de enteros nicos y solo es apropiada para tablas que usen columnas simples numricas como claves, como las columnas ID que se usan en todas las tablas de nuestro esquema de prctica. Cuando una aplicacin inserta una fila nueva en una tabla, la aplicacin simplemente solicita una secuencia de base de datos para proporcionar el siguiente valor disponible en la secuencia para el valor de la clave principal de la nueva fila. Adems, la aplicacin puede volver a usar posteriormente una secuencia generada para coordinar los valores de las claves externas en filas hijas relacionadas. Oracle administra la generacin de secuencia con una sobrecarga insignificante, permiten incluso a las aplicaciones de procesamiento de transacciones en lnea (OLTP) que demandan muchos recursos realizar bien su trabajo

Crear y administrar secuenciasPara crear una secuencia, se usa el comando SQL CREATE SEQUENCE.

CREATE SEQUENCE [ esquema.] secuencia [ START WITH entero] [ INCREMENT BY emtero] [ MAXVALUE entero | NOMAXVALUE ] [ MINVALUE entero | NOMINVALUE ] [ CYCLE | NOCYCLE ] [ CACHE entero | NOCACHE] [ ORDER | NOORDER]

Tenga en cuenta que cuando se crea una secuencia, es posible personalizarla para que se ajuste a las necesidades particulares de una aplicacin; por ejemplo, una secuencia de Oracle puede ascender o descender en uno o ms enteros, tener valores mximos o mnimos y ms.Si es necesario, posteriormente se pueden modifica las propiedades de una secuencia usando el comando ALTER SEQUENC de SQL. El comando ALTER SEQUENCE soporta las mismas opciones y parmetros que el comando CREATE SEQUENCE, con la excepcin del parmetro START WITH.

EJEMPLO 7.14: Crear una secuencia

Introduzca la siguiente secuencia CREATE SEQUENCE para crear una secuencia para el ID de pedidos de ventas.

CREATE SEQUENCE order_ids START WITH 2 INCREMENT BY 1 NOMAXVALUE;

La secuencia ORDER_IDS comienza con el entero 2 (recuerde, ya se tiene un registro en la tabla ORDERS con un ID igual 1), se incrementa cada generacin de secuencia en 1 y no tiene valor mximo.

EJERCICIO 7.15: Usar y volver a usar un numero de secuencia

Para generar un nmero nuevo de secuencia para la sesin de usuario, una sentencia SQL debe hacer referencia a la secuencia y a su pseudocolumna NEXTVAL. Introduzca la siguiente sentencia INSERT para insertar un numero pedido de ventas y usa la secuencia ORDER_IDS_ para generar un ID de pedido

NOTAUna pseudocolumna se parece a una columna de una tabla. Las sentencias SQL pueden referenciar pseudocolumnas para recuperar datos, pero no pueden insertar, actualizar o eliminar datos haciendo referencia a una pseudocolumna

INSERT INTO orders (id, c_id, orderdate, status) VALUES (order_ids.NEXTVAL, 2, 18-JUN-99, B);

NOTAUna vez que la sesin genera un Nuevo nmero de secuencia, solo la sesin puede volver a usar el nmero de secuencia; otras sesiones que generen los nmeros de secuencia con la misma secuencia reciben nmeros de secuencia consecutivos de su propiedad.

Para volver a usar el nmero de secuencia actual asignado a la sesin, una sentencia SQL debe hacer referencia a la secuencia y a la pseudocolumna CURRVAL. Usando la pseudocolumna CURRVAL, la sesin puede volver a usar el nmero de secuencia actual las veces que se quiera, incluso despus de que una transaccin se confirme o se restaure. Por ejemplo, introduzca las siguientes sentencias INSERT para insertar varios elementos nuevos en la tabla ITEMS para el pedido actual y luego confirme la transaccin.

INTSERT INTO items (o_id, id, quantity) VALUES (order_ids.CURRVAL,2,4);

INSERT INTO items (o_id, id, quantity) VALUES (order_ids.CURRVAL,3,5);

INSERT INTO items (o_id, id, quantity) VALUES (order_ids.CURRVAL,3,5);

COMMIT;

Secuencias en Oracle11gOracle incluye nuevas caractersticas para PL/SQ en la base de datos 11g. Estas no ofrecen nuevas funcionalidades, sino que proveen un mejor funcionamiento, de una codificacin ms simple, y de mayor legibilidad.

CONTINUE StatementFinalente, PL/SQL tiene un Statement CONTINUE para sus loops. Sintacticamrnte este is igual como el Statement EXIT, y este habilita una clausula opcional WHEN y un label (etiquetado). En el siguiente ejemplo, una interfaz de tabla est preguntando y grabando sus flagged como comentarios saltados.

LOOP FETCH cur BULK COLLECT INTO rows; EXIT WHEN rows.COUNT=0; FOR I IN 1..rows.COUNT LOOP CONTINUE WHEN NOT EVALUATORS.single_line_comment(rows(i)); -- process responsive data ... END LOOP;END LOOP;

Para las filas del comentario, el control se mueve al final del loop, y se procesa el siguiente.

Secuencias sin DualNo es necesario ejecutar un SELECT en PL/SQL cuando se est recuperando el NEXTVAL o CURVAL desde una sequencia. El siguiente muestra como acceder a una secuencia llamada my_sequence desde PL/SQL

DECLAREnew_Val NUMBER;BEGINnew_Val := my_sequence.nextval;...END;It couldnt be simpler.

Sinnimos

Cuando los programadores crean una aplicacin de base de datos, es prudente evitar que la lgica de aplicacin haga referencia de forma directa a las tablas, visitas y otros objetos de base de datos. De lo contrario, las aplicaciones deben actualizarse y volverse a compilar despus de que un administrador realice una simple modificacin a un objeto, como un cambio de nombre o un cambio estructural.Para ayudar a hacer las aplicaciones menos dependientes de los objetos de base de datos, se pueden crear sinnimos para los objetos de base de datos. Un sinnimo es un alias para una tabla, una visita, una secuencia o a otro objeto que se almacene en la base de datos. Como un sinnimo es solo un nombre alternativo para un objetivo, no requiere ms almacenamiento que su definicin. Cuando una aplicacin usa un sinnimo, Oracle enva la peticin al objeto de la base de datos subyacente al sinnimo.

Sinnimos privados y pblicos

Oracle permite crear sinnimos tantos pblicos como privados. Un sinnimo pblico es un alias de un objeto (otro nombre) que se pone a disposicin de todos los usuarios de una base de datos.

Un sinnimo privado es un sinnimo dentro del esquema de un usuario especfico que controla el uso que hacen del sinnimo otras personas.

Crear sinnimos

Para crear un sinnimo, se usa el comando CREATE SYNONYM de SQL.

CREATE [ PUBLIC] [SYNONYM [ esquema.] sinonimo FOR [ esquema.] objeto

Si se incluye la palabra clave opcional PUBLIC, Oracle crea un sinnimo publico en caso contrario, Oracle crea un sinnimo privado.

EJERCIO 7.16: Crear un sinnimo

Introduzca la siguiente sentencia para crear el sinnimo privado CUST en el esquema actual. El sinnimo privado es un alias para la tabla CUSTOMERS en el mismo esquema.

CREATE SYNONYM cust FOR curtomers;

A continuacin, introduzca la siguiente sentencia para crear un sinnimo pblico para la tabla SALESREPS del esquema actual.

CREATE PUBLIC SYNONYM salespeople FOR salesreps;

EJERCICIO 7.17: Usar un sinnimo

El uso de un sinnimo es transparente, simplemente haga referencia al sinnimo en cualquier lugar donde desea que aparezca el objeto subyacente. Por ejemplo, introduzca la siguiente peticin que usa el nuevo sinnimo CUST.

SELECT id, lastname FROM cust;

El resultado es el siguiente:

ID LASTNAME---- --------1 Joy2 Musial

ndices

El rendimiento de una aplicacin es siempre crtico. Esto se debe a que la productividad del usuario de una aplicacin est relacionado directamente con la cantidad de tiempo que el usuario debe permanecer ocioso mientras la aplicacin intenta realizar el trabajo. Con las aplicaciones de base de datos, el rendimiento depende mucho de lo rpido que una aplicacin pueda acceder a los datos de la tabla. Normalmente, la E/S a disco es el factor principal determinante en el acceso a tablas, cuanta menos E/S a disco sea necesaria, mayor ser el rendimiento de las aplicaciones dependientes. En general, es mejor intentar minimizar la cantidad de accesos a disco que las aplicaciones deben realizar a la hora de trabajar con las tablas de las base datos.

NOTAEsta seccin introduce los ndices para dar soporte a las secciones posteriores de este libro. Si desea informacin completa sobre los diferentes tipos de ndices que soporta Oracle y otros temas relacionados con el rendimiento, consulte el Capitulo 12

Un uso adecuado de los ndices de tabla es el principal mtodo de reproducir la E/S a disco y mejorar el rendimiento del acceso a tablas. Al igual que el ndice de un libro, el ndice de la columna de una tabla (o conjunto de columnas) permite a Oracle buscar rpidamente registros especficos de la tabla. Cuando una aplicacin hace peticiones a una tabla y usa una columna indexada en su criterio de seleccin, Oracle usa de forma automtica el ndice para buscar rpidamente las filas destino con una E/S mnima. Sin un ndice, Oracle tiene que leer toda la tabla desde disco para localizar las filas que coinciden con un criterio de seleccin.La presencia de un ndice para una tabla es totalmente opcional y transparente para los usuarios y para los programadores de aplicaciones de base de datos. Por ejemplo:

Las aplicaciones pueden acceder a los datos de tablas con o sin ndice asociados. Cuando existe un ndice y ayuda al rendimiento de la peticin de una aplicacin, Oracle usa automticamente el ndice; si no es as, Oracle ignora el ndice. Oracle actualizar automticamente un ndice para mantenerlo sincronizado con su tabla. Aunque los ndices pueden mejorar drsticamente el rendimiento de las peticiones de las aplicaciones, se desaconseja indexar todas las columnas de una tabla. Los ndices son significativos sol para columnas clave que las peticiones de la aplicacin usan especficamente para buscar filas de inters. Adems, el mantenimiento de los ndice genera una sobrecarga, los ndice innecesarios pueden en realidad ralentizar el sistema en vez de mejorar el rendimiento.Oracle 8i soporta diferentes tipos de ndices para satisfacer muchos tipos de requisitos de aplicaciones. El tipo de ndice que ms se usa en una base de datos Oracle es un ndice rbol-B, que a veces se conoce en la documentacin de Oracle con el nombre de ndice normal. Las siguientes secciones explican ms acerca de los ndices de rbol-B, que puede crear para las columnas de una tabla.

ndice de rbol-B

El tipo predeterminado y ms comn de ndice para la columna de una tabla es el ndice de rbol-B. Un ndice de rbol-B, o ndice normal, es un rbol ordenado de nodos ndice, cada uno de los cuales contiene una o ms entradas de ndice. Cada entrada de ndice se corresponde con una fila de tabla y contiene dos elementos: El valor indexado de la columna (o conjunto de valores) para la fila. El ROWID (o ubicacin fsica en el disco) de la fila.

Un ndice de rbol-B contiene una entrada para cada gila de la tabla, a no ser que la entrada de ndice para una fila sea nula. La figura 7.4 muestra un ndice tpico de rbol-B.

Figura 7.4.Un ndice de rbol-B

A la hora de usar un ndice rbol-B, Oracle desciende por el rbol de nodos ndice buscando valores de ndice buscando valores de ndice que coincidan con el criterio de seleccin de consulta. Cuando encuentra una coincidencia, Oracle usa el ROWID correspondiente para localizar y leer los datos de la fila de la tabla asociada desde el disco

Usar ndices de rbol-B de forma adecuadaLos ndices de rbol-B no son adecuados para todos los tipos de aplicaciones y todos los tipos de columnas de una tabla. En general, los ndices de rbol-B son la mejor eleccin para aplicaciones OLTP en las que se insertan, actualizan y eliminan datos constantemente. En tales entornos, los ndices de rbol-B funcionan mejor para columnas clave que contienen muchos valores distintos relativos al nmero total de valores clave de la columna. Las claves principales y alternativa de una tabla son ejemplos perfectos de columnas que deberan tener ndice de rbol-B. Convenientemente, Oraclave8i crea ndices de rbol-B de forma automtica para todas las restricciones de integridad de clave principal y nica de una tabla.

Crear ndice de rbol-BPara crear un ndice, se usa el comando CREATE INDEX de SQL. A continuacin se presenta una versin abreviada de la sintaxis del comando CREATE INDEX que se centra nicamente en las partes del comando que pertenecen a los ndices de rbol-B (normales).

CREATE [ UNIQUE] INDEX [ esquema/] indice ON { [ESQUEMA.] tabla { columna [ ASC | DESC] [, columna [ ASC|DESC ] ] )

Tenga en cuenta las siguientes consideraciones sobre el comando CREATE INDEX: Con la inclusin de la palabra clave opcional UNIQUE, puede evitar valores duplicados en el ndice. Sin embargo, en lugar de crear un ndice nico, Oracle Corp. recomienda que declare una restriccin nica para una tabla, con el fin de que la restriccin de integridad sea visible junto con otras restricciones de integridad en la base de datos. Es necesario especificar una o ms columnas que se deben indexar. Para cada ndice, puede especificar que se desea que los ndices almacenen los valores en orden ascendente o descendente.

NOTALas versiones de Oracle anteriores Oracle8i soportaban la palabra clave DESC para crear un ndice de rbol-B, pero siempre creaban ndices ascendentes. Oracle8i tambin soporta ahora ndices descendentes.

EJERCICIO 7.18: Crear un ndice de rbol-B

Para facilitar uniones ms rpidas entre las tablas ITEMS y PARTS, introduzca el siguiente comando, que crea un ndice de rbol-B para las columnas en la columna de clave externa PART_FK de la tabla ITEMS.

CREATE INDEX item_p_id On item (p_id ASC);

Oracle 11g y sus ndices Invisibles

Los ndices invisibles, disponibles a partir de Oracle Database 11g, segn la documentacin oficial, son una interesante alternativa al borrado de ndices o seteo como "unusable". Pero, son realmente una alternativa?Segn la informacin disponible, un ndice invisible ser ignorado por el optimizador al momento de evaluar los planes de ejecucin, excepto que alteremos el valor de sesin (o sistema) OPTIMIZER_USE_INVISIBLE_INDEXES, pero en todo momento el estado del ndice ser vlido. Esto nos brinda mltiples beneficios.Un ndice unusable es una forma sutil de dejar un ndice "fuera de combate" temporalmente en TODAS las sesiones. Un ndice invisible en cambio es selectivo: podemos verlo (y usarlo) en una sesin mientras en el resto hacer como si no existiera.Un ndice invisible puede volverse visible sin costo alguno, con una simple alteracin del mismo: ALTER INDEX mi_indice VISIBLE. Un ndice unusable en cambio deber ser reconstrudo, ya que las actualizaciones de datos sobre la tabla no fueron reflejadas mientras el ndice estuvo en ese estado. Los ndices invisibles SIEMPRE son mantenidos por Oracle con cada sentencia DML.

Con estas afirmaciones, podemos concluir que los ndices invisibles llegan para sustituir el estado unusable?-No! El propsito es completamente diferente. Los ndices invisibles evitarn, por ejemplo, que tengamos que 'inutilizar' ndices para poder realizar performance tunning, pero en ciertos casos particulares seguiremos necesitando del estado unusable.

Cuando tengamos que realizar una carga masiva de datos, tendremos que alterar el ndice como 'unusable' para realizar la carga eficientemente, ya que la invisibilidad de ndices de 11g tiene efecto sobre las consultas SELECT y no sobre las dems sentencias DML. El ndice invisible contina aportando overhead en cada modificacin de datos.

La clave de este novedoso estado est en el testeo de consultas. Por ejemplo, podremos probar cmo se comporta un SELECT con determinado ndice, sin que el ambiente en el cual lo estoy probando se entere. Creo un ndice invisible, seteo el valor de OPTIMIZER_USE_INVISIBLE_INDEXES como true, y testeo mi aplicacin sin efectos colaterales.Otro beneficio, razonando en forma opuesta: Cmo funcionara mi aplicacin si determinado ndice no existiera?Alteramos el ndice como 'invisible', seteamos en nuestra sesin OPTIMIZER_USE_INVISIBLE_INDEXES = false, y probamos. Los dems usuarios mientras tanto, continuarn utilizndolo normalmente.

Los ndices invisibles son un valor agregado a nuestra forma de trabajo, ya que nos proveen transparencia sobre el ambiente. Nos permiten realizar nuestras pruebas tranquilos de que nadie est siendo afectado.

EJERCICIO EXTRA: ndice invisible

1.- Se genera una estructura de tabla con su clave primaria

SQL> create table t2 (column1 number not null,3 constraint pk_t PRIMARY KEY (column1));Table created.

2.- Le insertamos datos

SQL> insert into t (select rownum from dba_objects);72137 rows created.SQL> commit;Commit complete.

3.- Llevamos a cabo una consulta para ver su plan de ejecucin

SQL> explain plan for2 select * from t 3 where column1 = 10000;Explained.SQL> set linesize 130SQL> set pagesize 0SQL> SQL> select * from table(DBMS_XPLAN.DISPLAY);Plan hash value: 1517170033-------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 ||* 1 | INDEX UNIQUE SCAN| PK_T | 1 | 13 | 1 (0)| 00:00:01 |-------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("COLUMN1"=10000)13 rows selected.

Como se puede apreciar, ocupa de forma clara , el ndice PK_T

4.- Dejamos el ndice como INVISIBLE y vemos nuevamente el plan de ejecucin

SQL> alter index pk_t invisible;Index altered.SQL> explain plan for2 select * from t3 where column1 = 10000;Explained.SQL> select * from table(DBMS_XPLAN.DISPLAY);Plan hash value: 1601196873---------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------| 0 | SELECT STATEMENT | | 91 | 1183 | 32 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 91 | 1183 | 32 (0)| 00:00:01 |---------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("COLUMN1"=10000)13 rows selected.

Como se puede apreciar, ahora el ndice no es VISIBLE , pero en el fondo no es visible para el optimizador, por el cual queda fuera de los planes de ejecucin que el optimizador pueda generar.

5.- Aunque utilicemos explicitamente el nombre del ndice con un hint, este no ser utilizado

SQL> explain plan for2 select /*+ index(t pk_t) */ * from t where column1 = 20000;Explained.SQL> select * from table(DBMS_XPLAN.DISPLAY);Plan hash value: 1601196873-------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 91 | 1183 | 32 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 91 | 1183 | 32 (0)| 00:00:01 |-------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("COLUMN1"=20000)13 rows selected.

6.- Podemos ver el estado de un ndice INVISIBLE consultando cualquier vista *_INDEXES

SQL> select owner , index_name , visibility from dba_indexes where index_name like 'PK_T';OWNER INDEX_NAME VISIBILIT------------------------------ ------------------------------ ---------SYS PK_T INVISIBLE

7.- Existe un parmetro de inicializacin llamado OPTIMIZER_USE_INVISIBLE_INDEXES, que le indica al optimizador si toma o no en cuenta los ndices en estado INVISIBLE cuando esta realizando los planes de ejecucin, por defecto est en FALSE, o sea, no toma en cuenta los ndices INVISIBLES.

SQL> show parameter optimizer_use_inviNAME TYPE VALUE------------------------------------ ----------- ------------------------------optimizer_use_invisible_indexes boolean FALSE

8.- Pero si cambiamos este parmetro y lo dejamos en TRUE, toma en cuenta los ndices aunque ellos estn INVISIBLES

SQL> alter system set optimizer_use_invisible_indexes=TRUE scope=both;System altered.SQL> select owner , index_name , visibility from dba_indexes where index_name like 'PK_T';OWNER INDEX_NAME VISIBILIT------------------------------ ------------------------------ ---------SYS PK_T INVISIBLESQL> SQL> explain plan forselect /*+ index(t pk_t) */ * from t where column1 = 20000; 2 Explained.SQL> select * from table(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------Plan hash value: 1517170033--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 ||* 1 | INDEX UNIQUE SCAN| PK_T | 1 | 13 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------1 - access("COLUMN1"=20000)13 rows selected.

El diccionario de datos: Un esquema nico

Todas las bases de datos Oracle usan unas cuantas tablas y vistas de sistema para hacer un seguimiento de los metadatos, sobre los datos de una base de datos. Esta coleccin de objetos del sistema se llama diccionario de datos de la base de datos de Oracle o catalogo del sistema. Oracle organiza el diccionario de datos de una base de datos dentro del esquema SYS.A medida que se crea y administran esquemas en una base de datos de Oracle, es posible mostrar informacin sobre los objetos de esquemas asociados realizando consultas a las tablas y las vistas del diccionario de datos. Por ejemplo, la Tabla 7.3 proporciona una lista de las diferentes vistas del diccionario de datos que corresponden a los objetos de esquema que se han explicado en este captulo. Las vistas que empiezan con el prefijo DBA_ muestran toda la informacin en la correspondientes tablas de base del diccionario de datos Como las vistas DBA son completas, solo pueden acceder a ellas los usuarios que tienen el privilegio de sistema SELECT ANY TABLE.(consulte el capitulo 9 si desea ms informacin sobre privilegios y seguridad dbase de datos.) Las vistas que empiezan con el prefijo ALL_ estn a disposicin de todos los usuarios y muestran aspectos especficos del dominio privilegiado del usuario actual. Las vistas que empiezan con el prefijo USER_ estn a disposicin de todos los usuarios y muestran aspecto especficos del usuario actual.

Tabla 7.3Visitas del diccionario de datos que se corresponden con las tablas, columnas, restricciones, vistas, secuencias, sinnimos e ndice

Tipo de objeto esquemaVistas interesantes del diccionario de datos

Tablas y columnasDBA_TABLES, ALL_TABLES y USER_TABLES muestran informacin general sobre la base de datos.

DBA_TAB_COLUMNS, ALL_TAB_COLUMNS y USER_TAB_COLUMNS muestran informacin sobre las columnas de cada tabla de la base de datos

NOTA: DBA_OBJECTS, ALL_OBJECTS y USER_OBJECTS muestran informacin sobre objetos de esquema, incluyendo tablas.

Restricciones de integridadDBA_CONSTRAINTS, ALL_CONSTRAINTS y USER_CONSTRAINTS muestran informacin general sobre restricciones.

DBA_CONS_COLUMNS, ALL_CONS_COLUMNS y USER_CONS_COLUMNS muestra informacin sobre columnas y restricciones asociadas.

VistasDBA_VIEWS, ALL_VIEWS y USER_VIEWS.

NOTA: DBA_OBJECTS, ALL_OBJECTS y USER_OBJECTS tambin muestran informacin sobre objetos de esquema incluyendo vistas

SecuenciasDBA_SEQUENCES, ALL_SEQUENCES y USER_SEQUENCES

NOTA: DBA_OBJECT, ALL_OBJECTS y USER_OBJECTS muestran informacin sobre objetos de esquema, incluyendo secuencia.

SinnimosDBA_SYNONYMS, ALL_SYNONYMS y USER_SYNONYMS

NOTA: DBA_BJECTS. ALL_OBJECTS y USER_OBJECTS muestran informacin sobre objetos de esquema, incluyendo sinnimos

ndicesDBA_INDEXES, ALL_INDEXES, USER_INDEXES, DBA_IND_COLUMNS, ALL_IND_COLUMNS y USER_IND_COLUMNS.

Ejercicio 7.19: Consultar el diccionario de datos

En este ltimo ejercicio prctico de este captulo, realizaremos consultas al diccionario de datos para mostrar informacin sobre las restricciones de integridad creadas en los ejercicios anteriores de este captulo. Para esta peticin, necesitamos apuntar a la vista de diccionario de datos USER_CONSTRAINTS. En primer lugar, introduzca el siguiente comando DESCRIBE para mostrar las columnas disponibles en la vista USER_CONSTRAINTS.

Resumen del capitulo

En este captulo se han explicado muchos tipos diferentes de objetos que puede crear en un esquema bsico de base de datos relacional. Las tablas son la estructura de datos bsica de cualquier base de datos relacional. Una tabla no es nada ms que una coleccin ordenada de filas que tienen toda ellas las mismas columnas. El tipo de datos de una columna describe el tipo bsico de datos que puede aceptar la columna. Para crear y modificar la estructura de una tabla, use los comandos CREATE TABLE y ALTER TABLE de SQL. Para imponer las reglas de negocios que describen los datos que pueden aceptar las columnas de una tabla, se pueden declarar restricciones de integridad junto con la tabla. Se pueden usar restricciones de integridad de dominio, como restricciones no nulas y restricciones de comprobacin, para definir explcitamente el dominio de los valores que aceptan las columnas. Se pueden usar restricciones de integridad de entidad, como restricciones de clave principal y restricciones nicas, para evitar filas duplicadas en una tabla. Por ltimo, se pueden usar restricciones de integridad referenciales para establecer e imponer la relacin entre diferentes columnas y tablas de una base de datos. Se pueden declarar todos los tipos de restricciones de integridad cuando se crea una tabla con el comando CREATE TABLE, o despus de crear la tabla con el comando ALTER TABLE. Una vista es un objeto de esquema que presenta datos de una o ms tablas. Una vista no es nada ms que una consulta que Oracle almacena en el diccionario de datos de la base de datos como un objeto de esquema. Cuando se usa vista para hacer cualquier cosa, Oracle deriva los datos de la vista desde la consulta de definicin de la vista. Para crear una vista se usa el comando CREATE VIEW de SQL. Una secuencia es un objeto de esquema que genera una serie de enteros nicos. Las secuencias se usan casi siempre para generar claves principales unicas para columnas de tipo ID. Cuando una aplicacin inserta una nueva fila en la tabla, la aplicacin puede realizar una consulta de una secuencia de base de datos para generar el prximo valor disponible en la secuencia para el valor de la clave principal de la fila La aplicacin puede volver a usar posteriormente un numero de secuencia generado para coordinar los valores de las claves externas en filas hijas relacionadas. Para crear una secuencia se usa el comando CREATE SEQUENCE de SQL. Para generar y volver a usar luego un nmero de secuencia, se hace referencia a las pseudocolumnas NEXTVAL y CURRVAL de la secuencia, respectivamente. Para hacer las aplicaciones menos dependientes de las tablas y otros objetos de esquema, se pueden crear sinnimos para los objetos de esquema. Un sinnimo es un alias para una tabla, vista, secuencia u otro objeto de esquema que se almacene en la base de datos. Los sinnimos se crean con el comando CREATE SYNONYM de SQL. Para mejorar el rendimiento del acceso a las tablas, se puede crear un ndice para una o ms columnas de la tabla. Se usa el comando CREATE INDEX de SQL para crear un ndice.

DESCRIBE user_constraints;

Name Null? Type--------------------- -------- ------OWER NOT NULL VARCHAR2(30)CONSTRAINT_NAME NOT NULL VARCHAR2(30)CONSTRAINT_TYPE VARCHAR2(1)TABLE_NAME NOT NULL VARCHAR2(30)SEARCH_CONDITION LONGR_OWNER VARCHAR2(30)R_CONSTRAINT_NAME VARCHAR2(30)DELETE_RULE VARCHAR2(9)STATUS VARCHAR2(8)DEFERRABLE VARCHAR2(14)DEFERRED VARCHAR2(9)VALIDATED VARCHAR2(13)GENERATED VARCHAR2(14)BAD VARCHAR2(3)RELY VARCHAR2(4)LAST_CHANGE DATE

Como puede ver, la vista USER_CONSTRAINTS contiene muchas columnas para registrar las propiedades de las restricciones de integridad. Para este ejercicio, introduzca la siguiente consulta para ver el nombre de cada restriccin, la tabla con la que est asociada, el tipo de restriccin y si la restriccin es aplazable o no.

SELECT constraint_name, table_name, DECODE(constraint_type, C,CHECK, P,PRIMARY KEY, U,UNIQUE, R,REFERENTIAL, O,VIEW WITH READ ONLY, OTHER) constraint_type, deferrable FROM user_constraints;

Si se han realizado los ejercicios anteriores en este captulo, el resultado de esta peticin debera ser el siguiente:

CONSTRAINT_NAME TABLE_NAME CONSTRAINT_TYPE DEFERRABLE--------------- ----------------- -------------------- ----------SYS_C002892 BACKLOGGED_ORDERS VIEW WITH READ ONLY NOT DEFERRABLECUSTOMERS_PK CUSTOMERS PRIMARY KEY NOT DEFERRABLESYS_C002882 CUSTOMERS UNIQUE NOT DEFERRABLESALESREPS_FK CUSTOMERS REFERENTIAL NOT DEFERRABLEQUANTITY_NN ITEMS CHECK NOT DEFERRABLEITEMS_PK ITEMS PRIMARY KEY NOT DEFERRABLEORDERS_PK ITEMS REFERENTIAL NOT DEFERRABLEPARTS_FK ITEMS REFERENTIAL NOT DEFERRABLESYS_C002876 ORDERS CHECK NOT DEFERRABLESTATUS_CK ORDERS CHECK DEFERRABLEORDERS_PK ORDERS PRIMARY KEY NOT DEFERRABLEC_ID_NN ORDERS CHECK NOT DEFERRABLECUSTOMERS_FK ORDERS REFERENTIAL NOT DEFERRABLESYS_C002879 PARTS CHECK NOT DEFERRABLEPARTS_PK PARTS PRIMARY KEY NOT DEFERRABLE