Norma de programacion plsql

Título: Norma de Programación PL/SQL Cambio:

PRM15DI2000

MDF28JL2008 Escrito por:

Sergio Aguilera E.

Actualizado/Modificado:

Sergio Aguilera E.

Aprobación:

Gianella Oropesa

Página:

1/50

Identificación:

QA N0003

NORMA DE PROGRAMACIÓN PL/SQL

Norma de Programación PL/SQL


PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Gianella Oropesa

Página:

2/50

Identificación:

QA N0003

CONTROL DE CAMBIOS

Versión Tipo Descripción del Cambio Autor Fecha Revisado por

1 Original Sergio Aguilera E. 15/12/2000 Patricio Caballero

2 Modificación Sergio Aguilera E. 22/06/2004 Jefes de Proyecto

y Analistas


y Analistas


y Analistas


y Analistas

6 Actualización Incorporación del Tipo Objeto Sergio Aguilera E. 22/09/2005 Jefes de Proyecto

y Analistas

7 Modificación En tipo de objeto se incorpora

Secuencias

Sergio Aguilera E. 26/09/2005 Jefes de Proyecto

y Analistas

8 Modificación Revisión y modificación de Estándares Sergio Aguilera E. 17/04/2006

9 Modificación Incorporación de Chequeos de

Exactitud, suficiencia y autorización,

en el marco del proyecto SOX.

Sergio Aguilera E. 12/07/2006 Patricio Caballero

10 Modificación Se incorpora el nombre de objetos de

BBDD que identifican a los sistemas

con dos letras.

Sergio Aguilera E. 05/01/2007 Patricio Tellez

11 Modificación Se incorpora Sistema Transaccional

en la definición identificación de

sistemas.

Sergio Aguilar E. 22/03/2007 Gianella Oropesa

12 Modificación Se incorpora nomenclatura para la

creación de table spaces (tablas e

índices) y la creación de nombres de

usuarios en la BBDD.


13 Modificación Se incorpora sistema PMO Sergio Aguilera E. 31/07/2007 Felipe Mellado

14 Modificación Se incorpora nomenclatura VM para

vistas materializadas


15 Modificación Se incorporan buenas practicas de

programación y uso de índices.

Sergio Aguilera E. 20/01/2008 Gianella Oropesa

16 Modificación Se incorpora el tipo A, para trigers

distintos de Update, Insert y Delete.

Sergio Aguilera E. 23/05/2008

17 Modificación En 10.020 Letras asignadas a

sistemas, se agrega letra VC para

identificar al sistema Ahorro

Darío Campos R. 08/07/2008 Sergio Aguilera E.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

3/50

Identificación:

QA N0003

Previsional Voluntario Colectivo.

18 Modificación Modificación del punto 8.070.3,

ejemplo explica lo bueno de usar un

select into.

Darío Campos R. 28/07/2008 Sergio Aguilera E.

19 Modificación Se incorpora sigla WP para sistema

Internet Pro

Sergio Aguilera E. 31/07/2008 Darío Campos

Tipo: Original : Primera versión del documento. Actualización : Puesta al día y/o renovación del contenido del documento. Modificación : Cambios o transformaciones al contenido del documento.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

4/50

Identificación:

QA N0003

TABLA DE CONTENIDOS

1.000 INTRODUCCIÓN......................................................................................................................................9

2.000 SUPUESTOS............................................................................................................................................9

3.000 ALCANCE ............................................ ....................................................................................................9

4.000 RESPONSABLES ....................................... .............................................................................................9

5.000 UBICACIÓN Y NOMENCLATURA DE ARCHIVOS............... .................................................................9

5.010 ESTRUCTURA DE DIRECTORIOS.....................................................................................................9 5.020 NOMENCLATURA DE ARCHIVOS........................... ........................................................................10

5.020.1 TIPO............................................................................................................................................10 5.020.2 SISTEMA....................................................................................................................................10 5.020.3 NOMBRE....................................................................................................................................10

6.000 NOMENCLATURA DE OBJETOS DE BASE DE DATOS........... .........................................................10

6.010 CARACTERÍSTICAS .................................... .....................................................................................10 6.010.1 TIPO............................................................................................................................................10 6.010.2 SISTEMA....................................................................................................................................11 6.010.3 NOMBRE....................................................................................................................................11 6.010.4 NOMBRES DE COLUMNAS DE UNA TABLA ................... ......................................................11

6.020 TABLAS ............................................. ................................................................................................11 6.030 CONSTRAINTS..................................................................................................................................12 6.040 ÍNDICES .............................................................................................................................................12 6.050 TRIGGERS .........................................................................................................................................13 6.060 SINÓNIMOS .......................................................................................................................................13 6.070 TABLE SPACES ....................................... .........................................................................................13

7.000 REGLAS GENERALES ................................... ......................................................................................14

7.010 EXACTITUD, SUFICIENCIA Y VALIDEZ ................... .......................................................................14 7.020 AUTODOCUMENTACIÓN .................................. ...............................................................................14 7.030 CONSTANTES Y VARIABLES............................. .............................................................................15 7.040 PROCEDIMIENTOS Y FUNCIONES .................................................................................................17 7.050 QUERYS.............................................................................................................................................18

8.000 REGLAS DE PROGRAMACIÓN PL/SQL ...................... .......................................................................19

8.010 ENCAPSULAR CONSULTAS DE UNA FILA EN FUNCIONES ...... .................................................19 8.010.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................19 8.010.2 MOTIVACIÓN.............................................................................................................................19 8.010.3 EJEMPLO............................................ .......................................................................................19



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

5/50

Identificación:

QA N0003

8.010.4 BENEFICIOS..............................................................................................................................19 8.010.5 DESAFÍOS..................................................................................................................................19

8.020 OCULTAR EL USO DE LA TABLA DUAL.................... ....................................................................20 8.020.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................20 8.020.2 MOTIVACIÓN.............................................................................................................................20 8.020.3 EJEMPLO............................................ .......................................................................................20 8.020.4 BENEFICIOS..............................................................................................................................20

8.030 DEFINIR CURSORES DE MÚLTIPLES FILAS EN PACKAGES .... .................................................21 8.030.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................21 8.030.2 MOTIVACIÓN.............................................................................................................................21 8.030.3 EJEMPLO............................................ .......................................................................................21 8.030.4 BENEFICIOS..............................................................................................................................22 8.030.5 DESAFÍOS..................................................................................................................................22

8.040 USAR REGISTROS (%ROWTYPE) PARA FETCH DE CURSORES... ............................................22 8.040.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................22 8.040.2 MOTIVACIÓN.............................................................................................................................22 8.040.3 EJEMPLO............................................ .......................................................................................22 8.040.4 BENEFICIOS..............................................................................................................................23

8.050 EVITAR INNECESARIO USO DE COUNT.................... ....................................................................23 8.050.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................23 8.050.2 MOTIVACIÓN.............................................................................................................................23 8.050.3 EJEMPLO............................................ .......................................................................................24 8.050.4 BENEFICIOS..............................................................................................................................25

8.060 UTILIZAR CURSOR FOR-LOOP PARA PROCESAR CURSORES .... ............................................25 8.060.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................25 8.060.2 MOTIVACIÓN.............................................................................................................................25 8.060.3 EJEMPLO............................................ .......................................................................................25 8.060.4 BENEFICIOS..............................................................................................................................26 8.060.5 DESAFÍOS..................................................................................................................................26

8.070 NO USAR CURSOR FOR-LOOP PARA FETCH DE UNA FILA ..... .................................................26 8.070.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................26 8.070.2 MOTIVACIÓN.............................................................................................................................26 8.070.3 EJEMPLO............................................ .......................................................................................26 8.070.4 BENEFICIOS..............................................................................................................................27

8.080 ESPECIFICAR COLUMNAS A ACTUALIZAR EN SELECT FOR UPD ATE....................................27 8.080.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................27 8.080.2 MOTIVACIÓN.............................................................................................................................27 8.080.3 EJEMPLO............................................ .......................................................................................28 8.080.4 BENEFICIOS..............................................................................................................................28

8.090 PARAMETRIZAR CURSORES EXPLÍCITOS................... ................................................................28 8.090.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................28 8.090.2 MOTIVACIÓN.............................................................................................................................28 8.090.3 EJEMPLO............................................ .......................................................................................28



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

6/50

Identificación:

QA N0003

8.090.4 BENEFICIOS..............................................................................................................................29 8.0100 UTILIZAR CLÁUSULA RETURNING ........................ ........................................................................29

8.0100.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................29 8.0100.2 MOTIVACIÓN.............................................................................................................................29 8.0100.3 EJEMPLO............................................ .......................................................................................29 8.0100.4 BENEFICIOS..............................................................................................................................30

8.0110 USAR CLÁUSULA BULK COLLECT......................... .......................................................................30 8.0110.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................30 8.0110.2 MOTIVACIÓN.............................................................................................................................30 8.0110.3 EJEMPLO............................................ .......................................................................................30 8.0110.4 BENEFICIOS..............................................................................................................................31 8.0110.5 DESAFÍOS..................................................................................................................................31

8.0120 ENCAPSULAR SENTENCIAS DML EN LLAMADAS A PROCEDURES . .......................................31 8.0120.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................31 8.0120.2 MOTIVACIÓN.............................................................................................................................31 8.0120.3 EJEMPLO............................................ .......................................................................................31 8.0120.4 BENEFICIOS..............................................................................................................................31 8.0120.5 DESAFÍOS..................................................................................................................................31

8.0130 USO DEL ROWCOUNT .....................................................................................................................32 8.0130.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................32 8.0130.2 MOTIVACIÓN.............................................................................................................................32 8.0130.3 EJEMPLO............................................ .......................................................................................32 8.0130.4 BENEFICIOS..............................................................................................................................32

