PRÁCTICA DE SISTEMAS GESTORES DE BASES DE DATOS

(1)

DE BASES DE DATOS

UNIVERSIDAD DE LAS ISLAS BALEARES

SISTEMAS GESTORES DE BASES DE DATOS

(2)

Índice de contenido

1.Instalación de Oracle...4 1.1.Acontecimientos... 4 1.2.Soluciones...4 2.Práctica 1... 5 2.1.Apartado (a)...5 2.1.1.Crear usuario SGBD1... 5 2.1.2.Crear usuario SGBD2... 5 2.1.3.Creación de tablas...6 2.1.4.Ficheros de control... 8 2.2.Apartado (b)...10 2.2.1.Tablas...10 2.2.2.Restricciones...12

2.2.3.Esquemas de secuencia y disparadores...13

2.3.Apartado (c)...14

2.3.1.Rellenar la tabla Cliente de SGBD2 a partir de Clientes de SGBD1... 14

2.3.2.Rellenar la tabla Fondo de SGBD2 a partir de Valores de SGBD1... 14

2.3.3.Rellenar la tabla Cuenta de SGBD2 a partir de Clientes de SGBD1...14

2.3.4.Rellenar la tabla Contratar de SGBD2 a partir de Clientes de SGBD1...14

2.3.5.Rellenar la tabla Valor de SGBD2 a partir de Valores de SGBD1...15

2.3.6.Rellenar la tabla Venta de SGBD2 a partir de Ventas de SGBD1... 15

2.3.7.Rellenar la tabla Generar de SGBD2 a partir de Ventas de SGBD1... 15

2.4.Apartado (d)...17

2.4.1.Plan de ejecución... 17

3.Práctica 2... 19

3.1.Organización física propuesta... 19

3.2.Atributos físicos de cada una de las tablas... 20

3.3.Cláusula de Almacenamiento... 21

3.4.Estimación del tamaño de los índices...24

3.5.Creación de los dos tablespaces... 27

3.6.Creación del usuario SGBD3... 28

3.7.Creación de las tablas para SGBD3...28

3.8.Crear las restricciones...32

3.8.1.Cuenta... 32 3.8.2.Contratar... 32 3.8.3.Valor... 32 3.8.4.Compra... 32 3.8.5.Venta...32 3.8.6.Generar... 32

(3)

3.9.Crear los esquemas de secuencia y disparadores... 33

3.9.1.Compra... 33

3.9.2.Venta...33

3.10.Cargar los datos... 33

3.10.1.Problema... 34

4.Optimización de la consulta... 34

4.1.1.Nuevo plan de ejecución...35

5.ANEXO 1: Logs arrojados por SQL Loader...37

5.1.control.log de la tabla CLIENTES... 37

5.2.control.log de la tabla VALORES...38

5.3.control.log de la tabla COMPRAS... 39

(4)

1. Instalación de Oracle

1.1. Acontecimientos

● Seguidos todos los pasos indicados en el PDF colgado en la web de la

asignatura.

● Instalado en un disco auxiliar.

● Funciona correctamente aunque existen problemas con el listener.

● He tratado de configurar el fichero tsnames.ora y el listeners.ora; estoy

convencido de que se trata de problemas con el nombre del host. Durante la instalación obtuve dos mensajes de aviso sobre posibles problemas con el acceso por red y efectivamente los pronósticos se han cumplido.

● Cómo consecuencia de ello:

● No puedo acceder al Enterprise Database Manager. ● No puedo acceder a iSQL *Plus.

1.2. Soluciones

● Documentación consultada:

file:///D:/Oracle%2010g%20R2/database/doc/install.102/b14316/reqs.htm#B ABGHEFB

● La dirección IP se obtiene mediante DHCP, en el manual de instalación se

propone instalar un adaptador de bucle invertido, solución adoptada, desactivar el DHCP para hacer la práctica.

● Se necesita un mínimo de 256 MB de RAM, y mi PC únicamente tiene 239

MB ya que la restante la usa la tarjeta gráfica cómo memoria compartida.

● Tras la reinstalación los problemas parecen haberse resuelto, ya tengo

acceso vía http.

● Al iniciar el sistema y tratar de conectarme al enterprise manager aparece

un mensaje de página no encontrada en el explorador. Tras buscar información en Google llego a la conclusión de que debo ejecutar el comando <emctl start dbconsole>, el resultado inmediato es un mensaje de error indicando que la variable de entorno oracle_sid no está definida, por lo tanto tras ejecutar <set ORACLE_SID=SGBD> ejecuto de nuevo el comando anterior y el problema desaparece.

● No entiendo porque las dos primeras veces funcionó, iniciándose

automáticamente, y luego dejo de hacerlo.

● A veces es necesario inicial e listener nualmente mediante el comando

(5)

2. Práctica 1

2.1. Apartado (a)

2.1.1. Crear usuario SGBD1

● Documentación consultada:

file:///D:/B19306_01/server.102/b14196/users_secure004.htm#sthref437

1. Conectarse al Enterprise Manager cómo SYSTEM.

2. Ir a Administración -> Usuarios (en Esquema, Usuarios y Privilegios) -> Crear.

3. Sentencia SQL:

CREATE USER "SGBD1" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "USERS" ACCOUNT UNLOCK

GRANT CREATE TABLE TO "SGBD1" GRANT "CONNECT" TO "SGBD1"

● CONNECT es el rol que tiene asignado todos los privilegios necesarios

para iniciar una sesión en Oracle (es decir únicamente el privilegio de sistema CREATE SESSION).

● CREATE TABLE nos permitirá crear tablas en nuestro tablespace así cómo

llenarlas.

● Se le asigna una cuota ilimitada sobre su tablespace por defecto (si no se

indicara cuota no podría crear objetos).

● Documentación para asignar permisos (privilegios):

file:///D:/B19306_01/network.102/b14266/authoriz.htm#DBSEG5000 file:///D:/B19306_01/network.102/b14266/authoriz.htm#i1009202

2.1.2. Crear usuario SGBD2

CREATE USER "SGBD2" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "USERS" ACCOUNT UNLOCK

(6)

GRANT CREATE TABLE TO "SGBD2" GRANT CREATE TRIGGER TO "SGBD2" GRANT "CONNECT" TO "SGBD2"

Ahora conectándose con SGBD1 le asignamos permisos al recien creado SGBD2 para consultar sus tablas:

grant select on clientes to sgbd2; grant select on valores to sgbd2; grant select on compras to sgbd2; grant select on ventas to sgbd2;

2.1.3. Creación de tablas

Nota: los valores numéricos tienen cómo mucho 5 decimales en aquellos casos en que sea necesario.

Crear tabla CLIENTES

create table clientes ( dni varchar2(9) not null, nombre varchar2(15) not null, apellido1 varchar2(20) not null, apellido2 varchar2(20),

numero varchar2(20) not null, isin varchar2(12) not null, fechaapertura date not null, fechabaja date,

constraint clientes_pk primary key (dni) );

Al importar los datos se ve que la clave primaria no se cumple por tanto la eliminamos -> alter table clientes drop constraint clientes_pk;

Crear tabla VALORES

create table valores( isin varchar2(13) not null, nombre varchar2(51) not null, fecha date not null,

cotizacion number(38,5) not null,

constraint valores_pk primary key (isin,fecha) );

