Fragmentación de una base de datos en Oracle 11g

UNIVERSIDAD TÉCNICA DE AMBATO Facultad de Ingeniería en Sistemas, Electrónica e Industrial Ingeniería en Sistemas Computacionales e Informáticos

Título:

Fragmentación de una Base de Datos en el SGBD Oracle

Carrera:

Ingeniería en Sistemas Computacionales e Informáticos

Área Académica:

Desarrollo de Software

Línea de Investigación:

Base de Datos

Ciclo Académico y Paralelo:

Octavo

Alumno:

Landa Patricio

Módulo y Docente:

Administración de Bases de Datos Ing. Urvina Renato

UNIVERSIDAD TÉCNICA DE AMBATO FACULTAD DE INGENIERÍA EN SISTEMAS, ELECTRÓNICA E INDUSTRIAL PERÍODO ACADÉMICO: ABRIL/2016 – SEPTIEMBRE/2016

I.

INFORME DEL PROYECTO 1. YY 1.1 Título Fragmentación de una base de Datos con Oracle Database 11g. 1.2 Objetivos Creación de Tablespaces en Oracle Database 11g. Implementación de la BD física a partir del MER proporcionado. Realizar la fragmentación de la Base de Datos implementada. 1.3 Resumen Esta práctica está orientada a la fragmentación de una base de datos, para lo cual haremos uso de Oracle Database 11g Release 2 versión Enterprise el cual está instalado en un entorno Windows en un inicio crearemos varios tablespaces en los diferentes discos que tenemos configurados como RAID 10, llegaremos a la implementación de una base de datos proporcionado por el docente en cual se ilustra en la Figura 1 a partir del cual generaremos el Modelo Relacional haciendo uso de una herramienta case como Power Designer con el propósito de facilitar y agilizar el trabajo, también se realizaran las pruebas respectivas para constatar que el trabajo se haya llevado de la manera correcta. 1.4 Palabras clave: Fragmentación, Partición, Tablespace, Oracle, Virtual. 1.5 Introducción Particionado de Tablas en Oracle Básicamente, el particionado se realiza utilizando una clave de particionado (partitioning key), que determina en que partición de las existentes en la tabla van a residir los datos que se insertan. Oracle también permite realizar el particionado de índices y de tablas organizadas por índices. Cada partición además puede tener sus propias propiedades de almacenamiento. Las tablas particionadas aparecen en el sistema como una única tabla, realizando el sistema la gestión automática de lectura y escritura en cada una de las particiones (excepto para el caso de la partición de Sistema introducida en la versión 11g). Tipos de Particionado en Oracle


Particionado Range: la clave de particionado viene determinada por un rango de valores, que determina la partición donde se almacenara un valor. Particionado Hash: la clave de particionado es una función hash, aplicada sobre una columna, que tiene como objetivo realizar una distribución equitativa de los registros sobre las diferentes particiones. Es útil para particionar tablas donde no hay unos criterios de particionado claros, pero en la que se quiere mejor el rendimiento. Particionado List: la clave de particionado es una lista de valores, que determina cada una de las particiones. Particionado Composite: los particionados anteriores eran del tipo simples (single o one-level), pues utilizamos un único método de particionado sobre una o más columnas. Oracle nos permite utilizar métodos de particionado compuestos, utilizando un primer particionado de un tipo determinado, y luego para cada partición, realizar un segundo nivel de particionado utilizando otro método. Las combinaciones son las siguientes (se han ido ampliando conforme han ido avanzando las versiones): range-hash, range-list, range-range, list-range, list-list, listhash y hash-hash (introducido en la versión 11g). Particionado Interval: tipo de particionado introducido igualmente en la versión 11g. En lugar de indicar los rangos de valores que van a determinar cómo se realiza el particionado, el sistema automáticamente creara las particiones cuando se inserte un nuevo registro en la b.d. Las técnicas de este tipo disponible son Interval, Interval List, Interval Range e Interval Hash (por lo que el particionado Interval es complementario a las técnicas de particionado vistas anteriormente). Particionado por Referencia: El particionamiento por referencia permite que las tablas relacionadas por claves foráneas sean lógicamente equi-particionadas. La tabla hija es particionada utilizando la misma clave de partición como la tabla padre sin tener que duplicar las columnas de clave. Las operaciones de mantenimiento de la partición realizadas en la tabla padre se reflejan en la tabla hija, pero operaciones de mantenimiento de partición no están permitidos en la tabla secundaria o tablas hija.