8.0140 REFERENCIAR ATRIBUTOS DE CURSORES INMEDIATAMENTE DE SPUÉS DE LA OPERACIÓN SQL...................................... ........................................................................................................32

8.0140.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................32 8.0140.2 MOTIVACIÓN.............................................................................................................................32 8.0140.3 EJEMPLO............................................ .......................................................................................33

8.0150 USAR BIND VARIABLES ................................ ..................................................................................33 8.0150.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................33 8.0150.2 MOTIVACIÓN.............................................................................................................................33 8.0150.3 EJEMPLO............................................ .......................................................................................34 8.0150.4 BENEFICIOS..............................................................................................................................34

8.0160 FORMATEAR SQL DINÁMICOS............................ ...........................................................................34 8.0160.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................34 8.0160.2 EJEMPLO............................................ .......................................................................................34 8.0160.3 BENEFICIOS..............................................................................................................................35 8.0160.4 DESAFÍOS..................................................................................................................................35

8.0170 OPTIMIZACIÓN BASADA EN COSTOS...................... .....................................................................35 8.0170.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................35 8.0170.2 MOTIVACIÓN.............................................................................................................................35

8.0180 NO UTILIZAR HINTS EN SQL........................... ................................................................................36 8.0180.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................36



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

7/50

Identificación:

QA N0003

8.0180.2 MOTIVACIÓN.............................................................................................................................36 8.0190 EVITAR SORTS .................................................................................................................................36

8.0190.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................36 8.0190.2 MOTIVACIÓN.............................................................................................................................36 8.0190.3 ALTERNATIVAS PARA EVITAR SORTS ..................... ............................................................36

8.0200 EVITAR CONVERSIONES DE TIPOS DE DATOS IMPLÍCITAS ... ..................................................37 8.0200.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................37 8.0200.2 MOTIVACIÓN.............................................................................................................................37 8.0200.3 EJEMPLO I.......................................... .......................................................................................37 8.0200.4 EJEMPLO II......................................... .......................................................................................37 8.0200.5 BENEFICIOS..............................................................................................................................37

8.0210 USO DE EXISTS VS IN......................................................................................................................38 8.0210.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................38 8.0210.2 MOTIVACIÓN.............................................................................................................................38 8.0210.3 EJEMPLO............................................ .......................................................................................39 8.0210.4 BENEFICIOS..............................................................................................................................41

8.0220 NO USAR VARIABLES GLOBALES ......................... .......................................................................41 8.0220.1 DESCRIPCIÓN DE LA REGLA ............................ .....................................................................41 8.0220.2 MOTIVACIÓN.............................................................................................................................41 8.0220.3 EJEMPLO............................................ .......................................................................................42 8.0220.4 BENEFICIOS..............................................................................................................................42 8.0220.5 DESAFÍOS..................................................................................................................................42

9.000 REGLAS PARA USO DE INDICES ......................... ..............................................................................42

9.010 PAUTAS DE INDIZACIÓN ............................... ..................................................................................42 9.020 TIPOS DE ÍNDICES ...........................................................................................................................43

9.020.1 ÍNDICES COMPUESTOS (CONCATENATED INDEX)............ .................................................43 9.020.2 UNIQUE AND NONUNIQUE INDEXES .....................................................................................43 9.020.3 ALMACENAMIENTO DE ÍNDICES.......................... ..................................................................43

9.030 CUANDO INDIZAR ..................................... .......................................................................................43 9.040 PAUTAS PARA ÍNDICES COMPUESTOS ..................... ..................................................................43 9.050 INDICES Y VALORES NULL............................. ................................................................................43 9.060 ÍNDICES BASADOS EN FUNCIONES....................... .......................................................................44

9.060.1 CONDICIONES PARA UTILIZAR ÍNDICES BASADO EN FUNCION ES.................................44 9.070 COMO SE ALMACENAN LOS ÍNDICES ...................... ....................................................................44 9.080 ÍNDICES DE CLAVE INVERTIDA......................... .............................................................................45 9.090 ÍNDICES BITMAP ..................................... .........................................................................................45

9.090.1 CREACIÓN DE ÍNDICES BITMAP ......................... ...................................................................45 9.090.2 ESTRUCTURA ...........................................................................................................................46 9.090.3 EJEMPLO DE USO..................................... ...............................................................................46 9.090.4 RECOMENDACIONES ..............................................................................................................46

9.0100 ÍNDICES PARTICIONADOS.............................. ................................................................................47 9.0100.1 LOCAL INDEX........................................ ....................................................................................47



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

8/50

Identificación:

QA N0003

9.0100.1.I LOCAL PREFIXED..................................... ...........................................................................47 9.0100.1.II LOCAL NONPREFIXED.................................. .....................................................................47

9.0100.2 GLOBAL PREFIXED INDEX.............................. ........................................................................47 9.0110 EFECTO DE OPERACIONES DML SOBRE TABLAS INDEXADAS... ............................................47 9.0120 ÍNDICES Y CONSTRAINTS...............................................................................................................47 9.0130 ÍNDICES Y CLAVES FORANEAS.......................... ...........................................................................48

10.000 ANEXO ...................................................................................................................................................49

10.010 TIPOS DE OBJETOS................................... ......................................................................................49 10.020 LETRAS ASIGNADAS A SISTEMAS........................ ........................................................................50



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

9/50

Identificación:

QA N0003

1.000 INTRODUCCIÓN Con el objetivo de estandarizar, facilitar y acelerar el proceso de desarrollo en PL/SQL, se entrega la presente normativa, la cual se enfoca en las siguientes áreas. Una primera área que es la estructura de directorios, nombres de archivos, nomenclatura de paquetes, procedimientos y funciones, nombres de tablas y sus campos, vistas y secuencias. Una segunda parte que se aplica al desarrollo interno de los procesos, con nomenclaturas de variables y esquemas de programación y documentación que permitan una comprensión más rápida de lo desarrollado. Finalmente una tercera parte, que considera aplicación de herramientas poco conocidas de Oracle, como lo son JOBS, ALERTS, LOCKS.

2.000 SUPUESTOS Dentro de esta normativa se consideran los siguientes supuestos:

• Sistema operativo de 32 bits que soporte nombres largos de archivos. • Base de datos Oracle 7.3 o superior. • Conocimiento de la actual estructura de directorios de Banchile. • Conocimiento de programación estructurada. • Conocimiento de SQL avanzado.

3.000 ALCANCE Todos los objetos PL/SQL. 4.000 RESPONSABLES El área de desarrollo y proyectos, es responsable del cumplimiento de esta norma.

5.000 UBICACIÓN Y NOMENCLATURA DE ARCHIVOS

Los archivos se deberán ubicar en ambiente UNIX, bajo el directorio /u/desarrol/FTE. Dentro de ese path se encontrarán los directorios de cada sistema, los cuales tomarán los mismos nombres especificados en la norma para aplicaciones PowerBuilder. A fin de no entorpecer el manejo de los archivos para la gente que no sabe utilizar el editor VI de UNIX, existe la opción de editarlos desde NT con el editor ULTRAEDIT, el cual permite abrir y grabar archivos directamente desde UNIX. Por otro lado, no se utilizarán subdirectorios para diferenciar los distintos objetos que se pueden construir en PL-SQL, como Packages, Procedures, Functions, Views, Tables, Constraints, Indexs, Clusters, etc. El único subdirectorio dentro de cada sistema correspondería a un directorio TMP, en el cual se almacenaría cualquier archivo que no correspondiera a los tipos ya señalados de archivos PL/SQL.

5.010 Estructura de directorios De esta forma, la estructura de directorios quedaría como sigue:

Nivel Descripción Valores posibles

1 Sistema y globales <SISTEMA>, GLOBAL 2 Temporal Archivos temporales



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

10/50

Identificación:

QA N0003

• Nivel 1: en este directorio se da paso a los sistemas existentes en la empresa,

además de un directorio de PL globales comunes a todos los sistemas. Se utilizará el nombre completo de los sistemas, por ejemplo ACCIONES, INVERSIONES, FONDOS_MUTUOS, DOLARES, LIQUIDACION, CONCILIACION, etc., compatibilizándolo con el nombre asignado en la norma POWER BUILDER.

• Nivel 2: directorio en donde se almacenan los archivos que no tienen que ver con algún objeto definido para PL/SQL, como lo son scripts de carga y shell varios.

Finalmente en el ambiente de desarrollo como en el de explotación se deberá mantener la misma estructura de directorios, a fin de homogeneizar ambos ambientes y facilitar las liberaciones.

5.020 Nomenclatura de Archivos Con respecto a la nomenclatura de los archivos, está se basará en la utilización del mismo nombre del objeto que representa.

5.020.1 Tipo

El primer carácter debe indicar el tipo de objeto que representa el archivo de acuerdo a la tabla Tipos de Objetos. (ver Anexo).

5.020.2 Sistema

Se utilizará una letra representativa del sistema de acuerdo a la nomenclatura dada para los sistemas en la Tabla de Sistemas (ver Anexo).

5.020.3 Nombre

Los últimos caracteres deben corresponder al nombre del archivo que se está creando, exactamente igual al nombre del objeto que representa. Se debe utilizar la agrupación de tres letras representativas del objeto.

6.000 NOMENCLATURA DE OBJETOS DE BASE DE DATOS

Para los distintos Tipos de Objetos de Base de Datos como packages, procedures, functions, views, tables, constraints, indexs, clusters, roles, etc., se debe utilizar el siguiente esquema de nombres.

6.010 Características

6.010.1 Tipo

El primer carácter debe indicar el tipo de objeto de acuerdo a la tabla Tipos de Objetos. (ver Anexo).



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

11/50

Identificación:

QA N0003

6.010.2 Sistema

Se utilizará una letra representativa del sistema de acuerdo a la nomenclatura dada para los sistemas en la Tabla de Sistemas (Ver Anexo). Adicionalmente, en función de la disponibilidad de letras, será necesario ampliar a dos letras representativas del sistema de acuerdo a la nomenclatura dada para los sistemas en la Tabla de Sistemas (Ver Anexo).)