(7)

Al importar los datos se ve que la clave primaria no se cumple por tanto la eliminamos -> alter table valores drop constraint valores_pk;

Crear tabla COMPRAS

Para ser coherentes con las demás tablas suponemos:

● DNIs de 9 caracteres.

● Identificadores de fondo de 12 caracteres. ● Números de cuenta de 20 caracteres.

create table compras( dni varchar2(9) not null, nombre varchar2(20) not null, apellido1 varchar2(20) not null, apellido2 varchar2(20),

isin varchar2(12) not null, fondo varchar2(50) not null, cuenta varchar2(20) not null, fecha date not null,

participaciones number(38,5) not null, precio number(38,5) not null,

constraint compras_pk primary key (dni,fecha) );

Una operación cómo máximo al día (repercute en la clave primaria elegida) -> Esta regla no se cumple por lo que se eliminará la restricción; -> alter table compras drop constraint compras_pk;

Crear tabla VENTAS

Para ser coherentes con las demás tablas suponemos:

● DNIs de 9 caracteres.

● Identificadores de fondo de 12 caracteres. ● Números de cuenta de 20 caracteres.

create table ventas( dni varchar2(9) not null, nombre varchar2(20) not null, apellido1 varchar2(20) not null, apellido2 varchar2(20),

isin varchar2(12) not null, fondo varchar2(50) not null, cuenta varchar2(20) not null, fecha date not null,

(8)

precio number(38,5) not null,

participaciones number(38,5) not null, fechacompra date not null,

constraint ventas_pk primary key (dni,fecha) );

Una operación cómo máximo al día (repercute en la clave primaria elegida). -> Esta regla no se cumple por lo que se eliminará la restricción; -> alter table ventas drop constraint ventas_pk;

2.1.4. Ficheros de control

● Documentación SQL*Loader utilizada:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_concepts.htm#g 1013706

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm #i1004663 (fichero de control).

● Links Google:

http://www.orape.net/modules.php?name=News&file=print&sid=19 (fichero de control).

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html#date%20format (máscara de fechas en el fichero de control).

http://www.oreilly.com/catalog/orsqlloader/chapter/ch01.html (registros de longitud variable).

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch03.htm#1004653 (optionally enclosed).

Sintaxis a usar desde la línea de comandos:

<sqlldr userid=sgbd1/sgbd1 ,control=fichero_de_control ,data=fichero_de_datos> dónde fichero_de_control contiene toda las directivas propias de SQL*Loader para que pueda trasladar oportunamente los datos desde el fichero_de_datos hasta la tabla especificada en el propio fichero.

(9)

● Este fichero se encuentra en formato o longitud fija.

● Cada registro del fichero tiene 134 bytes de longitud (contando un byte por

carácter).

Archivo de control usado:

LOAD DATA APPEND

INTO TABLE clientes( dni POSITION(01:09) CHAR, nombre POSITION(10:24) CHAR, apellido1 POSITION(25:44) CHAR,

apellido2 POSITION(45:64) CHAR NULLIF apellido2=BLANKS, numero POSITION(65:84) CHAR,

isin POSITION(85:96) CHAR,

fechaapertura POSITION(97:115) DATE "DD/MM/YYYY HH24:MI:SS", fechabaja POSITION(116:134) DATE "DD/MM/YYYY HH24:MI:SS")

Importar datos en la tabla VALORES

● Este fichero se encuentra en formato o longitud fija al igual que el anterior. ● Cada registro del fichero tiene 122 bytes de longitud.

Archivo de control usado:

LOAD DATA APPEND

INTO TABLE valores(

isin POSITION(01:13) CHAR, nombre POSITION(14:64) CHAR,

fecha POSITION(65:84) DATE "DD/MM/YYYY",

cotizacion POSITION(85:122) CHAR "TO_NUMBER(:cotizacion)")

Importar datos en la tabla COMPRAS

● Este fichero se encuentra en formato o longitud variable, con campos

separados por el carácter ';'. Archivo de control usado:

LOAD DATA APPEND

(10)

dni CHAR TERMINATED BY ";" ENCLOSED BY '"', nombre CHAR TERMINATED BY ";" ENCLOSED BY '"', apellido1 CHAR TERMINATED BY ";" ENCLOSED BY '"',

apellido2 CHAR TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"', -- este campo puede no estar presente

isin CHAR TERMINATED BY ";" ENCLOSED BY '"', fondo CHAR TERMINATED BY ";" ENCLOSED BY '"', cuenta CHAR TERMINATED BY ";" ENCLOSED BY '"',

fecha DATE "DD/MM/YYYY HH24:MI:SS" TERMINATED BY ";",

participaciones CHAR TERMINATED BY ";" "TO_NUMBER(:participaciones)", precio CHAR TERMINATED BY "\n" "TO_NUMBER(:precio)")

Importar datos en la tabla VENTAS

● Este fichero se encuentra en formato o longitud variable, con campos

separados por el carácter ';'. Archivo de control usado:

LOAD DATA APPEND

INTO TABLE ventas(

dni CHAR TERMINATED BY ";" ENCLOSED BY '"', nombre CHAR TERMINATED BY ";" ENCLOSED BY '"', apellido1 CHAR TERMINATED BY ";" ENCLOSED BY '"',

apellido2 CHAR TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"', -- este campo puede no estar presente

isin CHAR TERMINATED BY ";" ENCLOSED BY '"', fondo CHAR TERMINATED BY ";" ENCLOSED BY '"', cuenta CHAR TERMINATED BY ";" ENCLOSED BY '"',

fecha DATE "DD/MM/YYYY HH24:MI:SS" TERMINATED BY ";", precio CHAR TERMINATED BY ";" "TO_NUMBER(:precio)",

participaciones CHAR TERMINATED BY ";" "TO_NUMBER(:participaciones)", fechacompra DATE "DD/MM/YYYY HH24:MI:SS" TERMINATED BY "\n")

2.2. Apartado (b)

2.2.1. Tablas Cliente

create table cliente( dni varchar2(9) not null,

(11)

nombre varchar2(15) not null, apellido1 varchar2(20) not null, apellido2 varchar2(20),

constraint cliente_pk primary key (dni) );

Cuenta

create table cuenta(

numerotitulares number(5) not null,

constraint cuenta_pk primary key (numero) );

Contratar

create table contratar( cuenta varchar2(20) not null, cliente varchar2(9) not null,

constraint contratar_pk primary key (cuenta, cliente) );

Fondo

create table fondo( isin varchar2(12) not null, nombre varchar2(50) not null, fechacreacion date not null,

constraint fondo_pk primary key (isin) );

Valor

create table valor( isin varchar2(12) not null, fecha date not null,

constraint valor_pk primary key (isin, fecha) );

Compra

create table compra( operacion integer not null,

(12)

cuenta varchar2(20) not null, fecha date not null,

ordenante varchar2(9) not null, participaciones number(38,5) not null, precio number(38,5) not null,

constraint compra_pk primary key (operacion) );

Venta

create table venta( operacion integer not null, cuenta varchar2(20) not null, fecha date not null,

ordenante varchar2(9) not null, precio number(38,5) not null,

constraint venta_pk primary key (operacion) );