Por ejemplo una tabla padre PEDIDOS es particionada por rango en la columna PEDIDO_DATE; su tabla hija PEDIDO_ITEMS no contienen una columna PEDIDO_DATE pero puede ser particionada en función de la tabla PEDIDOS.

1.6 Materiales y Metodología Partimos del Modelo Entidad Relación (Figura 1).

Figura 1. Modelo Entidad Relación Obtenemos el Modelo Relacional diseñado con Power Designer (Figura 2).


Figura 2. Modelo Relacional A partir de nuestro Modelo Relacional y haciendo uso de las funcionalidades de Power Designer generaremos el código SQL para implementarlo en el SGBD Oracle. En el Sistema Operativo tenemos los discos configurados como RAID 10 esta parte es un requisito para empezar a realizar la implementación de nuestra base de datos ya que los ficheros de los diferentes Tablespaces que vamos a crear se guardaran en dichos discos según elijamos guardar en uno u otro, en este caso como se trata de un Sistema Windows los discos están etiquetamos como la unidad G y D, los Tablespaces requeridos para esta práctica y los discos donde serán almacenados cada uno de estos se detallan a continuación. Nombre Tablespace TN

Disco lógico ld1 (G) 512MB

T1, T2, T3, TA, TB, TC, TV

ld2 (D) 512MB

CREACION DE TABLESPACE Sintaxis para la creación de un tablespace simple, las partes marcadas en negrita son las que deberemos de cambiar. CREATE TABLESPACE [NOMBRE_TABLESPACE] DATAFILE '[PATH ABSOLUTO]\NOMBRE_ARCHIVO.DBF' SIZE [TAMAÑO] ONLINE; Ejemplo: Creación del Tablespace requeridos. CREATE TABLESPACE TN DATAFILE 'G:\TN.DBF' SIZE 2M ONLINE; CREATE TABLESPACE T1 DATAFILE 'D:\T1.DBF' SIZE 2M ONLINE; CREATE TABLESPACE T2 DATAFILE 'D:\T2.DBF' SIZE 2M ONLINE; CREATE TABLESPACE T3 DATAFILE 'D:\T3.DBF' SIZE 2M ONLINE;


CREATE TABLESPACE TA DATAFILE 'D:\TA.DBF' SIZE 2M ONLINE; CREATE TABLESPACE TB DATAFILE 'D:\TB.DBF' SIZE 2M ONLINE; CREATE TABLESPACE TC DATAFILE 'D:\TC.DBF' SIZE 2M ONLINE; CREATE TABLESPACE TV DATAFILE 'D:\TV.DBF' SIZE 2M ONLINE; Una vez creados los tablespaces necesarios queda listo el ambiente para realizar la implementación de nuestra base de datos, cabe recalcar que la fragmentación de las tablas se especifica al momento de crearlas en la parte final de la sentencia de creación de tabla, tal como se detalla a continuación. Nota: Solo se ha considerado documentar la implementación de aquellas tablas que requieren ser particionadas o aquellas que se deben de almacenar en tablespaces específicos. TABLA AREA crea te table AREA ( DESCRIPCION VARCHAR2(200) not null, cons traint PK_AREA pri mary key (DESCRIPCION) ) PARTITION BY LIST(DESCRIPCION) ( PARTITION PBSD VALUES('BASE DE DATOS') TABLESPACE TA, PARTITION PREDES VALUES('HARDWARE Y REDES') TABLESPACE TB, PARTITION PDESARROLLO VALUES('DESARROLLO SOFTWARE') TABLESPACE TC, PARTITION PDEFAULTAREA VALUES(DEFAULT) TABLESPACE TN); TABLA CAMPUS

TIPO DE FRAGMENTACION Partición por valor: Base de Datos->TA Hardware y Redes->TB Desarrollo Software->TC Default->TN