6.010.3 Nombre

Los últimos caracteres deben corresponder al nombre del objeto. Se debe utilizar la agrupación de tres letras representativas del objeto.

6.010.4 Nombres de Columnas de una Tabla

Los nombres de las columnas de una tabla, debe ser representativo del uso tipo y uso final que se le dará. Se debe mantener la agrupación representativa de tres letras.

6.020 Tablas La nomenclatura a utilizar sera la siguiente: • Ts_ nombreTabla Donde: • T: representa el carácter que indica el tipo de objeto, en este caso T= Tabla. • s: representa el carácter/caractéres del sistema al que pertenece la entidad. • nombreTabla: es el nombre de la entidad referida. Se debe utilizar la agrupación de

tres letras representativas de la tabla. Pueden usarse varias palabras pero separadas por ‘underscores’ (_). Los nombres serán en singular.

Ejemplos: • TI_BCO (se indica que se trata de un objeto del sistema I=Inversiones con datos de

Bancos) • TIW_TRS (se indica que se trata de un objeto Tabla, del sistema Inversiones Web,

con datos de transacciones). Características adicionales de cada Tabla: • El nombre de la tabla no puede superar los 30 caracteres. • Es obligatorio que todas las tablas tengan una clave primaria (PK). • Cada tabla debe tener definido un alias o nombre corto (a lo sumo 7 letras y único

en el sistema) para utilizar en constraints e índices. • Todas las tablas deberán contener comentarios. • Los nombres de los campos de una tabla, debe ser representativo del uso tipo y

uso final que se le dará. Se debe mantener la agrupación representativa de tres letras.

• El nombre del campo no puede superar los 30 caracteres.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

12/50

Identificación:

QA N0003

• Si existe la necesidad de almacenar archivos en Oracle, utilizar campos del tipo de datos BLOB, no utilizar LONG RAW.

• Para todos los campos del tipo alfanumérico utilizar el tipo de datos VARCHAR2(n) donde ‘n’ es mayor o igual que 1.

• Los campos de distintas tablas que hacen referencia al mismo concepto deben utilizar el mismo nombre, con idéntico tipo y longitud.

6.030 CONSTRAINTS La nomenclatura a utilizar sera la siguiente: • tcS_Ts_ nombreTabla. Donde: • Tc: es el tipo de constraint.

o PK. Primary Key o FK. Foreign Key o UKn. Unique Key (n es el numero secuencial de UK sobre la misma tabla) o CHn. Check Constraint (n es el numero secuencial de CH sobre la misma

tabla) • S: representa el carácter/caractéres del sistema al que pertenece la entidad. • Ts_ nombreTabla: El nombre de la tabla a la que pertenece.

Características adicionales de cada constraints: • En los casos de UNIQUE y CHECK constraint, deberán seguirse de un número

secuencial para identificar más de una constraint del mismo tipo. • Si relaciona dos tablas por FOREIGN deberán separarse sus alias por un

‘underscore’, ubicándose en primer lugar la tabla ´dueña’ de la constraint y luego la tabla referenciada.

• Las constraints por NOT NULL no llevan nombre, se utiliza el default del sistema. Ejemplos: • Pk_TI_BCO (Primary Key de tabla Bancos del sistema Inversiones). • Uk_TI_BCO (Unique Key nro 1 de tabla Bancos del sistema Inversiones). • Fk_TI_BCO_SUC (Foreign Key de tabla BANCOS en SUCURSALES del sistema

Inversiones). • CH1_TI_ BCO (Check nro1 sobre la tabla BANCOS del sistema Inversiones). • Pk_TIW_TRS (Primary Key de la tabla de Transacciones del sistema Inversiones

Web).

6.040 ÍNDICES Los nombres de índices deberán llevar: • Ts_ nombreTabla: el nombre de la tabla. • Nombre Columna: nombre de la columna sobre la se construye el indide.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

13/50

Identificación:

QA N0003

• I: al final del nombre Ejemplos: • TI_CTA_INV_TG_SRU_I NOTA: Si es creado por una constraint, el índice tendrá el nombre de dicha constraint.

6.050 TRIGGERS Los nombres de triggers estarán formados por cuatro partes (separadas por underscore): • El identificador de tipo objeto trigger = ‘G’. • El identificador del tipo de trigger que se compone de:

o La instancia en que ocurre: � A = After � B = Before

o El evento del trigger: � I = Insert � U = Update � D = Delete � A = Definido por el usuario.

o Ámbito que afecta: � R = Rows � S = Statement.

• El nombre de la tabla al que afecta el trigger.

En caso de que existan dos triggers o más sobre la misma tabla y a ejecutarse en los mismos casos, se evitará la duplicación de nombres con un número de secuencia al final. Ejemplos: • G_BIR_TI_BCO_1 • G_BIR_TI_BCO_2 • G_BIS_TI_CLI_1 • G_BIS_TIW_TRS_1 Los dos primeros triggers están definidos sobre la tabla TI_BCO (Bancos) del sistema Inversiones, y se disparan Before Insert para cada fila de la tabla (for each Row). El último trigger está definido sobre la tabla Clientes del mismo sistema, y se dispara Before Insert a nivel de sentencia (Statement).

6.060 SINÓNIMOS Similar a los nombres de tablas. No se deben definir sinónimos privados.

6.070 TABLE SPACES • Para Tablas:



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

14/50

Identificación:

QA N0003

o STAB, donde S corresponde a la sigla del sistema y TAB un sufijo estándar para identificar que se trata de un espacio para tablas.

• Para Índices:

o SIDX, donde S corresponde a la sigla del sistema e IDX un sufijo estándar para identificar que se trata de un espacio para índices.

Por ejemplo, para el proyecto Inversiones Web(TIW) se crearon los nombres de table spaces como sigue: TableSpace Tabla : TIWTAB TableSpace Indice : TIWIDX

7.000 REGLAS GENERALES

Aquí, se indican aspectos generales que mejoraran el entendimiento del trabajo en PL/SQL, además apoyará la autodocumentación de los mismos.

7.010 Exactitud, suficiencia y validez Estos controles son establecidos en la BD, para los datos que han sido capturados para su procesamiento (generados por personas, por sistemas o entradas de interfase), con el fin de asegurar que esos datos de entrada sean validados y editados tan cerca del punto de origen como sea posible. Las tablas se pueden relacionar entre ellas a través de las columnas que las componen, utilizando llaves primarias y foráneas, con ello la Base de Datos asegura el cumplimiento de esas relaciones a través de la integridad referencial, que se concreta en las restricciones de tablas. La utilización de Contraints se realiza a nivel de: • Tipo Not Null, es decir no se aceptan campos en Blancos. • Tipo Referencial, es decir que no se pueden borrar datos de una tabla hija sino se

ha borrado desde una padre. • Tipo Check, la cual implica que una determinada columna se debe llenar con cierto

valor de Dato. Por otra parte, se utilizan Triggers, que son procedimientos que son ejecutados cuando se produce un determinado evento en la BD, en Banchile se deben utilizar utilizar para mejorar y reforzar la integridad y la seguridad de la BD.

7.020 Autodocumentación Todos los PL/SQL se documentan al inicio del script, de la siguiente forma: ================================================================



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

15/50

Identificación:

QA N0003

-- Nombre : <<Nombre del PL/SQL>> -- Descripción : <<Descripción del PL/SQL>> -- Creador : <<Nombre del Ususario Creador del PL/SQL>> -- Fecha : <<DD/MM/YYYY>> -- Parametros : -- Entrada: -- parametroE 1: <<Descripción del parámetroE 1>> -- parametroE 2: <<Descripción del parámetroE 2>> -- parametroE 3: <<Descripción del parámetroE 3>> -- parametroE N: <<Descripción del parámetroE N>> -- Salida: -- parametroS 1: <<Descripción del parámetroS 1>> -- parametroS 2: <<Descripción del parámetroS 2>> -- parametroS 3: <<Descripción del parámetroS 3>> -- parametroS N: <<Descripción del parámetroS N>> -- Modificaciones: -- Modicador1: <<Nombre del Ususario que modifica>> -- Fecha Modificación1: <<DD/MM/YYYY>> -- Descripción1: <<Descripción de la Modificación>> ---------------------------------------------------------------------------------------------------------------- -- ModicadorN: <<Nombre del Ususario que modifica>> -- Fecha ModificaciónN: <<DD/MM/YYYY>> -- DescripciónN: <<Descripción de la Modificación>> ================================================================ Para los cambios en un punto de específico del script se documenta en forma reducida: -- <<DD/MM/YYYY>>; <<USUARIO>>; <<COMENTARIO>>

7.030 Constantes y variables Para este aspecto se mencionan algunas buenas prácticas: • Los nombres de constantes y variables deben mantener la agrupación de tres letras

para describir su representación. • Nombrar las constantes con el prefijo cl_ • Nombrar las constantes globales con el prefijo cg_ • Nombrar las variables locales con el prefijo vl_ • Nombrar las variables globales con el prefijo vg_ • Nombrar los cursores locales con el prefijo vl_cur_ • Nombrar los cursores globales con el prefijo vg_cur_ • Todas las variables relevantes deben estar declaradas en el inicio del

procedimiento o función.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

16/50

Identificación:

QA N0003

• Todas las variables y constantes relevantes documentarlas al inicio del paquete, función o procedimiento, antes de su uso.

• Los nombres de variables, constantes y cursores, a continuación del prefijo, deben ser compuestos en grupos de tres(3) caracteres separados por underscores que representen su significado.

• Privilegiar el uso de variables globales a los paquetes, así como el uso de constantes, no declararlas como variables; esto no se refiere a utilizar variables globales sobre variables locales, sino que se refiere a que, si existen variables comunes a todos los procedimientos, utilizadas para el mismo objetivo, mejor utilizarlas como variables globales más que como locales junto a parámetros.