Generar

create table generar( compra integer not null, venta integer not null,

participaciones number(38,5) not null,

constraint generar_pk primary key (compra,venta) );

2.2.2. Restricciones Cuenta

alter table cuenta add constraint cuenta_fk foreign key (isin) references fondo (isin);

Contratar

alter table contratar add constraint contratar_fk1 foreign key (cuenta) references cuenta (numero); alter table contratar add constraint contratar_fk2 foreign key (cliente) references cliente (dni);

Valor

alter table valor add constraint valor_fk foreign key (isin) references fondo (isin);

(13)

alter table compra add constraint compra_fk1 foreign key (cuenta) references cuenta (numero); alter table compra add constraint compra_fk2 foreign key (ordenante) references cliente (dni);

Venta

alter table venta add constraint venta_fk1 foreign key (cuenta) references cuenta (numero); alter table venta add constraint venta_fk2 foreign key (ordenante) references cliente (dni);

Generar

alter table generar add constraint generar_fk1 foreign key (compra) references compra (operacion); alter table generar add constraint generar_fk2 foreign key (venta) references venta (operacion);

2.2.3. Esquemas de secuencia y disparadores Compra

create sequence s_Compra increment by 1

start with 1;

create or replace trigger InsertarOperacionTcompra before insert on compra

for each row begin

select s_Compra.NEXTVAL into :NEW.operacion from dual;

end InsertarOperacionTcompra; /

Venta

create sequence s_Venta increment by 1

start with 1;

create or replace trigger InsertarOperacionTventa before insert on venta

for each row begin

select s_Venta.NEXTVAL into :NEW.operacion from dual;

end InsertarOperacionTventa; /

(14)

Al intentar crear la secuencia obtenemos el mensaje de privilegios insuficientes con lo cual mediante el enterprise manager aplicamos la sentencia sql: GRANT CREATE SEQUENCE TO "SGBD2"

2.3. Apartado (c)

2.3.1. Rellenar la tabla Cliente de SGBD2 a partir de Clientes de SGBD1

● Links Oracle:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.h tm#SQLRF01604

● select count(count(dni)) from sgbd1.clientes group by dni; ● Tenemos 39504 clientes diferentes en la tabla.

Insert into cliente (dni, nombre, apellido1, apellido2) (select dni, nombre, apellido1, apellido2 from sgbd1.clientes group by dni, nombre, apellido1, apellido2);

2.3.2. Rellenar la tabla Fondo de SGBD2 a partir de Valores de SGBD1

● El atributo fecha de creación de Fondo se asumirá cómo el valor en que se

crea la entrada en la base de datos obtenido mediante la función SYSDATE.

Insert into fondo (isin, nombre, fechacreacion) (select isin, nombre, sysdate from sgbd1.valores group by (isin, nombre));

2.3.3. Rellenar la tabla Cuenta de SGBD2 a partir de Clientes de SGBD1

Insert into cuenta (numero, isin, fechaapertura, fechabaja, numerotitulares) (select numero, isin, fechaapertura, fechabaja, count(numero) from sgbd1.clientes group by (numero, isin,

fechaapertura, fechabaja));

(15)

● Hay clientes para los cuales su ocurrencia para una determinada cuenta

está repetida: (select numero, dni from sgbd1.clientes group by (numero, dni) having count(*)>1; devuelve el valor de 5 filas).

Insert into contratar (cuenta, cliente) (select numero, dni from sgbd1.clientes group by (numero, dni));

2.3.5. Rellenar la tabla Valor de SGBD2 a partir de Valores de SGBD1

Insert into valor (isin, fecha, cotizacion) (select isin, fecha, cotizacion from sgbd1.valores);

Rellenar la tabla Compra de SGBD2 a partir de Compras de SGBD1

● Cómo mucho existe una operación de compra para una fecha concreta y

una determinada cuenta.

Insert into compra (cuenta, fecha, ordenante, participaciones, precio) (select cuenta, fecha, dni, participaciones, precio from sgbd1.compras);

2.3.6. Rellenar la tabla Venta de SGBD2 a partir de Ventas de SGBD1

● Puede haber varias operaciones que compartan todos los campos ya que

una venta puede estar compuesta de diversos grupos de participaciones (compras) sobre la misma cuenta.

Insert into venta (cuenta, fecha, ordenante, precio) (select cuenta, fecha, dni, precio from sgbd1.ventas group by (cuenta, fecha, dni, precio));

2.3.7. Rellenar la tabla Generar de SGBD2 a partir de Ventas de SGBD1

Insert into generar (compra, venta, participaciones) (select co.operacion, ve.operacion, vs.participaciones from compra co, venta ve, sgbd1.ventas vs

where vs.cuenta=co.cuenta and vs.fechacompra=co.fecha

and vs.cuenta=ve.cuenta and vs.fecha=ve.fecha and vs.dni=ve.ordenante and vs.precio=ve.precio );

Problemas:

SQL> select count(*)

(16)

3 where vs.cuenta=co.cuenta and vs.fechacompra=co.fecha

4 and vs.cuenta=ve.cuenta and vs.fecha=ve.fecha and vs.dni=ve.ordenante and vs.precio=ve.precio;

from compra co, sgbd1.ventas vs, venta ve *

ERROR en línea 2:

ORA-01652: no se ha podido ampliar el segmento temporal con 128 en el tablespace TEMP

ORA-27059: no se ha podido reducir el tamaño del archivo

OSD-04005: fallo de SetFilePointer(); no se ha podido leer del archivo O/S-Error: (OS 112) Espacio en disco insuficiente.

SQL> Insert into generar (compra, venta, participaciones) (select co.operacion, ve.operacion, vs.par

ticipaciones from compra co, venta ve, sgbd1.ventas vs 2 where vs.cuenta=co.cuenta and vs.fechacompra=co.fecha

3 and vs.cuenta=ve.cuenta and vs.fecha=ve.fecha and vs.dni=ve.ordenante and vs.precio=ve.precio

4 );

