====== Buenas prácticas de programación con SQL y PL/SQL ======

Extraído de una presentación de Steven Feurestein https://community.oracle.com/servlet/JiveServlet/download/915011-2-163991/Make%20No%20Small%20Plans%20PDF.pdf

  * Reutilización
    * Usa bind variables (con JDBC usar parámetros https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html#create_ps).
    * Usa código almacenado en la BD (paquetes, procedimientos, funciones, etc).
    * Usa vistas materializadas en lugar de vistas con DBlinks
  * Acceso a Datos
    * Calcula estadísticas de tablas, columnas e índices
    * El acceso más rápido a un datos es obteniendo directamente la fila (ROWID).
    * Revisa el plan de ejecución (explain plan)
  * Seguridad
    * Usa bind variables.
    * El usuario que ejecuta la aplicación NO puede ser el propietario de las tablas.
    * Construye una API con PL/SQL para consultar y actualizar los datos, de forma que el usuario que ejecuta la aplicación NO tenga acceso directo a las tablas.
  * Con PL/SQL solo se comparten en SGA las constantes estáticas de paquete (p.e. valor fijo de nº o string, pero NO un valor de secuencia).
  * Minimizar los cambios de contexto entre motor PL/SQL y SQL, que ocurren cada vez que se ejecuta una sentencia SQL dentro de PL/SQL.
    * No repetir sentencias (usar funciones/procedures)
    * Usar BULK COLLECT y FORALL
    * Una función en una sentencia SQL, a su vez, NO puede contener ninguna sentencia SQL (debe ser determinística).
  * Hacer GTs para estandarizar la escritura de SQL:
    * Estar al día sobre las novedades en SQL
    * ¿cómo, cuándo y dónde escribir SQL?
  * Novedades SQL
    * Funciones analíticas, como LAG y LEAD q permiten trabajar con las filas anterior y siguiente para calcular diferencias.
    * WITH, para definir "vistas" reutilizables en una sentencia compleja.
    * Flasback query.
    * ANSI JOIN: cambiar "(+)" por sintaxis completa.
    * Usar subconsultas como si fuesen funciones.
    * Novedades 12.1
      * Patrones (patter matching) y limit (row limiting)
      * Columnas en memoria (in-memory column store)
      * JSON nativo
  * Simplificar SQL (fuera sentencias complejas)
    * NUNCA repitas una sentencia SQL.
    * Oculta las sentencias SQL en una capa de acceso a datos.
    * SQL como servicio = vistas + paquetes
    * Gestión de excepciones
    * NO consultes tablas directamente, hazlo mediante vistas.
    * Separa datos y código en esquemas diferentes.
    * Rutas absolutas para todas las columnas y variables q referencias en una consulta.
  * Gestión de excepciones (ejemplo Sistema de Error Logger https://github.com/OraOpenSource/Logger)
    * Pasarle al Sistema de Log las variables locales y el estado de la app.
    * Usar transacciones autónomas para el Sistema de Log.
    * Usar  EXCEPTION_INIT para asignarle nombre a los errores, NO usar directamente nºs y/o mensajes de error.
  * Trazas (solo en el entorno de producción, por defecto desactivada).
    * Usar Oracle Trace, NUNCA dbms_output.put_line()
    * Usar DBMS_APPLICATION_INFO
    * Tb puedes usar Logger (visto antes) https://github.com/OraOpenSource/Logger
  * Testing
    * SQLdeveloper Unit Testing
    * [[plsql:utplsql|Test unitarios del código PL/SQL con utPLSQL]]
  * Escribe Código Mantenible
    * Regression test
    * NO repetir nada (no solo constantes, tampoco varchar2 y lógica de negocio).
    * Usar %TYPE y %ROWTYPE (en lugar de varchar2)
    * Usar funciones para encapsular fórmulas y lógica de negocio
    * Minimizar la parte ejecutable de los bloques, NO más de 50 líneas.
    * Ocultar expresiones complejas en constantes o funciones.
  * Optimizar el código
    * Cachear (function result cache, q solo está en versión enterprise de Oracle)
    * IMPORTANTE: la optimización de BD (DBA) no llega a más del 20% del efecto, el 80% se consigue escribiendo código mantenible.
    * Identificar cuellos de botella haciendo tuning más granular, usando DBMS_PROFILER y DBMS_HPROF.
    * Usar variables de paquete (colecciones) para cachear tablas pequeñas de códigos, estáticas, teniendo en cuenta q SOLO sirve para mantener los datos durante la MISMA SESION (mejor en procesos batch, OJO con apps web q usarn datasource donde la sesión web NO ES IGUAL a la sesión de BD). Consumen PGA.
    * Function RESULT CACHE.
      * No se cachean tipos complejos como registros (record) con CLOBS, colecciones, etc.
      * Solo se cachean los parámetros de entrada y el valor de salida
      * Si usar INVOKER RIGHTS necesitas v12.1 para q use result cache
      * OJO si usas VPD (virtual private database) y/o contextos (SYS_CONTEXT)
      * Sobre todo cachear tablas cuyos datos no cambian durante una sesión
    * BULK COLLECT. Se usa en SELECTs para cargar todas las filas en una colección, de un golpe.
      * Ejemplos sencillos <code sql>
                    SELECT * BULK COLLECT INTO collection(s) FROM table;
                    FETCH cur BULK COLLECT INTO collection(s);
                    EXECUTE IMMEDIATE query BULK COLLECT INTO collection(s); </code>
      * No sirve NO_DATA_FOUND, hay q controlar cuando se vacía la colección.
      * Se puede usar LIMIT para limitar el nº de filas a procesar <code sql> 
                OPEN emps_in_dept_cur; 
                LOOP
                  FETCH emps_in_dept_cur
                    BULK COLLECT INTO emps LIMIT 1000;
                  EXIT WHEN emps.COUNT = 0;
                  process_emps (emps);
                END LOOP;</code>
      * NO USES Bulk Collect si tu bucle funciona bien, o si NO contiene sentencias DMLs.
    * FORALL. Se usa en UPDATE/INSERT/DELETE, para actualizar todas las filas de un golpe, desde una colección.
      * Ejemplo <code sql>
               FORALL indx IN low_value .. high_value
                  UPDATE employee
                     SET salary = newsal_in
                   WHERE employee_id = list_of_emps (indx);</code>
      * SQL%ROWCOUNT cuento el total de filas modificadas por todo el FORALL
      * SQL%BULK_ROWCOUNT cuenta el nº de filas modificadas en cada ciclo del bucle FORALL
    * Si tienes bucles q contienen sentencias DML, cambia el proceso por otro en 3 fases:
      * Primero, usa BULK COLLECT para cargar los datos en una colección.
      * Segundo, haz los cambios en la colección.
      * Tercero, usa FORALL para aplicar los cambios en la BD desde la colección.
    * Mejoras en 12c de WITH https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1

:!: Este contenido es una copia de https://wiki.um.es/wikis/programador/doku.php?id=plsql:optimizacion


 --- //[[juanlu@um.es|JUAN LUIS SERRADILLA AMARILLA]] 15/09/2022//