• Los nombres de variables deben ser adecuados a su uso, y si representan columnas de tablas, que posean el mismo nombre de la columna, pero de su tabla base para el caso de columnas que son claves foráneas.

• No usar variables para distintos objetivos, mejor definir otras variables que sean utilizadas para esos otros usos.

• Evitar la declaración de variables inútiles, como las variables que controlan los ciclos de una instrucción FOR LOOP, así como el uso de variables para las columnas del cursor, ya que es posible utilizar la misma variable del cursor.

• Utilizar %TYPE para definir tipos asociados a tablas. • No utilizar valores en duro en el código, mejor definir constantes al inicio del

procedimiento o paquete, o mejor, definir tablas de parámetros para mantener esos valores dinámicamente.

• En lo posible utilizar la declaración de tipos, a fin de declarar variables de tipos entendibles, vale decir, definir subtipos: Ejemplo:

SUBTYPE correlativo_cartera IS NUMBER(9); y al declarar la variable local VL_COR utilizar: vl_cor correlativo_cartera

Todos los subtipos deben definirse previamente en una especie de planilla de subtipos permitidos, los que deben tener una clara razón de ser y no crear subtipos sin importancia práctica, vale decir, un subtipo para un contador no tiene sentido, pero si un subtipo para rut de cliente. En estos momentos se recomienda crear subtipos para los siguientes conceptos:

Subtipo Tipo Concepto st_rut VARCHAR2(9) Rut st_sub_rut NUMBER(3) Subrut st_cor NUMBER(12) Correlativo (operación, orden, asignación) st_val_mon NUMBER(21,6) Valor monetario st_fec DATE Fecha sin hora st_fec_hra DATE Fecha con hora



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

17/50

Identificación:

QA N0003

st_si_no VARCHAR2(2) Indicador SI/NO st_nem VARCHAR2(8) Nemotécnico (fondo, subrut, cliente) st_des VARCHAR2(100) Descripción (nombre, dirección)

• Utilizar variables con nombres ad_hoc para ocultar complejidad alta de código, vale

decir, antes de, por ejemplo, crear una sentencia IF cuya condición se compone de varias subcondiciones, agrupar esas subcondiciones en variables booleanas con nombre significativo, y realizar la sentencia IF en base a esas variables booleanas.

7.040 Procedimientos y Funciones • Los nombres de procedimientos y funciones dentro de un paquete deberán utilizar

una nomenclatura basada en una primera letra seguida de un underscore (P para procedimientos, F para funciones) seguido de grupos de tres(3) letras separadas por underscores que representen el significado del procedimiento o función; para funciones y procedimientos globales, deberán iniciarse con PG_ o FG_.

• Los nombres de parámetros a procedimientos y funciones deben empezar con el prefijo p_.

• No abusar del uso de parámetros tipo IN OUT, si sólo son de salida utilizar OUT, si sólo son de entrada utilizar IN, sólo en muy contadas ocasiones utilizar IN OUT.

• Controlar todos los SELECT con EXCEPTIONS a fin de no producirse eventos inesperados.

• Utilizar mensajes de error en español, anexando el mensaje original que entrega Oracle (en caso de que exista), además de tabular los errores de acuerdo a su número asignado; al enviar un mensaje de error componerlo de acuerdo a la siguiente estructura: • Correlativo único por procedimiento al menos. • Mensaje explicativo en español. • Mensaje completo entregado por Oracle, en caso que corresponda.

• Ser modular al crear el código, evitando duplicidad del mismo, utilizar funciones o procedimientos locales al paquete o a los mismos procedimientos o funciones para generar procesos que se utilizan repetidamente dentro del procedimiento, función o paquete.

• Identar al desarrollar los PL, utilizando una cantidad de espacios constantes(3 caracteres espaciadores, no un salto tabular) en cada nivel de identación.

• Utilizar mayúsculas y minúsculas para diferenciar entre palabras reservadas de PL y variables, constantes y cursores.

• No usar los saltos de control tipo GOTO, LABEL, etc., utilizar programación estructurada u programación orientada a objetos para el caso de Oracle 8 en adelante.

• Dejar autodocumentado para qué sirve el procedimiento, paquete, función o vista y, en caso de existir, que significa cada uno de los parámetros a nivel de la declaración del mismo



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

18/50

Identificación:

QA N0003

• Utilizar comentarios para autodocumentar los procesos desarrollados; documentar a medida que se desarrolla.

• Utilizar al menos dos parámetros que indiquen si el procedimiento debe realizar un COMMIT y si el procedimiento debe realizar un RAISE o un RETURN al momento de un error o situación inesperada.

• Al desarrollar incluir sentencias DBMS_OUTPUT al mismo tiempo a fin de poder rutear el código fácilmente, mientras no se disponga de una herramienta que permita realizar esta tarea.

7.050 Querys • Utilizar alias de tablas con sentido, y no letras únicamente, es decir, al menos tres

letras para un alias. • Utilizar alias en todas las columnas de las querys a fin de claridad, y no utilizar alias

iguales al nombre de la columna para aquellas que son claves foráneas; el alias puede ser el nombre la columna base o algún nombre significativo a su uso.

• En cualquier SELECT utilizar el EXCEPTION, y al menos la cláusula WHEN others THEN a fin de controlar cualquier error imprevisto, utilizando mensajes de error de acuerdo a la norma ya mencionada en el punto anterior.

• Esquematizar la escritura de las querys de acuerdo a un patrón definido, que puede ser:

SELECT car.cor_car_det FROM ti_car_rfi car WHERE car.ti_irf_cod_irf = ‘FD’ AND car.ti_irf_ti_tip_inv_cod_inv = ‘IF’ ORDER BY car.cor-car

• En el FROM incluir las tablas de derecha a izquierda de acuerdo al orden en que se ingrese a la query.

• Utilizar SUBQUERYS en el FROM y WHERE para condiciones complejas en base a otras tablas, principalmente en el FROM.

• En el WHERE las condiciones escribirlas de derecha a izquierda, por ejemplo, a una query se ingresa por la tabla OPE y se llega a la tabla CAR pasando por DET.

SELECT ope.cor_ope, det.cor_ope_orf, car.ti_irf_cod_irf FROM ti_car_rfi car, ti_det_orf det, ti_ope_rfi ope WHERE ope.fec_ing_ope = TRUNC(SYSDATE) AND det.ti_ope_rfi_cor_ope = ope.cor_ope AND car.ti_det_orf_cor_ope_orf = det.cor_ope_orf

• Todas las columnas que son valores constantes deben ir al principio de la query.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

19/50

Identificación:

QA N0003

8.000 REGLAS DE PROGRAMACIÓN PL/SQL

8.010 Encapsular consultas de una fila en funciones

8.010.1 Descripción de la regla

Colocar las consultas que devuelven sólo una fila en funciones, y luego llamar a dicha función para retornar la información.

8.010.2 Motivación

Evitar harcode de estas queries en los bloques de código

8.010.3 Ejemplo

En lugar de escribir: BEGIN SELECT title INTO l_title FROM book WHERE isbn = isbn_id … Crear una función: PACKAGE te_book IS FUNCTION title (isbn_IN book.isbn%TYPE) RETURN book.titel%TYPE; … Y ahora la aplicación será similar a: BEGIN l_title := te_book.title (isbn_id); …

8.010.4 Beneficios

• Mayor legibilidad del código resultante.

8.010.5 Desafíos

• Entrenar y disciplinar al equipo de desarrollo para adherir al estándar de encapsulamiento.

• Se deberá asignar un correcto tamaño de SGA para el manejo de grandes volúmenes de código.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

20/50

Identificación:

QA N0003

8.020 Ocultar el uso de la tabla DUAL


Ocultar el uso de la tabla dual a través de la definición de funciones.

8.020.2 Motivación

El uso de la tabla DUAL es una alternativa posible y valedera dentro de muchas más. Aunque siempre deberia evitarse su uso, en caso de ser necesario entonces encapsular el acceso por medio de funciones o procedimientos procedimientos, de tal manera que si se puede cambiar la implementación por otra alternativa al uso de esta tabla, sea transparente a las aplicaciones.

8.020.3 Ejemplo

• Código NO recomendado: DECLARE my_id INTEGER; BEGIN SELECT patient_seq.NEXTVAL INTO my_id FROM dual;

• Código recomendado: Crear una función...

CREATE OR REPLACE FUNCTION next_patient_id RETURN patient.patient_id%TYPE IS retval patient.patient_id%TYPE; BEGIN

SELECT patient_seq.NEXTVAL INTO retval FROM dual; RETURN retval;

END;

• Ahora la aplicación se vería de esta manera: DECLARE

my_id INTEGER; BEGIN

my_id := next_patient_id; …

8.020.4 Beneficios

• Mayor legibilidad del código.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

21/50

Identificación:

QA N0003

• Cambios en las implementaciones de las funciones no afectan la funcionalidad.

8.030 Definir cursores de múltiples filas en packag es


Definir cursores de múltiple filas en packages. Además de empaquetar las queries permite documentar los cursores en la especificación del package.

8.030.2 Motivación

Compartir cursores definidos a través de distintos programas. Armar catalogo de Queries usados por la aplicación. De ser necesario, ocultar la definición del SELECT.

8.030.3 Ejemplo

• Código recomendado Definición del Package:

CREATE OR REPLACE PACKAGE book_info IS SQL publico en la especificación

CURSOR byauthor_cur ( author_in IN books.author%TYPE) IS SELECT * FROM books WHERE author = author_in;

SQL oculto en el body del Package. Agregar RETURN. CURSOR bytitle_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE;

TYPE author_summary_rt IS RECORD (

author books.author%TYPE, total_page_count PLS_INTEGER, total_book_count PLS_INTEGER);