Insert into generar (compra, venta, participaciones) (select co.operacion, ve.operacion, vs.particip *

ERROR en línea 1:

ORA-01652: no se ha podido ampliar el segmento temporal con 128 en el tablespace TEMP

Solución:

● Modificar el tamaño del tablespace TEMP añadiendo un fichero extra de

1GB. Parece que la solución sigue sin funcionar.

● Añadir más ficheros. La configuración final queda de la siguiente manera:

• TEMP01.DBF en E:\ORACLE\PRODUCT\10.2.0\ORADATA\SGBD\ con 4 GB • TEMP02.DBF en E:\ORACLE\PRODUCT\10.2.0\ORADATA\SGBD\ con 1 GB • TEMP03.DBF en D:\ con 3 GB • TEMP04.DBF en D:\ con 3 GB

Aún así sigue sin funcionar; llego a la conclusión de que el producto cartesiano que se efectúa con las tres tablas es mucho más grande de lo que imaginé en un principio.

● Modificar la sentencia SQL para evitar el producto cartesiano de las 3 tablas

utilizando para ello subqueries correlacionados. La sentencia resulta:

Insert into generar (compra, venta, participaciones) (select (select co.operacion from compra co where vs.cuenta=co.cuenta and vs.fechacompra=co.fecha), (select ve.operacion from venta ve where vs.cuenta=ve.cuenta and vs.fecha=ve.fecha and vs.dni=ve.ordenante and

(17)

vs.precio=ve.precio), vs.participaciones from sgbd1.ventas vs);

● Cómo verificación:

select compra, co.participaciones, venta, ve.precio, ge.participaciones from compra co, venta ve, generar ge

where ge.compra=co.operacion and ge.venta=ve.operacion

group by compra, co.participaciones, venta, ve.precio, ge.participaciones order by compra, co.participaciones, venta, ve.precio, ge.participaciones;

2.4. Apartado (d)

Se tiene en cuenta el número de titulares de cada cuenta asociada a una compra o venta.

select comprado, compras, vendido, ventas, vendido - comprado as incremento_patrimonial from (select sum((ge.participaciones*ve.precio)/cu.numerotitulares) as vendido, count(*) as ventas from sgbd2.generar ge, sgbd2.venta ve, sgbd2.cliente cl, sgbd2.cuenta cu

where ge.venta=ve.operacion and ve.ordenante=cl.dni and cl.nombre=’Balbaner’ and cl.apellido1=’Trinidad’ and cl.apellido2=’Owens’ and ve.cuenta=cu.numero),

(select sum((co.participaciones*co.precio)/cu.numerotitulares) as comprado, count(*) as compras from sgbd2.compra co, sgbd2.cliente cl, sgbd2.cuenta cu

where co.cuenta=cu.numero and co.ordenante=cl.dni and cl.nombre='Balbaner' and cl.apellido1='Trinidad' and cl.apellido2='Owens');

Los resultados obtenidos muestran que el cliente de momento sufre pérdidas:

● Importe gastado en compras: 11732,5005

● Número de operaciones: 7

● Importe obtenido en ventas: 3125,00013

● Número de operaciones: 2

● Incremento patrimonial: - 8607,5004

2.4.1. Plan de ejecución Plan de Ejecución

(18)

---Plan hash value: 1815096825

---| Id ---| Operation ---| Name ---| Rows ---| Bytes ---| Cost (% CPU)| Time | ---| 0 ---| SELECT STATEMENT ---| ---| 1 ---| 52 ---| 315 (4)| 00:00:04 | | 1 | NESTED LOOPS | | 1 | 52 | 315 (4)| 00:00:04 | | 2 | VIEW | | 1 | 26 | 43 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 126 | | | | 4 | NESTED LOOPS | | 1 | 126 | 43 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 93 | 42 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 69 | 41 (0)| 00:00:01 |

| 7 | TABLE ACCESS FULL | GENERAR | 1 | 26 | 41 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| VENTA | 1 | 43 | 0 (0)| 00:00:01 |

|* 9 | INDEX UNIQUE SCAN | VENTA_PK | 1 | | 0 (0)| 00:00:01 |

| 10 | TABLE ACCESS BY INDEX ROWID | CUENTA | 1 | 24 | 1 (0)| 00:00:01 |

|* 11 | INDEX UNIQUE SCAN | CUENTA_PK | 1 | | 0 (0)| 00:00:01 |

(19)

|* 12 | TABLE ACCESS BY INDEX ROWID | CLIENTE | 1 | 33 | 1 (0)| 00:00:01 |

|* 13 | INDEX UNIQUE SCAN | CLIENTE_PK | 1 | | 0 (0)| 00:00:01 | | 14 | VIEW | | 1 | 26 | 272 (4)| 00:00:04 | | 15 | SORT AGGREGATE | | 1 | 102 | | | | 16 | NESTED LOOPS | | 1 | 102 | 272 (4)| 00:00:04 | |* 17 | HASH JOIN | | 1 | 78 | 271 (4)| 00:00:04 |

|* 18 | TABLE ACCESS FULL | CLIENTE | 1 | 33 | 57 (4)| 00:00:01 |

| 19 | TABLE ACCESS FULL | COMPRA | 107K| 4719K| 212 (3)| 00:00:03 |

| 20 | TABLE ACCESS BY INDEX ROWID | CUENTA | 1 | 24 | 1 (0)| 00:00:01 |

|* 21 | INDEX UNIQUE SCAN | CUENTA_PK | 1 | | 0 (0)| 00:00:01 |

3. Práctica 2

3.1. Organización física propuesta

Tabla Filas Tipo de Tabla Motivos

FONDO 14 NORMAL La tabla presenta un número muy bajo de filas

y no mejoraría para nada el rendimiento utilizar una estructura de tabla especial.

VALOR 34000 PARTICION

ADA (RANGO)

Este tipo de tabla (particionando por rango) ofrece un buen rendimiento para particionar datos de carácter histórico. En este caso definiremos 11 particiones una para cada uno

(20)

de los años para los que disponemos de cotización (de 1995 a 2005). CONTRAT AR 17800 0

IOT La tabla presenta pocas columnas y un número considerable de filas; cómo vimos en clase este puede ser el caso dónde las tablas IOT ofrezcan mayores beneficios.

CUENTA 59000 IOT Aunque presenta más columnas que el caso anterior conseguiremos un acceso más rápido (las filas están ordenadas) y ahorraremos espacio de almacenamiento (no duplicamos la clave primaria).

COMPRA 10700

0 CLUSTER (1)

CLIENTE 39000 CLUSTER (1)

Ambas tablas se relacionan por una restricción de integridad referencial (ordenante=dni) y suelen accederse juntas mediante un join.

GENERAR 57000 CLUSTER (2)

VENTA 49000 CLUSTER (2)

Ambas tablas se relacionan por una restricción de integridad referencial (venta=operacion) y suelen accederse juntas mediante un join. (1) Ambas tablas pertenecerán al mismo clúster.

(2) Ambas tablas pertenecerán al mismo clúster.

3.2. Atributos físicos de cada una de las tablas

Tabla Filas Tipo de Tabla Atributos

FONDO 14 NORMAL La tabla posee un número de valores pequeño y

con escasa (o nula) actividad de operaciones de inserción, eliminación o actualización, se recomienda por lo tanto un valor del 5% para PCTFREE y 20% para PCTUSED.

VALOR 34000 PARTICION

ADA (RANGO)

Existe una operación de inserción diaria y las actualizaciones podemos considerarlas cómo nulas, luego un valor del 5% para PCTFREE y 60% para PCTUSED es razonable. Se prioriza el uso adecuado del espacio libre.

CONTRAT AR

17800 0

IOT En este caso se reserva poco espacio para actualizaciones y un valor normal para

garantizar un adecuado uso del espacio. Un 5% para PCTFREE y 50% para PCTUSED parece

(21)

adecuado.

CUENTA 59000 IOT Cómo en el caso anterior, se reserva poco

espacio para actualizaciones y un valor normal para garantizar un adecuado uso del espacio. Un 5% para PCTFREE y 50% para PCTUSED parece razonable.

COMPRA 10700

0

CLUSTER (1)

CLIENTE 39000 CLUSTER (1)

Si consideramos las actualizaciones cómo prácticamente nulas, un valor del 5% para PCTFREE y 60% para PCTUSED es razonable. Se prioriza el uso adecuado del espacio libre.

GENERAR 57000 CLUSTER (2)

VENTA 49000 CLUSTER (2)

De nuevo, si consideramos las actualizaciones cómo prácticamente nulas, un valor del 5% para PCTFREE y 60% para PCTUSED es razonable. Se prioriza el uso adecuado del espacio libre.

(1) Ambas tablas pertenecerán al mismo clúster. (2) Ambas tablas pertenecerán al mismo clúster.

(3) No consideramos posibles cuestiones de concurrencia con lo que el parámetro INITRANS tomará el valor por defecto de 1.

(4) Al omitir la cláusula TABLESPACE consideraremos que, de momento, las tablas se crean en el tablespace por defecto del propietario de los objetos. Fuente: http://www.bd.cesma.usb.ve/ci5313/em04/docs/clase5-spdec01.pdf

3.3. Cláusula de Almacenamiento

Para las particionadas se dividirán los valores obtenidos para la tabla si fuera normal y se añadirá cómo cláusula storage que heredarán todas las particiones.

Para las IOT se procederá cómo en la sección siguiente variando únicamente el ENTRY SIZE correspondiente.

Comentarios:

• Los valores para “Datos Anteriores” han sido obtenidos de las vistas user_tables, user_segments y user_extents.

• Se aplica el valor de PCTINCREASE por defecto (0).

• El tamaño de bloque de la BD es de 8KB.

(22)

tablas normales y particionadas por rango:

• Fuente 1 : http://www.dmreview.com/article_sub.cfm?articleId=2139

• Fuente 2 : http://www.bd.cesma.usb.ve/ci5313/em04/docs/clase4-spdec01.pdf

o RS = tamaño medio de fila. o NR = número de filas en la tabla. o BS = tamaño de bloque.

o ES = tamaño de la extensión. o Asumiendo los valores:

 KCBH = 20 bytes  KTBBH = 48 bytes  KTBIT = 24 bytes  UB4 = 4 bytes  KDBH = 14 bytes  KDBT = 4 bytes

• Las fórmulas que se manejan son las siguientes:

o Tamaño_cabecera = KCBH + UB4 + KTBBH + KDBH = 20 + 4 + 48 + 14 = 86 bytes

o Espacio_disponible (FS)= techo((BS – Tamaño_cabecera)*(1-PCTFREE/100)) - KDBT

o Número_filas_bloque (NRB) = suelo (FS/RS) o Número_bloques (NB) = techo(NR / NRB)

o Número_extensiones (NE) = techo ((NB * BS)/ES) o Espacio_requerido = ES*NE;

• Para evitar fragmentación se intentará que la información inicial de la tabla quepa en una única extensión, lo que marcará el tamaño de INITIAL (Con lo que el parámetro NE deberá ser 1). Para el tamaño de NEXT se aplicará un porcentaje sobre INITIAL en base a las peculiaridades de la tabla.

Tabla Cláusula de Almacenamiento

FONDO RS = 63 bytes FS = techo((8192-86)*0.95)-4 = 7697 bytes NRB = suelo(7697/63) = 122 filas/bloque NB = techo(14/122) = 1 bloque Si NE = 1  1 = techo(1*8192 / ES) ES = 8192 bytes

(23)

luego: INITIAL = 16K NEXT = 16K MINEXTENTS = 1 MAXEXTENTS = 30 VALOR RS = 30 bytes FS = techo((8192-86)*0.95)-4 = 7697 bytes NRB = suelo(7697/30) = 256 filas/bloque NB = techo(34374/256) = 135 bloques Si NE = 1  1 = techo(135*8192 / ES) ES = 1080 KB

Luego, considerando 11 particiones dividimos el valor obtenido, y los valores para cada partición serán:

INITIAL = 100K NEXT = 100K MINEXTENTS = 1 MAXEXTENTS = 20 CONTRAT AR FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (2*1) + (0*2) + (9 + 20 bytes) = 39 bytes NB = 1.05*( 178481 /suelo(7250/39)) = 1014 bloques Luego: INITIAL = 8112 KB CUENTA FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (5*1) + (0*2) + (20 + 12+ 7 + 7 + 5 bytes) = 64 bytes NB = 1.05*( 59191 /suelo(7250/64)) = 551 bloques Luego: INITIAL = 4408 KB

(24)

COMPRA CLIENTE HSPACE = 8192 -20 – 4 – 48 – 14 = 8106 bytes SAFTD = 8106*(0.95 – 4*(2+1)*ROWSINBLOCK) S1 = 56 bytes S2 = 33 bytes

select avg(count(*)) from compra group by ordenante;  4 claves

SIZE = ((4*56)+(1*33)) + 19 + 1 + 9 + 2*(5) = 296 bytes CKPB = suelo((8106)/(296 + 2*5)) = 26 NB = techo(39504/26) = 1520 INITIAL = 12160 KB GENERAR VENTA HSPACE = 8192 -20 – 4 – 48 – 14 = 8106 bytes S1 = 16 bytes S2 = 50 bytes select avg(count(*)) from generar

group by compra;  2 claves

SIZE = ((2*16)+(1*50)) + 19 + 1 + 4 + 2*(3) = 112 bytes CKPB = suelo((8106)/(112 + 2*3)) = 68

NB = techo(49276/68) = 725 INITIAL = 5800 KB

3.4. Estimación del tamaño de los índices

Fuentes:

● httphttp://www.lsbu.ac.uk/oracle/oracle7/server/doc/SAD73/apA.htm#toc201 ● http://www.lsbu.ac.uk/oracle/oracle7/server/doc/SAD73/ch10.htm#settingsp ● Consideramos el PCTFREE por defecto para los índices (10%).

Fórmulas (tomando cómo base las de la sección anterior):

● Tamaño_cabecera = 113 + (24*INITTRANS) = 137 bytes

(25)

● Tamaño_entrada (ES) = 2 + 6 + F + V + D

● F = Bytes de las columnas que almacenan 127 bytes o menos. (1 byte) ● V = Bytes de las columnas que almacenan más de 127 bytes. (2 bytes) ● D = Espacio de datos combinado de cada una de las columnas.

● Número_bloques (NB)= 1.05 * ((Número de filas no nulas)/suelo(FS/ES))

VENTA FS = ((8192 - 137)*(0.9)) = 7250 bytes

D se ha calculado con “select avg(length(operacion)) from venta;”

ES = 8 + (1*1) + (0*2) + (4 bytes) = 13 bytes NB = 1.05*( 49276/suelo(7250/13)) = 93 bloques Luego: INITIAL = 744 KB GENERAR FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (2*1) + (0*2) + (10 bytes) = 20 bytes NB = 1.05*( 57881/suelo(7250/20)) = 168 bloques Luego: INITIAL = 1344 KB COMPRA FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (1*1) + (0*2) + (4 bytes) = 13 bytes NB = 1.05*( 107390 /suelo(7250/13)) = 203 bloques Luego: INITIAL = 1624 KB CLIENTE FS = ((8192 - 137)*(0.9)) = 7250 bytes

“select avg(length(dni)) from cliente;” = 9 bytes

ES = 8 + (1*1) + (0*2) + (9 bytes) = 18 bytes

(26)

NB = 1.05*( 39504 /suelo(7250/18)) = 104 bloques Luego: INITIAL = 832 KB FONDO FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (1*1) + (0*2) + (12 bytes) = 21 bytes NB = 1.05*( 14 /suelo(7250/21)) = 2 bloques Luego: INITIAL = 16 KB CONTRATAR FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (2*1) + (0*2) + (9 + 20 bytes) = 39 bytes NB = 1.05*( 178481 /suelo(7250/39)) = 1014 bloques Luego: INITIAL = 8112 KB VALOR FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (2*1) + (0*2) + (12 + 7 (tipo DATE)) = 29 bytes NB = 1.05*( 34374 /suelo(7250/29)) = 145 bloques Luego: INITIAL = 1160 KB CUENTA FS = ((8192 - 137)*(0.9)) = 7250 bytes ES = 8 + (1*1) + (0*2) + (20 bytes) = 29 bytes NB = 1.05*( 59191 /suelo(7250/29)) = 249 bloques Luego:

(27)

INITIAL = 1992 KB

3.5. Creación de los dos tablespaces

El primero para datos tendrá un tamaño de 100MB ya que la suma de los datos (sus INITIAL) alcanza los 30596KB y se dejará un margen de seguridad.

Utilizaremos aquí un tamaño de bloque de 16KB ya que por regla general las tablas albergan una cantidad de datos considerable.

El segundo para índices tendrá un tamaño de 8MB ya que la suma de los datos (sus INITIAL) alcanza los 5720KB y se dejará un margen de seguridad. Aquí consideraremos un tamaño de bloque de 4KB ya que los objetos contendrán menos información que en caso de los datos.

Lo primero será definir los parámetros DB_nK_CACHE_SIZE para n = 4 y n = 16:

Fuente: http://www.oracle-base.com/articles/9i/MultipleBlockSizes.php

alter system set DB_4K_CACHE_SIZE=16M scope=SPFILE; alter system set DB_16K_CACHE_SIZE=16M scope=SPFILE;

A continuación:

SQL> connect sys/oracle as sysdba Conectado.

SQL> SHUTDOWN IMMEDIATE Base de datos cerrada.

Base de datos desmontada. Instancia ORACLE cerrada. SQL> STARTUP

Instancia ORACLE iniciada.

Total System Global Area 612368384 bytes Fixed Size 1250428 bytes

Variable Size 159386500 bytes Database Buffers 444596224 bytes Redo Buffers 7135232 bytes Base de datos montada.

Base de datos abierta.

(28)

Tablespaces:

CREATE SMALLFILE TABLESPACE "DADPRACT" DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SGBD\DADPRACT01.DBF' SIZE 40M AUTOEXTEND ON NEXT 2M MAXSIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 16384

CREATE SMALLFILE TABLESPACE "INDPRACT" DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SGBD\INDPRACT01.DBF' SIZE 8M AUTOEXTEND ON NEXT 512K MAXSIZE 20M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 4096

3.6. Creación del usuario SGBD3

CREATE USER "SGBD3" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "DADPRACT" QUOTA UNLIMITED ON "INDPRACT" QUOTA UNLIMITED ON "USERS"

ACCOUNT UNLOCK

GRANT SELECT ON "SGBD1"."CLIENTES" TO "SGBD3" GRANT SELECT ON "SGBD1"."COMPRAS" TO "SGBD3" GRANT SELECT ON "SGBD1"."VALORES" TO "SGBD3" GRANT SELECT ON "SGBD1"."VENTAS" TO "SGBD3" GRANT "CONNECT" TO "SGBD3"

GRANT CREATE TABLE TO "SGBD3"

GRANT CREATE CLUSTER TO "SGBD3" GRANT CREATE SEQUENCE TO "SGBD3" GRANT CREATE TRIGGER TO "SGBD3";

3.7. Creación de las tablas para SGBD3

Link:

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96521/clustrs.htm

create table fondo(

isin varchar2(12) not null, nombre varchar2(50) not null, fechacreacion date not null,

constraint fondo_pk primary key (isin) )

(29)

PCTFREE 5 PCTUSED 20

STORAGE (INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 30);

create table valor(

isin varchar2(12) not null, fecha date not null,

constraint valor_pk primary key (isin, fecha) )

TABLESPACE DADPRACT PCTFREE 5 PCTUSED 60

STORAGE (INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 20)

PARTITION BY RANGE ( fecha)

(PARTITION valor_p1 VALUES LESS THAN (TO_DATE('01/01/1996', 'DD/MM/YYYY')),

PARTITION valor_p2 VALUES LESS THAN (TO_DATE('01/01/1997', 'DD/MM/YYYY')),

PARTITION valor_p11 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')));

create table contratar(

cuenta varchar2(20) not null, cliente varchar2(9) not null,

constraint contratar_pk primary key (cuenta, cliente) )

ORGANIZATION INDEX TABLESPACE INDPRACT

(30)

create table cuenta(

numerotitulares number(5) not null,

constraint cuenta_pk primary key (numero) )

ORGANIZATION INDEX TABLESPACE INDPRACT

STORAGE (INITIAL 4408K NEXT 4408K MINEXTENTS 1 MAXEXTENTS 10); CREATE CLUSTER compra_cliente

(dni varchar2(9)) PCTUSED 60 PCTFREE 5 SIZE 296 TABLESPACE DADPRACT STORAGE (INITIAL 12160K NEXT 12160K MINEXTENTS 1 MAXEXTENTS 10);

CREATE INDEX compra_cliente_indice

ON CLUSTER compra_cliente TABLESPACE INDPRACT STORAGE (INITIAL 2400K NEXT 2400K MINEXTENTS 1 MAXEXTENTS 10) PCTFREE 5;

Nota: se han aproximado los valores del INIT para un índice sobre compra añadido al de cliente.

create table compra(

operacion integer not null, cuenta varchar2(20) not null, fecha date not null,

ordenante varchar2(9) not null, participaciones number(38,5) not null, precio number(38,5) not null,

constraint compra_pk primary key (operacion), constraint compra_un unique (cuenta, fecha) )

(31)

create table cliente(

dni varchar2(9) not null, nombre varchar2(15) not null, apellido1 varchar2(20) not null, apellido2 varchar2(20),

constraint cliente_pk primary key (dni) )

CLUSTER compra_cliente (dni);

CREATE CLUSTER generar_venta

(compra integer) PCTUSED 60 PCTFREE 5 SIZE 112 TABLESPACE DADPRACT STORAGE (INITIAL 5800K NEXT 5800K MINEXTENTS 1 MAXEXTENTS 10);

CREATE INDEX generar_venta_indice

ON CLUSTER generar_venta TABLESPACE INDPRACT STORAGE (INITIAL 2100K NEXT 2100K MINEXTENTS 1 MAXEXTENTS 10) PCTFREE 5;

Nota: se han aproximado los valores del INIT para un índice sobre generar añadido al de venta.

create table generar(

compra integer not null, venta integer not null,

participaciones number(38,5) not null,

constraint generar_pk primary key (compra,venta) )

CLUSTER generar_venta (compra);

create table venta(

(32)

cuenta varchar2(20) not null, fecha date not null,

ordenante varchar2(9) not null, precio number(38,5) not null,

constraint venta_pk primary key (operacion),

constraint venta_un unique (cuenta, fecha, ordenante) )

CLUSTER generar_venta (operacion);

3.8. Crear las restricciones

3.8.1. Cuenta

alter table cuenta add constraint cuenta_fk foreign key (isin) references fondo (isin);

3.8.2. Contratar

alter table contratar add constraint contratar_fk1 foreign key (cuenta) references cuenta (numero); alter table contratar add constraint contratar_fk2 foreign key (cliente) references cliente (dni);

3.8.3. Valor

alter table valor add constraint valor_fk foreign key (isin) references fondo (isin);

3.8.4. Compra

alter table compra add constraint compra_fk1 foreign key (cuenta) references cuenta (numero); alter table compra add constraint compra_fk2 foreign key (ordenante) references cliente (dni);

3.8.5. Venta

alter table venta add constraint venta_fk1 foreign key (cuenta) references cuenta (numero); alter table venta add constraint venta_fk2 foreign key (ordenante) references cliente (dni);

3.8.6. Generar

alter table generar add constraint generar_fk1 foreign key (compra) references compra (operacion); alter table generar add constraint generar_fk2 foreign key (venta) references venta (operacion);

(33)

3.9. Crear los esquemas de secuencia y disparadores

3.9.1. Compra

create sequence s_Compra increment by 1

start with 1;

create or replace trigger InsertarOperacionTcompra before insert on compra

for each row begin

select s_Compra.NEXTVAL into :NEW.operacion from dual;

end InsertarOperacionTcompra; /

3.9.2. Venta

create sequence s_Venta increment by 1

start with 1;

create or replace trigger InsertarOperacionTventa before insert on venta

for each row begin

select s_Venta.NEXTVAL into :NEW.operacion from dual;

end InsertarOperacionTventa; /

3.10. Cargar los datos

Insert into cliente (dni, nombre, apellido1, apellido2) (select dni, nombre, apellido1, apellido2 from sgbd1.clientes group by dni, nombre, apellido1, apellido2);

Insert into fondo (isin, nombre, fechacreacion) (select isin, nombre, sysdate from sgbd1.valores group by (isin, nombre));

Insert into cuenta (numero, isin, fechaapertura, fechabaja, numerotitulares) (select numero, isin, fechaapertura, fechabaja, count(numero) from sgbd1.clientes group by (numero, isin,

fechaapertura, fechabaja));

Insert into contratar (cuenta, cliente) (select numero, dni from sgbd1.clientes group by (numero, dni));

(34)

Insert into valor (isin, fecha, cotizacion) (select isin, fecha, cotizacion from sgbd1.valores); Insert into compra (cuenta, fecha, ordenante, participaciones, precio) (select cuenta, fecha, dni, participaciones, precio from sgbd1.compras);

Insert into venta (cuenta, fecha, ordenante, precio) (select cuenta, fecha, dni, precio from sgbd1.ventas group by (cuenta, fecha, dni, precio));

Insert into generar (compra, venta, participaciones) (select (select co.operacion from compra co where vs.cuenta=co.cuenta and vs.fechacompra=co.fecha), (select ve.operacion from venta ve where vs.cuenta=ve.cuenta and vs.fecha=ve.fecha and vs.dni=ve.ordenante and

vs.precio=ve.precio), vs.participaciones from sgbd1.ventas vs);

3.10.1. Problema

No había manera de cargar los datos en generar, tardaba demasiado, la solución ha pasado por definir dos restricciones de tipo UNIQUE en compra (cuenta, fecha) y venta (cuenta, fecha, ordenante).

Por último resta ubicar los índices en el tablespace correspondiente:

alter index compra_un rebuild tablespace indpract; alter index venta_pk rebuild tablespace indpract; alter index venta_un rebuild tablespace indpract; alter index generar_pk rebuild tablespace indpract; alter index compra_pk rebuild tablespace indpract; alter index cliente_pk rebuild tablespace indpract; alter index valor_pk rebuild tablespace indpract; alter index fondo_pk rebuild tablespace indpract;

4. Optimización de la consulta

Link: http://www.lcc.uma.es/~bds/adminbd/apuntes/ABD4_Oracle.pdf

select comprado, compras, vendido, ventas, vendido - comprado as incremento_patrimonial from (select sum((ge.participaciones*ve.precio)/cu.numerotitulares) as vendido, count(*) as ventas from generar ge, venta ve, cliente cl, cuenta cu

where ge.venta=ve.operacion and ve.ordenante=cl.dni and cl.nombre=’Balbaner’ and cl.apellido1=’Trinidad’ and cl.apellido2=’Owens’ and ve.cuenta=cu.numero),

(select sum((co.participaciones*co.precio)/cu.numerotitulares) as comprado, count(*) as compras from compra co, cliente cl, cuenta cu

(35)

and cl.apellido1='Trinidad' and cl.apellido2='Owens');

Cómo se aprecia en la consulta disponemos ya de índices para casi todo ya que para las claves primarias se define automáticamente un índice. La única parte que si admitiría la definición de un índice en vistas a aumentar la eficiencia es la identificación del cliente a partir de su nombre, apellido1 y apellido2 (valores que son únicos en la tabla cliente); dicho lo cual el objetivo se centra en encontrar un índice cliente (nombre, apellido1, apellido2):

- Un índice de mapa de bits no parece eficiente dado que el número de valores posibles de la clave es muy alto.

- Un índice B-Tree parece apropiado ya que las columnas a indexar tienen cardinalidad elevada.

Parte de ventas:

CREATE INDEX "SGBD3"."CLIENTE_IND1" ON "SGBD3"."CLIENTE" ("NOMBRE", "APELLIDO1", "APELLIDO2", "DNI") TABLESPACE "INDPRACT"

CREATE INDEX "SGBD3"."VENTA_IND1" ON "SGBD3"."VENTA" ("ORDENANTE", "OPERACION") TABLESPACE "INDPRACT"

CREATE INDEX "SGBD3"."VENTA_IND1" ON "SGBD3"."VENTA" ("ORDENANTE", "OPERACION", "PRECIO", "CUENTA") TABLESPACE "INDPRACT"

CREATE INDEX "SGBD3"."GENERAR_IND1" ON "SGBD3"."GENERAR" ("VENTA", "PARTICIPACIONES") TABLESPACE "INDPRACT"

Todos los índices son b-tree ya que no existe una lista de valores corta sobre la que definir un mapa de bits. Además se incluyen las columnas que albergan los valores que serían necesarios tras obtener las filas (dni,

participaciones) para ahorrarnos un acceso a la tabla correspondiente.

4.1.1. Nuevo plan de ejecución

---| 0 ---| SELECT STATEMENT ---| ---| 1 ---| 52 ---| 13 (0)---| 00:00:01 |

(36)

| 1 | NESTED LOOPS | | 1 | 52 | 13 (0)| 00:00:01 | | 2 | VIEW | | 1 | 26 | 8 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 112 | | | | 4 | NESTED LOOPS | | 1 | 112 | 8 (0)| 00:00:01 | | 5 | NESTED LOOPS | | 1 | 100 | 6 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 76 | 5 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | CLIENTE_IND1 | 1 | 33 | 3 (0)| 00:00:01 |

|* 8 | INDEX RANGE SCAN | VENTA_IND1 | 2 | 86 | 2 (0)| 00:00:01 |

|* 9 | INDEX UNIQUE SCAN | CUENTA_PK | 1 | 24 | 1 (0)| 00:00:01 |

|* 10 | INDEX RANGE SCAN | GENERAR_IND1 | 1 | 12 | 2 (0)| 00:00:01 | | 11 | VIEW | | 1 | 26 | 5 (0)| 00:00:01 | | 12 | SORT AGGREGATE | | 1 | 102 | | | | 13 | NESTED LOOPS | | 1 | 102 | 5 (0)| 00:00:01 | | 14 | NESTED LOOPS | | 1 | 78 | 4 (0)|

(37)

00:00:01 |

|* 15 | INDEX RANGE SCAN | CLIENTE_IND1 | 1 | 33 | 3 (0)| 00:00:01 |

|* 16 | TABLE ACCESS CLUSTER| COMPRA | 3 | 135 | 1 (0)| 00:00:01 |

|* 17 | INDEX UNIQUE SCAN | CUENTA_PK | 1 | 24 | 1 (0)| 00:00:01 |

5. ANEXO 1: Logs arrojados por SQL Loader

5.1. control.log de la tabla CLIENTES

Archivo de Control: control.txt Archivo de Datos: clientes.txt Archivo de Errores: clientes.bad Desechar Archivo: ninguno especificado (Permitir todos los registros desechados) Número a cargar: ALL

Número a ignorar: 0 Errores permitidos: 50

Matriz de enlace: 64 filas, máximo de 256000 bytes Continuación: ninguno especificado

Ruta de acceso utilizada: Convencional

Tabla CLIENTES, cargada de cada registro lógico. Opción INSERT activa para esta tabla: APPEND

Nombre Columna Posición Long Term Entorno Tipo de Dato --- --- --- ---- ----

---DNI 1:9 9 CHARACTER NOMBRE 10:24 15 CHARACTER APELLIDO1 25:44 20 CHARACTER APELLIDO2 45:64 20 CHARACTER

(38)

NULL if APELLIDO2 = BLANKS

NUMERO 65:84 20 CHARACTER ISIN 85:96 12 CHARACTER

FECHAAPERTURA 97:115 19 DATE DD/MM/YYYY HH24:MI:SS FECHABAJA 116:134 19 DATE DD/MM/YYYY HH24:MI:SS Tabla CLIENTES:

178486 Filas se ha cargado correctamente. 0 Filas no cargada debido a errores de datos.

0 Filas no cargada porque todas las cláusulas WHEN han fallado. 0 Filas no cargada porque todos los campos eran nulos.

Espacio asignado a matriz de enlace: 9856 bytes (64 filas) Bytes de buffer de lectura: 1048576

Total de registros lógicos ignorados: 0 Total de registros lógicos leídos: 178486 Total de registros lógicos rechazados: 0 Total de registros lógicos desechados: 0 La ejecución empezó en Lun Abr 09 21:52:14 2007 La ejecución terminó en Lun Abr 09 21:53:03 2007 Tiempo transcurrido: 00:00:48.31

Tiempo de CPU: 00:00:02.66

5.2. control.log de la tabla VALORES

Archivo de Control: control.txt Archivo de Datos: valores.txt Archivo de Errores: valores.bad Desechar Archivo: ninguno especificado (Permitir todos los registros desechados) Número a cargar: ALL

(39)

Continuación: ninguno especificado Ruta de acceso utilizada: Convencional

Tabla VALORES, cargada de cada registro lógico. Opción INSERT activa para esta tabla: APPEND

---ISIN 1:13 13 CHARACTER NOMBRE 14:64 51 CHARACTER FECHA 65:84 20 DATE DD/MM/YYYY COTIZACION 85:122 38 CHARACTER Cadena SQL para la columna: "TO_NUMBER(:cotizacion)"

Tabla VALORES:

Total de registros lógicos ignorados: 0 Total de registros lógicos leídos: 34374 Total de registros lógicos rechazados: 0 Total de registros lógicos desechados: 0 La ejecución empezó en Mar Abr 10 00:32:36 2007 La ejecución terminó en Mar Abr 10 00:32:45 2007 Tiempo transcurrido: 00:00:09.48

Tiempo de CPU: 00:00:00.31

5.3. control.log de la tabla COMPRAS

Archivo de Control: control.txt Archivo de Datos: compras.txt

(40)

Archivo de Errores: compras.bad Desechar Archivo: ninguno especificado (Permitir todos los registros desechados) Número a cargar: ALL

Ruta de acceso utilizada: Convencional

Tabla COMPRAS, cargada de cada registro lógico. Opción INSERT activa para esta tabla: APPEND

---DNI FIRST * ; " CHARACTER NOMBRE NEXT * ; " CHARACTER APELLIDO1 NEXT * ; " CHARACTER APELLIDO2 NEXT * ; O(") CHARACTER ISIN NEXT * ; " CHARACTER FONDO NEXT * ; " CHARACTER CUENTA NEXT * ; " CHARACTER

FECHA NEXT * ; DATE DD/MM/YYYY HH24:MI:SS PARTICIPACIONES NEXT * ; CHARACTER Cadena SQL para la columna: "TO_NUMBER(:participaciones)" PRECIO NEXT * WHT CHARACTER Cadena SQL para la columna: "TO_NUMBER(:precio)"

Tabla COMPRAS:

Total de registros lógicos ignorados: 0 Total de registros lógicos leídos: 107390 Total de registros lógicos rechazados: 0 Total de registros lógicos desechados: 0 La ejecución empezó en Mar Abr 10 00:35:58 2007 La ejecución terminó en Mar Abr 10 00:36:40 2007

(41)

Tiempo transcurrido: 00:00:42.35 Tiempo de CPU: 00:00:03.07

5.4. control.log de la tabla VENTAS

Archivo de Control: control.txt Archivo de Datos: ventas.txt Archivo de Errores: ventas.bad Desechar Archivo: ninguno especificado (Permitir todos los registros desechados) Número a cargar: ALL

Ruta de acceso utilizada: Convencional Tabla VENTAS, cargada de cada registro lógico. Opción INSERT activa para esta tabla: APPEND

---DNI FIRST * ; " CHARACTER NOMBRE NEXT * ; " CHARACTER APELLIDO1 NEXT * ; " CHARACTER APELLIDO2 NEXT * ; O(") CHARACTER ISIN NEXT * ; " CHARACTER FONDO NEXT * ; " CHARACTER CUENTA NEXT * ; " CHARACTER

FECHA NEXT * ; DATE DD/MM/YYYY HH24:MI:SS PRECIO NEXT * ; CHARACTER

Cadena SQL para la columna: "TO_NUMBER(:precio)"

PARTICIPACIONES NEXT * ; CHARACTER Cadena SQL para la columna: "TO_NUMBER(:participaciones)"

(42)

Tabla VENTAS:

Total de registros lógicos ignorados: 0 Total de registros lógicos leídos: 57881 Total de registros lógicos rechazados: 0 Total de registros lógicos desechados: 0 La ejecución empezó en Mar Abr 10 00:39:31 2007 La ejecución terminó en Mar Abr 10 00:39:51 2007 Tiempo transcurrido: 00:00:19.72