crea te table CAMPUS ( CODCAM INTEGER not null, CAMPUS VARCHAR2(100) not null, cons traint PK_CAMPUS pri mary key (CODCAM) ) PARTITION BY LIST(CAMPUS) ( PARTITION P1 VALUES('HUACHI') TABLESPACE T1, PARTITION P2 VALUES('INGAHURCO') TABLESPACE T2, PARTITION P3 VALUES('QUEROCHACA') TABLESPACE T3, PARTITION PDEFAULT VALUES(DEFAULT) TABLESPACE TN ); TABLA ASIGNATURA

Partición por valor:

crea te table ASIGNATURA ( CODAS VARCHAR2(5) not null, CODCAM INTEGER not null, DESCRIPCION VARCHAR2(200) not null, NOMBRE VARCHAR2(100) not null, cons traint PK_ASIGNATURA primary key (CODAS), cons traint FK_ASIGNATURA_CAMPUS FOREIGN KEY(CODCAM) REFERENCES CAMPUS(CODCAM) ON DELETE CASCADE,

Partición por Referencia:

Huachi->T1 Ingahurco->T2 Querochaca->T3 Default->TN

La entidad Asignaturas es particionada de acuerdo al valor del área a la que pertenece.

UNIVERSIDAD TÉCNICA DE AMBATO FACULTAD DE INGENIERÍA EN SISTEMAS, ELECTRÓNICA E INDUSTRIAL PERÍODO ACADÉMICO: ABRIL/2016 – SEPTIEMBRE/2016 cons traint FK_ASIGNATURA_AREA FOREIGN KEY(DESCRIPCION) REFERENCES AREA(DESCRIPCION) ON DELETE CASCADE ) PARTITION BY REFERENCE(FK_ASIGNATURA_AREA); TABLA HOBBIE crea te table HOBBIE ( CIEST CHAR(10) NOT NULL, DESCHOBBIE VARCHAR2(200), CONSTRAINT FK_HOBBIE_ESTUDIANTE FOREIGN KEY(CIEST) REFERENCES ESTUDIANTE(CIEST) ON DELETE CASCADE )TABLESPACE TV; TABLA PSICOLOGICO

Entidad que se almacenará en el Tablespace TV.

crea te table PSICOLOGICO ( CIEST CHAR(10) NOT NULL, DESCPSIC VARCHAR2(200), CONSTRAINT FK_PSICOLOGICO_ESTUDIANTE FOREIGN KEY(CIEST) REFERENCES ESTUDIANTE(CIEST) ON DELETE CASCADE )TABLESPACE TV; TABLA SALUD


crea te table SALUD ( CIEST CHAR(10) NOT NULL, DESCSALUD VARCHAR2(150), CONSTRAINT FK_SALUD_ESTUDIANTE FOREIGN KEY(CIEST) REFERENCES ESTUDIANTE(CIEST) ON DELETE CASCADE )TABLESPACE TV; TABLA FORMACIÓN


crea te table FORMACION ( CI CHAR(10) not null, ESPECIALIDAD VARCHAR2(100) not null, NIVEL VARCHAR2(50) not null, ANO INTEGER, UNIVERSIDAD VARCHAR2(200) not null, TITULO VARCHAR2(200) not null, PAIS VARCHAR2(150) not null, CONSTRAINT FK_FORMACION_DOCENTE FOREIGN KEY(CI) REFERENCES DOCENTE(CI) ON DELETE CASCADE )TABLESPACE TV; TABLA TELEFONO (DOCENTE)


crea te table TELEFONO ( CI CHAR(10) not null, NUMERO CHAR(10) not null, CONSTRAINT FK_TELEFONO_DOCENTE FOREIGN KEY(CI) REFERENCES DOCENTE(CI) ON DELETE CASCADE )TABLESPACE TV; TABLA NOTAS