CURSOR summary_cur (author_in IN books.author%TYPE) RETURN author_summary_rt;

END book_info;

Usando Package Cursor: DECLARE onebook book_info.bytitle_cur%ROWTYPE; BEGIN OPEN book_info.bytitle_cur ('%PL/SQL%'); LOOP



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

22/50

Identificación:

QA N0003

EXIT WHEN book_info.bytitle_cur%NOTFOUND; FETCH book_info.bytitle_cur INTO onebook; book_info.display (onebook); END LOOP; CLOSE book_info.bytitle_cur; END;

8.030.4 Beneficios

• Sólo se debe escribir la query en un solo lugar. Se obtiene un catalogo de queries utilizadas.

• Mayor mantenibilidad, todos las modificaciones a los queries se realizan en un único lugar (el package de cursores)

• Reusabilidad del código. • Mayor seguridad. Ocultar definición del Select.

8.030.5 Desafíos

• Cerrar cursores explícitamente. Los cursores de paquetes son persistentes, y permanecen abiertos hasta cerrarse explícitamente o hasta la desconexión de la sesión. Esto es diferente a cursores definidos localmente que se cierran al finalizar el bloque actual.

• El equipo de desarrollo debe reunirse y armar un conjunto unico de consultas que formaran parte del package de cursores.

8.040 Usar registros (%rowtype) para fetch de curso res


En cursores, hacer el fetch sobre un registro (definido con %rowtype) y no sobre listas de variables.

8.040.2 Motivación

Es peligroso explicitar las variables y tipos retornados por un cursor en variables individuales, porque ante un cambio del cursor (por ejemplo, agregado de una columna) el código fallará. Usando registros, si el cursor cambia, puede recompilarse el código y automáticamente se adapta a la nueva definición del cursor.

8.040.3 Ejemplo

Suponer la siguiente declaración de un cursor en un package... PACKAGE book_pkg IS CURSOR books_by_category (category_in book.category%TYPE); IS



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

23/50

Identificación:

QA N0003

SELECT title, author FROM book WHERE category = category_in; END book_pkg;

• Código NO recomendado: Declaración de variables individuales

DECLARE l_title book.title%TYPE; l_author book.author%TYPE; BEGIN OPEN book_pkg.books_by_category(‘SCIFI’); FETCH book_pkg.books_by_category INTO l_title, l_author; … END;

• Código recomendado: Declaración de variable usando %rowtype

DECLARE scifi_rec book_pkg.books_by_category%ROWTYPE; BEGIN OPEN book_pkg.books_by_category (‘SCIFI’); FETCH book_pkg.books_by_category INTO scifi_rec; … END;

8.040.4 Beneficios

• El código se adapta automáticamente a los cambios en la estructura del cursor.

• Se escribe menos código. No es necesario definir variables individuales. • Mayor mantenibilidad. • Mayor legibilidad.

8.050 Evitar innecesario uso de COUNT


Evitar uso de SELECT COUNT(*) para verificar existencia de registros. Usar el COUNT exclusivamente cuando la cantidad actual de ocurrencias es requerida.

8.050.2 Motivación

Mejorar Performance: El chequeo previo de cantidad de registros (COUNT(*)) obliga a recorrer dos veces la tabla, una vez para chequear la cantidad y otra vez para procesarlos, lo cual degrada la performance.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

24/50

Identificación:

QA N0003

8.050.3 Ejemplo

Considerar el siguiente requerimiento: • Obtener el ID de la compañía que “machea” con un nombre dado. • Si no machea, retorna mensaje de error • Si machea con más de un registro, retorna lista de registros. • Si machea con un único registro retorna el ID de la compañía. ¿Como resolvería este requerimiento? • Caso NO recomendado:

BEGIN SELECT COUNT(*) INTO v_cantidad FROM company WHERE name LIKE :nombre;

IF v_cantidad = 1 THEN

SELECT company_id INTO v_id FROM company WHERE name = :nombre;

return_value (v_id); ELSIF v_cantidad > 1 THEN mostrar_lista ELSIF v_cantidad = 0 THEN display ('No matches found.'); END IF;

END; NOTA: Se ejecutan dos selects para obtener el id de la compañía.

• Caso recomendado DECLARE

CURSOR comp_cur IS ...; comp_rec comp_cur%ROWTYPE; BEGIN OPEN comp_cur; FETCH comp_cur INTO comp_rec; IF comp_cur%NOTFOUND



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

25/50

Identificación:

QA N0003

THEN display ('No match found.'); ELSE FETCH comp_cur INTO comp_rec; IF comp_cur%FOUND THEN show_list; ELSE :employee.company_id := comp_rec.company_id; :employee.company_nm := comp_rec.company_nm; END IF; END IF;

CLOSE comp_cur; END;

8.050.4 Beneficios

• Uso mínimo de CPU y memoria. • Evita duplicación de sentencias SQLs. • Mejora mantenibilidad ante cambios en modelo de datos, solo se

mantiene una único SQL. • Una traducción más exacta del requerimiento.

8.060 Utilizar cursor FOR-LOOP para procesar cursor es


Usar FOR-LOOP para manejar cursores incondicionales.

8.060.2 Motivación

• Código más legible. • Menor cantidad de código. • Menor chance de errores.

8.060.3 Ejemplo

Procesar todos los registros de un cursor. • Caso NO recomendado:

DECLARE CURSOR emp_cur IS ... ; emp_rec emp_cur%ROWTYPE; BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec;



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

26/50

Identificación:

QA N0003

EXIT WHEN emp_cur%NOTFOUND; give_raise (emp_rec.empno, 10000); END LOOP; CLOSE emp_cur; END;

8.060.4 Beneficios

• Evitar el esfuerzo de codificar la apertura, búsqueda (fetch) y cierre de los cursores.

• Código resultante más legible. • Código menos propenso a errores

8.060.5 Desafíos

• Luego del END LOOP no se tiene información del cursor procesado. Si fuera necesario información adicional (como filas procesadas) debe mantenerse mediante variables adicionales actualizadas en el bloque LOOP.

• Si el desarrollador no es cuidadoso, el código dentro del LOOP puede ser muy extenso.

8.070 No usar cursor FOR-LOOP para fetch de una fil a


No utilizar cursores que retornen una única fila con un FOR loop.

8.070.2 Motivación

Un cursor FOR-LOOP es menos eficiente que un SELECT-INTO o un OPEN-FETCH-CLOSE cuando se trata de recorrer sólo una fila.

8.070.3 Ejemplo

CREATE OR REPLACE FUNCTION book_title ( isbn_in IN book.isbn%TYPE ) RETURN book.title%TYPE IS CURSOR title_cur IS SELECT title FROM book WHERE isbn = isbn_in; l_rec title_cur%ROWTYPE;

BEGIN

OPEN title_cur; FETCH title_cur INTO l_rec;



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

27/50

Identificación:

QA N0003

CLOSE title_cur; RETURN l_rec.title;

END;

• Caso NO recomendado.

No usar For Loop

BEGIN FOR rec IN title_cur LOOP

l_rec := rec; END LOOP; RETURN l_rec.title;

END;

• Caso recomendado:

Usar SELECT INTO

l_title book.title%type;

BEGIN SELECT title INTO l_title FROM book WHERE isbn = isbn_in;

RETURN l_title;

END;

8.070.4 Beneficios

• Legibilidad: El código satisface el requerimiento de la forma más directa y clara.

• Performance: Un cursor FOR-LOOP es menos eficiente que un SELECT-INTO o un cursor explícito para retornar una fila.

8.080 Especificar columnas a actualizar en SELECT F OR UPDATE


Especificar las columnas a actualizar en SELECT FOR UPDATE.

8.080.2 Motivación

Lock de las filas a ser actualizadas. Evitar que otra sesión cambie las filas afectadas por el cursor.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

28/50

Identificación:

QA N0003

8.080.3 Ejemplo

Actualizar el sabor de helado preferido por la familia PEREZ DECLARE CURSOR change_prefs_cur IS SELECT PER.name, PREF.name flavor FROM person PER, preference PREF

WHERE PER.name = PREF.person_name AND PREF.type = ‘ HELADO’ FOR UPDATE OF PREF.name;

BEGIN FOR rec IN change_prefs_cur LOOP IF rec.name LIKE ‘PEREZ’ THEN UPDATE preference SET name = ‘CHOCOLATE’ WHERE CURRENT OF change_prefs_cur; END IF; END LOOP; END;

8.080.4 Beneficios

• Mantener la mínima cantidad de locks sobre una tabla. • Auto documentar el comportamiento del código.

8.090 Parametrizar cursores explícitos


Parametrizar los cursores explícitos (evitar hardcode en los where).

8.090.2 Motivación

Permite que el cursor sea reusado en diferentes procesos, es un complemento de la regla SQL-04.

8.090.3 Ejemplo

• Caso NO encomendado DECLARE CURSOR r_and_d_cur IS SELECT last_name FROM employee WHERE department_id = 10; --Hardcode!! BEGIN OPEN r_and_d_cur;

• Caso recomendado Crear un pkg de cursores…



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

29/50

Identificación:

QA N0003

CREATE OR REPLACE PACKAGE dept_info_pkg IS CURSOR name_cur (dept IN INTEGER) IS --usar parámetros SELECT last_name FROM employee WHERE department_id = dept; --evita hardcode y utiliza bindVariable.

Y abrirlo… BEGIN OPEN dept_info_pkg.name_cur (10); … END;

O mejor aún, evitar harcode del literal…

DECLARE r_and_d_dept CONSTANT PLS_INTEGER :=10; BEGIN OPEN dept_info_pkg.name_cur ( r_and_d_dept ); … END;

8.090.4 Beneficios

• Mayor nivel de reusabilidad en las aplicaciones. • Menor Mantenimiento. • Mejoras en performance, ya que los parámetros de los cursores son