crea te table NOTAS ( CODAS VARCHAR2(5) not null, CIEST CHAR(10) not null, CI CHAR(10) not null, NOTA1 DOUBLE PRECISION, NOTA2 DOUBLE PRECISION, PROMEDIO DOUBLE PRECISION GENERATED ALWAYS AS

Partición por Referencia: Las notas se particionarán de acuerdo al valor de la asignatura y del área a la que pertence.

UNIVERSIDAD TÉCNICA DE AMBATO FACULTAD DE INGENIERÍA EN SISTEMAS, ELECTRÓNICA E INDUSTRIAL PERÍODO ACADÉMICO: ABRIL/2016 – SEPTIEMBRE/2016 ( (NOTA1+NOTA2)/2 )VIRTUAL, CONSTRAINT FK_NOTAS_DOCENTE FOREIGN KEY(CI) REFERENCES DOCENTE(CI) ON DELETE CASCADE, CONSTRAINT FK_NOTAS_ESTUDIANTE FOREIGN KEY(CIEST) REFERENCES ESTUDIANTE(CIEST) ON DELETE CASCADE, CONSTRAINT FK_NOTAS_ASIGNATURA FOREIGN KEY(CODAS) REFERENCES ASIGNATURA(CODAS) ON DELETE CASCADE ) PARTITION BY REFERENCE(FK_NOTAS_ASIGNATURA);

INSERCCIÓN DE DATOS Para la inserción de los datos no se han considerado todas las tablas solamente las más principales. NOMBRE TABLA AREA

CAMPUS

ASIGNATURA

ESTUDIANTE

HOBBIE

PSICOLOGICO

SENTENCIA SQL INSERT ALL INTO AREA VALUES ('BASE DE DATOS') INTO AREA VALUES ('HARDWARE Y REDES') INTO AREA VALUES ('DESARROLLO SOFTWARE') SELECT * FROM dua l; INSERT ALL INTO CAMPUS VALUES (1,'HUACHI') INTO CAMPUS VALUES (2,'INGAHURCO') INTO CAMPUS VALUES (3,'QUEROCHACA') SELECT * FROM dua l; INSERT ALL INTO ASIGNATURA VALUES ('ASG1',1,'DESARROLLO SOFTWARE','DESARROLLO DE SOFTWARE IV') INTO ASIGNATURA VALUES ('ASG2',2,'BASE DE DATOS','ADMINISTRACION DE BASE DE DATOS') INTO ASIGNATURA VALUES ('ASG3',3,'HARDWARE &REDES','ADMINISTRACION DE SISTEMAS OPERATIVOS Y REDES') SELECT * FROM dua l; INSERT ALL INTO ESTUDIANTE VALUES ('1804600912','RUIZ','JORGE',to_date('19960725','YYYYMMDD')) INTO ESTUDIANTE VALUES ('1804600913','ALMACHE','CARLOS',to_date('19950625','YYYYMMDD')) INTO ESTUDIANTE VALUES ('1804600914','PICO','ALFREDO',to_date('19940712','YYYYMMDD')) SELECT * FROM dua l; INSERT ALL INTO HOBBIE VALUES ('1804600912','MONTAR A BICICLETA') INTO HOBBIE VALUES ('1804600913','ESCUCHAR MUSICA') INTO HOBBIE VALUES ('1804600914','VER TELEVISION') SELECT * FROM dua l; INSERT ALL INTO PSICOLOGICO VALUES ('1804600912','Embeleso') INTO PSICOLOGICO VALUES ('1804600913','Normopatia') INTO PSICOLOGICO VALUES ('1804600914','Compulsión a la repeticion')


SALUD

DOCENTE

FORMACIÓN

TELEFONO (DOCENTE) NOTAS