tratados como binded variables y no se necesita parsearlo en cada ejecución.

8.0100 Utilizar cláusula RETURNING


Usar RETURNING para retornar información de filas que se modifican.

8.0100.2 Motivación

Disminuye la cantidad de código generado.

8.0100.3 Ejemplo

Suponer que se usa una secuencia para generar la PK de una tabla, y luego se necesita ese número para un posterior proceso…



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

30/50

Identificación:

QA N0003

INSERT INTO patient (patient_id, last_name, first_name) VALUES (patient_seq.NEXTVAL, ‘FEUERSTEIN’, ‘STEVEN’) RETURNING patient_id INTO l_patient_id;

8.0100.4 Beneficios

• Mejora la performance de las aplicaciones. • Reduce el volumen del código.

8.0110 Usar cláusula BULK COLLECT


Usar BULK COLLECT para mejorar la performance de queries con múltiples filas.


Necesidad de retornar gran cantidad de filas de la base de datos. Esto retira las filas en un solo pedido al motor de la base.

8.0110.3 Ejemplo

CREATE OR REPLACE PROCEDURE process_employee ( deptno_in dept.deptno%TYPE) RETURN emplist_t IS TYPE numTab IS TABLE OF emp.empno%TYPE; TYPE chartTab IS TABLE OF emp.ename%TYPE; TYPE dateTab IS TABLE OF emp.hiredate%TYPE; enos numTab; names charTab; hdates dateTab; BEGIN SELECT empno, ename, hiredate BULK COLLECT INTO enos, enames, hdates FROM emp WHERE deptno = deptno_in; … END;

Si se utiliza un cursor explícito… BEGIN



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

31/50

Identificación:

QA N0003

OPEN emp_cur INTO emp_rec; FETCH emp_cur BULK COLLECT INTO enos, enames, hdtaes;

8.0110.4 Beneficios

• Mejora la performance de las aplicaciones.

8.0110.5 Desafíos

• Se debe declarar una colección por cada columna de la lista del SELECT.

• Se debe ser cuidadoso al usar esto, ya que puede quedarse sin memoria debido al alto número de filas retornadas.

8.0120 Encapsular sentencias DML en llamadas a proc edures


Encapsular los INSERTs, UPDATEs y DELETEs en procedimientos.


Mayor legibilidad y consistencia en el manejo de errores.

8.0120.3 Ejemplo

En vez de escribir un INSERT como sigue: INSERT INTO book (isbn, title, author) VALUES (...)

Usar un procedure:

Add_book (...); O un procedimiento de un package:

Te_book.ins (...)

8.0120.4 Beneficios

• La aplicación se ejecuta mas rápido, por reutilizar el mismo insert, realizando menos parseo y reduce la demanda de memoria SGA.

• La aplicación maneja de manera consistente los errores relacionados con DML "

8.0120.5 Desafíos

• Se necesita generar más código procedural. • Se podría necesitar crear procedimientos múltiples de UPDATE.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

32/50

Identificación:

QA N0003

8.0130 Uso del ROWCOUNT


Muchas veces es necesario verificar la cantidad de registros modificados por una DML. En estos casos, debe utilizarse SQL%ROWCOUNT.


Asegurarse que el DML se haya ejecutado apropiadamente. Ya que en un UPDATE o DELETE no levanta excepción si ninguna fila es afectada.

8.0130.3 Ejemplo

BEGIN UPDATE book SET author = 'PEREZ, PEDRO' WHERE author = 'PEREZ, JUAN'; IF SQL%ROWCOUNT < 8 THEN ROLLBACK; END IF;

END;

8.0130.4 Beneficios

Los programas son verificados y están mejor habilitados para manejar problemas más eficientemente.

8.0140 Referenciar atributos de cursores inmediatam ente después de la operación SQL


Referenciar atributos de cursores inmediatamente después de la operación SQL. Las instrucciones DML son ejecutadas por cursores implícitos en PL/SQL. Los atributos de cursores reflejan lo realizado en la última operación implícita. Debería mantenerse al mínimo la cantidad de código entre las operaciones DML y la referencia a algún atributo del cursor.


Obtener información sobre los resultados de la más reciente operación implícita realizada.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

33/50

Identificación:

QA N0003

8.0140.3 Ejemplo

DECLARE PROCEDURE show_max_count IS l_total_pages PLS_INTEGER; BEGIN SELECT MAX (page_count) INTO l_total_pages FROM book WHERE title LIKE ‘%PL/SQL%’; DBMS_OUTPUT.PUT_LINE(l_total_pages); END; BEGIN UPDATE book SET page_count = page_count / 2 WHERE title LIKE ‘%PL/SQL%’; show_max_count; DBMS_OUTPUT.PUT_LINE(‘ pages adjusted in ‘|| SQL%ROWCOUNT || ‘ books’); END;

En este ejemplo, entre el UPDATE y la referencia a SQL%ROWCOUNT, se ejecuta el cursor implícito del SELECT MAX. El resultado de esto dependerá del cursor del SELECT y no del UPDATE.

8.0150 Usar Bind Variables


• Utilizar Bind Variables en lugar de valores “Harcode “. • No concatenar los valores de variables en SQLs dinámicos, utilizar BIND

VARIABLES. De esta manera Oracle parsea la versión genérica del SQL, la cual puede ser ejecutada una y otra vez sin importar el valor actual de la variable.

• Usar Bind Variables para querys que son ejecutadas muchas veces, Ej. Ambientes OLTP.


• Evita reparsear las consultas para cada cambio de valores en la consulta. Esto se traduce en mejor performance de la consulta.

• Evita hacer una copia de la misma consulta por cada cambio de valores. Solo se mantiene una única consulta en la shared pool que es compartida por todas las sesiones. El efecto de esto es que se reduce la cantidad de memoria utilizada de la shared pool.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

34/50

Identificación:

QA N0003

8.0150.3 Ejemplo

Usando Bind Variable en Sql Dinámico. Actualizar cualquier columna numérica en la tabla especificada, basados en el nombre pasado como parámetro: CREATE OR REPLACE PROCEDURE updnuval ( tab_in IN VARCHAR2, namecol_in IN VARCHAR2, numcol_in IN VARCHAR2, name_in IN VARCHAR2, val_in IN NUMBER ) IS cur PLS_INTEGER; Fdbk PLS_INTEGER; BEGIN cur := open_and_parse ( ‘ UPDATE ’ || tab_in || ‘ SET ‘ || numcol_in || ‘ WHERE ’ || namecol_in || ‘ LIKE : name ‘ ) ; DBMS_SQL.BIND_VARIABLE ( cur, ‘ val ‘ , val_in ); DBMS_SQL.BIND_VARIABLE ( cur, ‘ name ‘ , name_in ); fdbk := DBMS_SQL.EXECUTE ( cur ); DBMS_SQL.CLOSE_CURSOR ( cur ); END;

8.0150.4 Beneficios

• La SGA requiere menos memoria para los cursores de los SQL Dinámicos.

• La performance de la aplicación se incrementa al reducirse el parseo. • Escribiendo SQL Dinámico con bind variables se conseguirá una forma

más fácil y menos propensa a errores.

8.0160 Formatear SQL dinámicos


Formatear los strings de los SQL dinámicos para que sean más fáciles de leer y mantener.

8.0160.2 Ejemplo

Alternativas para formatear el mismo SQL…



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

35/50

Identificación:

QA N0003

v_sql := ‘ DECLARE ’ || ‘ CURSOR curs_get_orders IS ’ || ‘ SELECT * FROM ord_order; ‘ || ‘ BEGIN ‘ || ‘ FOR v_order_rec IN curs_get_orders LOOP ‘ || ‘ process_order ( v_order_rec.order_id ) ‘ || ‘ END LOOP; ‘ || ‘ END; ‘ ; v_sql := ‘ DECLARE CURSOR curs_get_orders IS SELECT * FROM ord_order; BEGIN FOR v_order_rec IN curs_get_orders LOOP process_order ( v_order_rec.order_id ) ; END LOOP; END ‘ ;

8.0160.3 Beneficios

Leer y mantener el código más fácilmente.

8.0160.4 Desafíos

Es extremadamente importante convenir un estándar con los desarrolladores para formatear los SQL dinámicos.

8.0170 Optimización basada en costos


Usar optimización basada en costos para SQL o DMLs. Para esto la base de datos no debe estar configurada con OPTIMIZER_MODE=RULE y las tablas deben estar analizadas. Por otro lado, en cuanto a la programación, no está permitido alterar las sesiones a reglas ni usar hints de RULE.


El optimizador basado en costos genera mejores planes de ejecución que el de reglas. A partir de Oracle 8i el optimizador basado en costos no tiene tantos problemas como en versiones anteriores. El optimizador basado en reglas no está siendo actualizado por Oracle, por lo tanto muchas formas de acceder a los datos no están disponibles por reglas (partition prunning, hash join, bitmap indexes, etc).



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

36/50

Identificación:

QA N0003

8.0180 No utilizar hints en SQL


No utilizar hint dentro de definiciones de sentencias SQL.


• Usar hints es una forma de hardcoding (en este caso se hardcodea el plan de ejecución de una consulta).

• Requieren mayor mantenimiento de código. • Un plan de ejecución puede ser bueno en un momento y malo en el

futuro debido a cambios en la cantidad y calidad de los datos subyacentes.

8.0190 Evitar Sorts


Evitar las operaciones de Sorts siempre que sea posible. Las operaciones que producen Sorts son las siguientes: • Cláusulas ORDER BY o GROUP BY. • Cláusula DISTINCT. • Operadores INTERSECT, UNION, MINUS. • Join Sort-Merge. • Ejecución del comando ANALYZE.


Las operaciones de Sorts consumen una excesiva cantidad de recursos al eliminarlos se mejora la performance de las aplicaciones.

8.0190.3 Alternativas para Evitar Sorts