SELECT * FROM dua l; INSERT ALL INTO SALUD VALUES ('1804600912','BUENA SALUD') INTO SALUD VALUES ('1804600913','BUENA SALUD') INTO SALUD VALUES ('1804600914','BUENA SALUD') SELECT * FROM dua l; INSERT ALL INTO DOCENTE VALUES ('1904660912','GALARZA','RODRIGO',to_date('19750725','YYYYMMD D')) INTO DOCENTE VALUES ('1904760913','PINCAY','ESTEBAN',to_date('19700720','YYYYMMDD')) INTO DOCENTE VALUES ('1904860914','DURAN','MAYRA',to_date('19720525','YYYYMMDD')) SELECT * FROM dua l; INSERT ALL INTO FORMACION VALUES ('1904660912','BASE DE DATOS','TERCERO',2001,'UTA','INGENIERO EN SISTEMAS','ECUADOR') INTO FORMACION VALUES ('1904760913','REDES INFORMATICAS','CUARTO',2002,'ESPOCH','MASTER','ECUADOR') INTO FORMACION VALUES ('1904860914','DESARROLLO DE SOFTWARE','TERCERO',2004,'UTA','INGENIERO EN SISTEMAS','ECUADOR') SELECT * FROM dua l; INSERT ALL INTO TELEFONO VALUES ('1904660912','0962926572') INTO TELEFONO VALUES ('1904760913','0962035577') INTO TELEFONO VALUES ('1904860914','0962136671') SELECT * FROM dua l; INSERT ALL INTO NOTAS (CODAS,CIEST,CI,NOTA1,NOTA2) VALUES ('ASG1','1804600912','1904660912',7.5,8.1) INTO NOTAS (CODAS,CIEST,CI,NOTA1,NOTA2) VALUES ('ASG2','1804600913','1904760913',6.8,8.6) INTO NOTAS (CODAS,CIEST,CI,NOTA1,NOTA2) VALUES ('ASG3','1804600914','1904860914',8.2,8) SELECT * FROM dua l;

1.7 Resultados y Discusión Para la implementación de la base de datos se hizo uso de una herramienta CASE como lo es Power Designer, herramienta que permite generar código SQL a partir de un modelo relacional para diferentes Gestores de Base de Datos entre estos tenemos MySQL, PostgreSQL, Oracle, SQL Server, Informix y muchos otros más. Uno de los principales problemas encontrados en esta práctica fue que el Gestor de Base de Datos Oracle tiene una interpretación muy peculiar en cuanto al carácter & pues éste estaba formando parte de la cadena ‘Hardware &Redes’ al momento de definir la lista de valores para la partición en la tabla área, fue en ese entonces donde generó un error, lo que se hizo fue reemplazar el carácter mencionado por una ‘Y’ quedando ‘Hardware y Redes’ y el inconveniente quedo solucionado. 1.8 Conclusiones En la tabla notas se pudo también a ver hecho una vista para mostrar la tabla original más una columna calculada que sería el promedio, pero considero que las columnas virtuales que Oracle permite crear nos facilita la tarea al momento de definir campos de este tipo, con lo que se ahorra el tiempo para realizar una vista. Las partición por referencia es un concepto nuevo que se ha venido integrando conforme han ido evolucionando las versiones de Oracle, el mismo que hace uso del


concepto de herencia para poder particionar una tabla hija utilizando la misma clave de partición de la tabla padre sin tener que duplicar las columnas de clave. La fragmentación de la tablas nos permite mejorar el tiempo de respuesta de las consultas ya que al estar divididas acceden a una o varias partes pero no tiene que recorrer toda la tabla muy útil cuando queremos mejorar el rendimiento de nuestra base de datos. 1.9 Referencias bibliográficas Espinosa Roberto. Business Intelligence. Particionado de tablas en Oracle. http://www.dataprix.com/blogs/respinosamilla/particionado-tablas-oracle Baer Hermann. Oracle Corporation. 2 September 2009. U.S.A. Partitioning with Oracle Database 11g Release 2. https://es.scribd.com/doc/22401527/Partitioning-With-Oracle-Database-11g-R2 Hall Tim. Oracle Database Administrator Certified Professional. Partitioning Enhancements in Oracle Database 11g Release 1. https://oracle-base.com/articles/11g/partitioning-enhancements-11gr1 Learn Database Technologies. Oracle/PLSQL: CREATE TABLESPACE statement. http://www.techonthenet.com/oracle/tablespaces/create_tablespace.php 1.10

Fotografías y gráficos

CONSULTAS DE VERIFICACION Se procede a realizar consultas a la base de datos para constatar que todo se haya hecho de manera correcta.

Figura 3. Tablespaces creados


Figura 4. Particiones de la tabla notas en sus correspondientes Tablespace

Figura 5. Particiones de la tabla Campus en sus correspondientes tablespace

Figura 6. Particiones de la tabla Asignatura en sus correspondientes tablespace

Figura 7. Partición por valor tabla Campus


Figura 8. Partición por valor tabla Área

Figura 9. Partición por Referencia de la tabla Notas

Figura 10. Tablas contenidas en el Tablespace TV

Fragmentación de una base de datos en Oracle 11g

Recommend Documents