Algunas alternativas para evitar sorts innecesarios: • Usar UNION ALL en lugar de UNION. Esto evita la eliminación de

duplicados. • Acceder a tablas por índices. De esta manera el optimizador realizara

nested loop join en lugar de sort-merge-join. • Crear índices que contengan los campos utilizados frecuentemente en

cláusulas ORDER BY y/o GROUP BY. El optimizador Oracle utilizara el índice en lugar de hacer una operación de Sorts, ya que el índice se encuentra ordenado.

• Para tomar estadísticas, usar ESTIMATE en lugar de COMPUTE. Usar DBMS_STATS para tomar estadísticas solo de ciertas columnas, de una tabla determinada.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

37/50

Identificación:

QA N0003

8.0200 Evitar conversiones de tipos de datos implíc itas


Evitar conversión de tipos de datos implícitas al escribir SQLs y código PL/SQL y evitar conversiones de datos implícitas.


• El comportamiento de las conversiones puede no ser intuitivo. • Las reglas de conversión no están bajo el control del desarrollador.

8.0200.3 Ejemplo I

Forma Incorrecta:

DECLARE my_birthday DATE := ’09-SEP-58’;

Forma Correcta:

DECLARE my_birthday DATE := TO_DATE (’09-SEP-58’,’DD-MM-RR’);

8.0200.4 Ejemplo II

Forma Incorrecta:

SELECT Campo 1 FROM TABLA WHERE Camp1 = ‘09-SEP-58’;

Forma Correcta:

SELECT Campo 1 FROM TABLA WHERE Campo1 = TO_DATE( ‘09-SEP-58’,’DD-MM-RR’)

8.0200.5 Beneficios

El comportamiento del código es más predecible y consistente, puesto que no depende de recursos externos al código.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

38/50

Identificación:

QA N0003

8.0210 Uso de Exists vs IN


• Usar EXIST en lugar de IN para subquerys, si el predicado de selección esta en el Query principal.

• Usar IN en lugar de EXISTS para subquerys, si el predicado de selección esta en el subquery

Nota: Siempre hay excepciones a la regla pero esa es la regla general.


• Select * from T1 where x in ( select y from T2 ) Esta consulta es procesada de la siguiente manera:

select * from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y;

� El subquery es evaluado y luego es joineado con la tabla original.

El caso opuesto es: o select * from t1 where exists ( select null from t2 where y=x ) Esta consulta es procesada de la siguiente manera:

for x in ( select * from t1 ) loop if ( exists ( select null from t2 where y = x.x ) then --retornar registro end if end loop

o Cuando es mejor usar EXISTS o IN?

Caso 1)

� Supongamos que el resultado de la subquery select y from t2 es “enorme” y toma mucho tiempo.

� La tabla t1 es pequeña y ejecutar ( select null from t2 where y = x.x ) es mucho mas rápido ya que existe un índice en t2(y).

� Entonces en este Caso el EXISTS es más apropiado que utilizar IN.

Caso 2)

� Supongamos que el resultado de la subquery (select y from t2) es “chico”.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

39/50

Identificación:

QA N0003

� Entonces IN es generalmente mas apropiado que utilizar EXISTS

Caso 3)

� Si ambas, el subquery y la tabla externa son “enormes”.

� Entonces Entonces Entonces Entonces no se puede asegurar cual de los dos no se puede asegurar cual de los dos no se puede asegurar cual de los dos no se puede asegurar cual de los dos

será mas apropiado, dependerá de los índices y será mas apropiado, dependerá de los índices y será mas apropiado, dependerá de los índices y será mas apropiado, dependerá de los índices y

de otros factores.de otros factores.de otros factores.de otros factores.

8.0210.3 Ejemplo

• Creación del ambiente:

rem create table big as select * from all_objects; rem insert /*+ append */ into big select * from big ; rem commit; rem insert /*+ append */ into big select * from big ; rem commit; rem insert /*+ append */ into big select * from big ; rem create index big_idx on big(object_id); rem rem rem create table small as select * from all_objects where rownum < 100; rem create index small_idx on small(object_id); rem rem analyze table big compute statistics rem for table rem for all indexes rem for all indexed columns rem / rem analyze table small compute statistics rem for table rem for all indexes rem for all indexed columns rem /

Resultado:

o Tabla SMALL tiene 99 registros, o Tabla BIG tiene 133.000 registros

CASO 1) Tabla exterior Enorme. Subquery retorna poc os registros. • Usando IN:

select count(subobject_name) from big where object_id in ( select object_id from small ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.02 0.02 0 993 0 1



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

40/50

Identificación:

QA N0003

------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- total 4 0.03 0.03 0 993 0 1 Rows Execution Plan ------- ------------------------------------------ --------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 792 MERGE JOIN 100 SORT (JOIN) 100 VIEW OF 'VW_NSO_1' 99 SORT (UNIQUE) 792 INDEX GOAL: ANALYZED (FULL SCAN) O F 'SMALL_IDX' (NON-UNIQUE) 891 SORT (JOIN) 0 TABLE ACCESS GOAL: ANALYZED (FULL) O F 'BIG'

• Usando EXISTS

select count(subobject_name) from big where exists ( select null from small where small. object_id = big.object_id ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 4.12 4.12 0 135356 15 1 ------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- total 4 4.12 4.12 0 135356 15 1 Rows Execution Plan ------- ------------------------------------------ --------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 792 FILTER 135297 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'BIG' 133504 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'SMALL_IDX' (NON-UNIQUE)

• Conclusión: Cuando la tabla exterior es GRANDE y la subconsulta es pequeña entonces es mas eficiente utilizar IN

CASO 2) Tabla exterior Chica. Subquery retorna much os registros.

• Usando IN:

select count(subobject_name) from small where object_id in ( select object_id from big ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.51 0.82 50 298 22 1 ------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- total 5 0.52 0.83 50 298 22 1



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

41/50

Identificación:

QA N0003

Rows Execution Plan ------- ------------------------------------------ --------- 0 SELECT STATEMENT GOAL: CHOOSE 1 SORT (AGGREGATE) 99 MERGE JOIN 16913 SORT (JOIN) 16912 VIEW OF 'VW_NSO_1' 16912 SORT (UNIQUE) 135296 INDEX GOAL: ANALYZED (FAST FULL SC AN) OF 'BIG_IDX' (NON-UNIQUE) 99 SORT (JOIN) 99 TABLE ACCESS GOAL: ANALYZED (FULL) O F 'SMALL'

• Usando EXISTS:

select count(subobject_name) from small where exists ( select null from big where small.ob ject_id = big.object_id ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.01 0 204 12 1 ------- ------ -------- ---------- ---------- ---- ------ ---------- ---------- total 4 0.01 0.01 0 204 12 1 EGATE) 99 FILTER 100 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SMALL' 99 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE)

• Conclusión: Cuando la tabla exterior es pequeña y l a subconsulta es GRANDE entonces es mas eficiente utilizar EXISTS .

8.0210.4 Beneficios

• Mejorar performance de SQLs cuando se deben utilizar Subquerys.

8.0220 No usar variables globales


Sólo usar variables globales en packages si no existe otro modo de resolverlo y usarlas exclusivamente en el body para casos en que se necesite persistencia a nivel de sesión.


Evitar dependencias ocultas y efectos laterales.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

42/50

Identificación:

QA N0003

8.0220.3 Ejemplo

Función con una dependencia oculta sobre una variable global…

CREATE OR REPLACE FUNCTION overdue_fine ( isbn_in IN book.isbn%TYPE) RETURN NUMBER IS l_days_overdue NUMBER; BEGIN l_days_overdue := overdue_pkg.days_overdue (isbn_in, SYSDATE); RETURN ( l_days_overdue * overdue_pkg.g_daily_fine); --Variable Global END;

Se puede reemplazar la dependencia agregando un parámetro…

CREATE OR REPLACE FUNCTION overdue_fine ( isbn_in IN book.isbn%TYPE, daily_fine_in IN NUMBER) RETURN NUMBER IS l_days_overdue NUMBER; BEGIN l_days_overdue := overdue_pkg.days_overdue (isbn_id, SYSDATE); RETURN (l_days_overdue * daily_fine_in); END;

8.0220.4 Beneficios

• Reduce la interdependencia entre programas. • Modificar un programa sin la preocupación de afectar a otros programas.

8.0220.5 Desafíos

Es necesario recodificar los programas para reemplazar las referencias globales por parámetros.

9.000 REGLAS PARA USO DE INDICES

9.010 Pautas de Indización • Crear índices solo cuando fuese necesario. • Crear un índice para acelerar una consulta puede afectar a otras. • Eliminar índices innecesarios. • Evaluar el plan de ejecución a fin de corroborar la utilización de los índices por el

optimizador.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

43/50

Identificación:

QA N0003

9.020 Tipos de índices

9.020.1 Índices compuestos (concatenated index).

Es un índice creado con mas de una columna (hasta 32). Las columnas en un índice compuesto pueden aparecer en cualquier orden y no necesitan ser adyacentes en la tabla.

9.020.2 Unique and nonunique indexes

Unique index garantiza que no haya dos claves con el mismo valor.

9.020.3 Almacenamiento de índices.

• B*-tree Normal Clave invertida (Reverse key) Descendente Basado en funciones

• Bitmap • Comprimidos

9.030 Cuando indizar • Las claves de los índices deben tener una alta selectividad. La selectividad de un

índice esta dada por la cantidad de distintas claves, sobre el total de tuplas que posee la tabla. Una buena selectividad es lograda cuando son pocas las claves que tienen un mismo valor.

• Considere indexar claves utilizadas frecuentemente en cláusulas WHERE. • Considere indexar columnas utilizadas frecuentemente en join’s de tablas. • No utilice índices B*-tree con claves o expresiones con pocos valores distintos ya

que estos tienen baja selectividad y no mejoran la performance. • No indexar columnas que son frecuentemente modificadas. Update / Insert y

delete’s que modifican tablas indexadas, demoran mas que tablas no indexadas. • No indexé claves que aparezcan en una cláusula WHERE utilizando funciones. Una

cláusula WHERE que utilice funciones que no sean MIN o MAX, no tendrán disponible el índice, excepto que el índice, sea basado en funciones.

9.040 Pautas para índices compuestos • Coloque primero (“leading part”) las columnas de mayor selectividad. • Coloque primero las columnas accedidas con mayor frecuencia. • Si especificara la clave entera, coloque primero la columna mas restrictiva. • Utilice la opción COMPRESS cuando sea posible.

9.050 Indices y valores Null • Los valores NULL en índices se consideran distintos excepto cuando todos los

valores not-null en dos o más filas de un índice son idénticos, en ese caso las filas se consideran idénticas.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

44/50

Identificación:

QA N0003

• Los índices Unique evitan que las filas que contienen valores NULL sean tratadas como idénticas. Oracle no pone en un índice las filas de la tabla en las cuales todas las columnas de la clave son NULL.

9.060 Índices basados en funciones Los índices basados en funciones proporcionan un mecanismo eficiente para las consulta que contienen funciones en su cláusula WHERE. Los índices basados en funciones precomputa el valor de la función o de la expresión y lo almacena en el índice. Se puede crear un índice basado en función como un b-tree o un índice Bitmap.

9.060.1 Condiciones para utilizar índices basado en funciones

• La función debe ser determinística. Es decir, que dado y=f(x) para el mismo x, siempre devuelve el mismo y.

• Ejemplo función NO deterministica: f(x) = to_char(sysdate,’ddmmyyyy’)||x • La sesión debe tener seteado el parámetro Query_rewrite_enabled=true. Ejemplo: CREATE INDEX FBI_UPPER_LASTNAME ON CUSTOMERS(upper(cust_last_name));

9.070 Como se almacenan los índices Oracle utiliza los índices del tipo b-tree que se balancean para igualar tiempos de acceso a cualquier fila. La teoría de los índices del b-tree está más allá del alcance de este documento. Los bloques altos ((branch blocks) de un índice de tipo b-tree contiene datos que apuntan a los bloques de índice de nivel inferior. Los bloques del índice de nivel inferior (leaf blocks) contienen los valores de los rowid de los datos usado para localizar la fila real. Los leaf blocks estan doblemente enlazados. Los índices en las columnas que contienen datos de carácter se basan en los valores binarios de los caracteres. Para un índice unique, hay un rowid por valor de los datos. Para un índice del nonunique, el rowid incluye en la calve un orden de clasificación, así que los índices del nonunique son clasificados por la clave y el rowid del índice. Las claves que contienen todos valores null no se indexan.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

45/50

Identificación:

QA N0003

9.080 Índices de clave invertida Crear un índice de clave invertida, comparado a un índice estándar, invierte los bytes de cada columna puesta en un índice (excepto el rowid) mientras que guarda el orden de la columna. Tal arreglo puede ayudar a evitar la contención en disco cuando las modificaciones al índice se concentran en bloques de la ultima hoja. Invirtiendo la clave del índice, las inserciones se distribuyen a través de todas las hojas del índice. Utilizar un índice con clave invertida elimina la posibilidad de hacer un range scan sobre el mismo. Porque las claves lógicamente adyacentes no se almacenan de forma contigua. sólo se pueden realizar consultas por clave o accesos full.

9.090 Índices Bitmap Oracle almacena cada valor de clave con cada rowid almacenado. Cada bit en el BITMAP corresponde a un rowid posible. Si se fija el bit, entonces significa que la fila con el rowid correspondiente contiene la misma clave. Una función convierte la posición del bit a un rowid real, así que el índice Bitmap proporciona la misma funcionalidad que un índice regular aunque es distinto internamente.

9.090.1 Creación de índices Bitmap

CREATE BITMAP INDEX prod_supplier_id ON sh.products (supplier_id);



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

46/50

Identificación:

QA N0003

9.090.2 Estructura

Row Value

Suplier Id = 1

Suplier Id = 2

Suplier Id = 3

Suplier Id = 4

…

‘1’ 1 0 0 0 ‘2’ 0 1 0 0 ‘3’ 0 0 1 0 ‘2’ 0 1 0 0 ‘3’ 0 0 1 0

9.090.3 Ejemplo de uso

SELECT * FROM products WHERE supplier_id = 3;

Suplier Id = 3

0 0 1 Fila Retornada 0 1 Fila Retornada

9.090.4 Recomendaciones

• Usarlos en columnas con baja cardinalidad, es decir el numero de valores distintos es pequeño comparado con el nro de filas de la tabla. Ejemplo, Sexo, Estados, Tipos, etc

• Ocupan menos espacio y son mas eficientes que los índices B*Tree cuando la columna es de baja cardinalidad.

• Los índices bitmap no son convenientes para los usos de OLTP con una gran cantidad de transacciones concurrentes que modifican los datos. Estos índices son performantes para DDS donde los usuarios consultan los datos con pocas actualizaciones diarias.

• Este tipo de índices tienen tendencia a desbalancearse. Por lo tanto cuando se realizan grandes movimientos de datos es recomendable reconstruirlos.

• En procesos batch que implique movimiento de muchos datos es recomendable hacer lo siguiente:

o Deshabilitar Indices Bitmap o Ejecutar procesos Batch o Reconstruir indices Bitmap (rebuild Index)



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

47/50

Identificación:

QA N0003

9.0100 Índices particionados

9.0100.1 Local index

En un local index, todas las claves en una partición particular del índice se refieren exclusivamente a las filas almacenadas en una sola partición de la tabla. Este tipo de índice tiene en algunas condiciones, mejores tiempos de respuesta y permite un mejor y mas rápido mantenimiento. 9.0100.1.i Local prefixed

Un local index es prefixed si las columnas de particionamiento de la tabla se corresponden con las primeras del índice.

9.0100.1.ii Local nonprefixed Un local index es non-prefixed si las columnas de particionamiento de la tabla no se encuentran a la izquierda del índice.

9.0100.2 Global prefixed index

Oracle no suporta global non-prefixed indexes. En un global index, las claves de una partición particular del índice pueden referir a las filas almacenadas en más de un partición o subpartition de la tabla. Para mas detalles ver documento “EXS-Tablas Particionadas”

9.0110 Efecto de operaciones DML sobre tablas index adas. Luego de periodos de alta actividad de DMLs, se deben reorganizar los índices B*-tree. Estos índices reconstruidos sobre datos existentes son mas eficientes que los mantenidos implícitamente por el Oracle Server.

• Los inserts sobre la tablas, genera la inserción de una entrada en el bloque

correspondiente del índice. Pudiéndose generar un (block split). • Los deletes sobre tablas, genera la eliminación de una entrada en el bloque

correspondiente del índice. Pudiéndose generar la liberación del bloque. • Los updates sobre tablas, genera la eliminación de la vieja entrada y la inserción de

una nueva entrada en el bloque correspondiente del índice.

9.0120 Índices y Constraints • Cuando se define una primary o unique key constraint sobre una tabla, el motor

genera automáticamente un índice (o utiliza uno existente) para soportarlo. • No se puede eliminar índices que se utilizan para cumplir con alguna constraint.

Para hacer esto, se debe dropear primero la contraint y luego el índice. • Los índices responden por lo menos a dos propósitos:

o Para realizar consultas mas performantes o Hacer cumplir claves únicas y primarias.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

48/50

Identificación:

QA N0003

9.0130 Índices y claves foraneas El motor de base de datos, no crea automáticamente índices para claves foráneas. Deletes o updates sobre parent-child tables (con una foreing key constraint) causan diferentes lockeos dependiendo de la presencia de índices sobre las columnas de la Foreign key. Si no se indexa la Foreign key, la tabla child sufre un lock (table-level lock) siempre que ocurra lo siguiente:

• Un registro es borrado de la tabla padre. • La columna apuntada por la Foreign key es actualizada en la tabla padre. • Por este motivo para evitar lockeos a nivel de tabla se creaban índices aun

cuando las columnas que lo componían no eran utilizadas en cláusula WHERE.

En la base de datos de Oracle9i, se ha mejorado este comportamiento y ya no es necesario crear índices para evitar los lockeos antes descriptos.



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

49/50

Identificación:

QA N0003

10.000 ANEXO

10.010 Tipos de Objetos

Letra Descripción C Paquetes F Funciones G Triggers I Indices L Clusters O Tipo Objeto P Procedimientos PK Constraints. Primary Key FK Constraints. Foreign Key UK Constraints. Unique Key CH Constraints. Check Constraints RS Rol de Sistema S Secuencia T Tablas V Vistas VM Vistas Materializadas



PRM15DI2000


Sergio Aguilera E.


Sergio Aguilera E.

Aprobación:

Darío Campos

Página:

50/50

Identificación:

QA N0003

10.020 Letras Asignadas a Sistemas

Letra Sistema A Acciones B Informes de Gestión, Gestión Banco C Consultas de Gestión CI Club de Inversiones D Dólar E Estudios F Fondos Mutuos G Modelo Global H Fiscalía I Inversiones IW Inversiones Web J Inversiones Sistemáticas K Facturación de Factoring; Contabilidad L Liquidación de Operaciones M Informes Marketing N Clientes Potenciales O Conciliación Bancaria P Pago de Comisiones PM PMO Q Administración de Retail R Riesgo Multiproductos (Líneas de Crédito) S Informes para SVS T Tributario U Cartera Mercado de Fondos Mutuos V Ahorro Previsional Voluntario VC Ahorro Previsional Voluntario Colectivo W Internet WP Internet Pro - Portal Privado de Clientes Y Control Operacional WF Work Flow CPT Negocios Internacionales

Norma de programacion plsql

Documents

Transcript of Norma de programacion plsql