10 trucos para trabajar con Oracle

Hay varias prácticas de Oracle en Sberbank que pueden resultarle útiles. Creo que algo de esto le resulta familiar, pero no solo usamos herramientas ETL para cargar, sino también procedimientos almacenados de Oracle. Oracle PL / SQL implementa los algoritmos más complejos para cargar datos en almacenamientos, donde necesita "sentir cada byte".

• Registro de compilación automático
• Qué hacer si quieres hacer una vista con parámetros
• Usar estadísticas dinámicas en consultas
• Cómo guardar el plan de consulta al insertar datos a través del enlace de la base de datos
• Ejecución de procedimientos en sesiones paralelas
• Tirando de las sobras
• Combinando varias historias en una
• Normalizador
• Representación en formato SVG
• Aplicación de búsqueda de metadatos de Oracle

Registro de compilación automático

En algunas bases de datos de Oracle, Sberbank tiene un disparador de compilación que recuerda quién, cuándo y qué cambió en el código de los objetos del servidor. Por lo tanto, el autor de los cambios se puede establecer a partir de la tabla de registro de compilación. También se implementa automáticamente un sistema de control de versiones. En cualquier caso, si el programador olvidó enviar los cambios a Git, este mecanismo se cubrirá. Describamos un ejemplo de implementación de dicho sistema de registro automático de compilación. Una de las versiones simplificadas del disparador de compilación que escribe en el registro en forma de tabla ddl_changes_log tiene este aspecto:

create table DDL_CHANGES_LOG
(
  id               INTEGER,
  change_date      DATE,
  sid              VARCHAR2(100),
  schemaname       VARCHAR2(30),
  machine          VARCHAR2(100),
  program          VARCHAR2(100),
  osuser           VARCHAR2(100),
  obj_owner        VARCHAR2(30),
  obj_type         VARCHAR2(30),
  obj_name         VARCHAR2(30),
  previous_version CLOB,
  changes_script   CLOB
);

create or replace trigger trig_audit_ddl_trg
  before ddl on database
declare
  v_sysdate              date;
  v_valid                number;
  v_previous_obj_owner   varchar2(30) := '';
  v_previous_obj_type    varchar2(30) := '';
  v_previous_obj_name    varchar2(30) := '';
  v_previous_change_date date;
  v_lob_loc_old          clob := '';
  v_lob_loc_new          clob := '';
  v_n                    number;
  v_sql_text             ora_name_list_t;
  v_sid                  varchar2(100) := '';
  v_schemaname           varchar2(30) := '';
  v_machine              varchar2(100) := '';
  v_program              varchar2(100) := '';
  v_osuser               varchar2(100) := '';
begin
  v_sysdate := sysdate;
  -- find whether compiled object already presents and is valid
  select count(*)
    into v_valid
    from sys.dba_objects
   where owner = ora_dict_obj_owner
     and object_type = ora_dict_obj_type
     and object_name = ora_dict_obj_name
     and status = 'VALID'
     and owner not in ('SYS', 'SPOT', 'WMSYS', 'XDB', 'SYSTEM')
     and object_type in ('TRIGGER', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'VIEW');
  -- find information about previous compiled object
  select max(obj_owner) keep(dense_rank last order by id),
         max(obj_type) keep(dense_rank last order by id),
         max(obj_name) keep(dense_rank last order by id),
         max(change_date) keep(dense_rank last order by id)
    into v_previous_obj_owner, v_previous_obj_type, v_previous_obj_name, v_previous_change_date
    from ddl_changes_log;
  -- if compile valid object or compile invalid package body broken by previous compilation of package then log it
  if (v_valid = 1 or v_previous_obj_owner = ora_dict_obj_owner and
     (v_previous_obj_type = 'PACKAGE' and ora_dict_obj_type = 'PACKAGE BODY' or
     v_previous_obj_type = 'PACKAGE BODY' and ora_dict_obj_type = 'PACKAGE') and
     v_previous_obj_name = ora_dict_obj_name and
     v_sysdate - v_previous_change_date <= 1 / 24 / 60 / 2) and
     ora_sysevent in ('CREATE', 'ALTER') then
    -- store previous version of object (before compilation) from dba_source or dba_views in v_lob_loc_old
    if ora_dict_obj_type <> 'VIEW' then
      for z in (select substr(text, 1, length(text) - 1) || chr(13) || chr(10) as text
                  from sys.dba_source
                 where owner = ora_dict_obj_owner
                   and type = ora_dict_obj_type
                   and name = ora_dict_obj_name
                 order by line) loop
        v_lob_loc_old := v_lob_loc_old || z.text;
      end loop;
    else
      select sys.dbms_metadata_util.long2clob(v.textlength, 'SYS.VIEW$', 'TEXT', v.rowid) into v_lob_loc_old
        from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u
       where o.obj# = v.obj#
         and o.owner# = u.user#
         and u.name = ora_dict_obj_owner
         and o.name = ora_dict_obj_name;
    end if;
    -- store new version of object (after compilation) from v_sql_text in v_lob_loc_new
    v_n := ora_sql_txt(v_sql_text);
    for i in 1 .. v_n loop
      v_lob_loc_new := v_lob_loc_new || replace(v_sql_text(i), chr(10), chr(13) || chr(10));
    end loop;
    -- find information about session that changed this object
    select max(to_char(sid)), max(schemaname), max(machine), max(program), max(osuser)
      into v_sid, v_schemaname, v_machine, v_program, v_osuser
      from v$session
     where audsid = userenv('sessionid');
    -- store changes in ddl_changes_log
    insert into ddl_changes_log
      (id, change_date, sid, schemaname, machine, program, osuser,
       obj_owner, obj_type, obj_name, previous_version, changes_script)
    values
      (seq_ddl_changes_log.nextval, v_sysdate, v_sid, v_schemaname, v_machine, v_program, v_osuser,
       ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, v_lob_loc_old, v_lob_loc_new);
  end if;
exception
  when others then
    null;
end;

En este disparador, se obtienen el nombre y el nuevo contenido del objeto compilado, complementado con el contenido anterior del diccionario de datos y escrito en el registro de cambios.

Qué hacer si quieres hacer una vista con parámetros

Tal deseo a menudo puede ser visitado por un desarrollador de Oracle. ¿Por qué es posible crear un procedimiento o función con parámetros, pero no hay vistas con parámetros de entrada que se puedan utilizar en los cálculos? Oracle tiene algo para reemplazar este concepto faltante, en nuestra opinión.

Veamos un ejemplo. Sea una tabla con las ventas por división para cada día.

create table DIVISION_SALES
(
  division_id INTEGER,
  dt          DATE,
  sales_amt   NUMBER
);

Esta consulta compara las ventas por departamento durante dos días. En este caso, 30/04/2020 y 11/09/2020.

select t1.division_id,
       t1.dt          dt1,
       t2.dt          dt2,
       t1.sales_amt   sales_amt1,
       t2.sales_amt   sales_amt2
  from (select dt, division_id, sales_amt
          from division_sales
         where dt = to_date('30.04.2020', 'dd.mm.yyyy')) t1,
       (select dt, division_id, sales_amt
          from division_sales
         where dt = to_date('11.09.2020', 'dd.mm.yyyy')) t2
 where t1.division_id = t2.division_id;

Aquí hay una vista que me gustaría escribir para resumir tal solicitud. Me gustaría pasar fechas como parámetros. Sin embargo, la sintaxis no lo permite.

create or replace view vw_division_sales_report(in_dt1 date, in_dt2 date) as
select t1.division_id,
       t1.dt          dt1,
       t2.dt          dt2,
       t1.sales_amt   sales_amt1,
       t2.sales_amt   sales_amt2
  from (select dt, division_id, sales_amt
          from division_sales
         where dt = in_dt1) t1,
       (select dt, division_id, sales_amt
          from division_sales
         where dt = in_dt2) t2
 where t1.division_id = t2.division_id;

Se sugiere una solución de este tipo. Creemos un tipo para la línea desde esta vista.

create type t_division_sales_report as object
(
  division_id INTEGER,
  dt1         DATE,
  dt2         DATE,
  sales_amt1  NUMBER,
  sales_amt2  NUMBER
);

Y crearemos un tipo para una tabla a partir de tales cadenas.

create type t_division_sales_report_table as table of t_division_sales_report;

En lugar de una vista, escribamos una función canalizada con parámetros de entrada de fecha.

create or replace function func_division_sales(in_dt1 date, in_dt2 date)
  return t_division_sales_report_table
  pipelined as
begin
  for z in (select t1.division_id,
                   t1.dt          dt1,
                   t2.dt          dt2,
                   t1.sales_amt   sales_amt1,
                   t2.sales_amt   sales_amt2
              from (select dt, division_id, sales_amt
                      from division_sales
                     where dt = in_dt1) t1,
                   (select dt, division_id, sales_amt
                      from division_sales
                     where dt = in_dt2) t2
             where t1.division_id = t2.division_id) loop
    pipe row(t_division_sales_report(z.division_id,
                                     z.dt1,
                                     z.dt2,
                                     z.sales_amt1,
                                     z.sales_amt2));
  end loop;
end;

Puede referirse a él de esta manera:

select *
  from table(func_division_sales(to_date('30.04.2020', 'dd.mm.yyyy'),
                                 to_date('11.09.2020', 'dd.mm.yyyy')));

Esta solicitud nos dará el mismo resultado que la solicitud al comienzo de esta publicación con fechas explícitamente sustituidas.

Las funciones canalizadas también pueden ser útiles cuando necesita pasar un parámetro dentro de una solicitud compleja.

Por ejemplo, considere una vista compleja en la que el campo1, mediante el cual desea filtrar datos, está oculto en algún lugar profundo de la vista.

create or replace view complex_view as
 select field1, ...
   from (select field1, ...
           from (select field1, ... from deep_table), table1
          where ...),
        table2
  where ...;

Y una consulta de una vista con un valor fijo de field1 puede tener un plan de ejecución incorrecto.

select field1, ... from complex_view
 where field1 = 'myvalue';

Aquellos. en lugar de filtrar primero deep_table por la condición field1 = 'myvalue', la consulta puede unir primero todas las tablas, habiendo procesado una cantidad innecesariamente grande de datos, y luego filtrar el resultado por la condición field1 = 'myvalue'. Esta complejidad se puede evitar creando una función con un parámetro asignado a field1 en lugar de una vista canalizada.

Usar estadísticas dinámicas en consultas

Sucede que la misma consulta en la base de datos de Oracle procesa cada vez una cantidad diferente de datos en las tablas y subconsultas que se utilizan en ella. ¿Cómo logras que el optimizador averigüe de qué manera unir tablas esta vez y qué índices usar cada vez? Considere, por ejemplo, una consulta que conecta una parte de los saldos de la cuenta que han cambiado desde la última carga al directorio de la cuenta. La parte de los saldos de cuenta modificados varía mucho de una descarga a otra, y asciende a cientos de líneas, a veces millones de líneas. Dependiendo del tamaño de esta porción, es necesario combinar los saldos modificados con las cuentas, ya sea por el método / * + use_nl * / o por el método / * + use_hash * /. Es inconveniente volver a recopilar estadísticas cada vez, especialmente si el número de filas cambia de una carga a otra, no en la tabla unida, sino en la subconsulta unida.La sugerencia / * + dynamic_sampling () * / puede venir al rescate aquí. Demostremos cómo afecta, usando una solicitud de ejemplo. Deje que la tabla change_balances contenga los cambios en los saldos y cuentas: el directorio de cuentas. Unimos estas tablas por los campos account_id disponibles en cada una de las tablas. Al comienzo del experimento, escribiremos más filas en estas tablas y no cambiaremos su contenido.

Primero, tomemos el 10% de los cambios en los residuos en la tabla change_balances y veamos qué utilizará el plan con dynamic_sampling:

SQL> EXPLAIN PLAN
  2   SET statement_id = 'test1'
  3   INTO plan_table
  4  FOR  with c as
  5   (select /*+ dynamic_sampling(change_balances 2)*/
  6     account_id, balance_amount
  7      from change_balances
  8     where mod(account_id, 10) = 0)
  9  select a.account_id, a.account_number, c.balance_amount
 10    from c, accounts a
 11   where c.account_id = a.account_id;

Explained.

SQL>
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
Plan hash value: 874320301

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |  9951K|   493M|       |   140K  (1)| 00:28:10 |
|*  1 |  HASH JOIN         |                 |  9951K|   493M|  3240K|   140K  (1)| 00:28:10 |
|*  2 |   TABLE ACCESS FULL| CHANGE_BALANCES |   100K|  2057K|       |  7172   (1)| 00:01:27 |
|   3 |   TABLE ACCESS FULL| ACCOUNTS        |    10M|   295M|       |   113K  (1)| 00:22:37 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")
   2 - filter(MOD("ACCOUNT_ID",10)=0)

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.

Entonces, vemos que se propone pasar por las tablas change_balances y accounts usando un escaneo completo y unirlas usando una combinación hash.

Ahora reduzcamos drásticamente la muestra de change_balances. Tomemos el 0.1% de los cambios residuales y veamos qué usará el plan con dynamic_sampling:

SQL> EXPLAIN PLAN
  2   SET statement_id = 'test2'
  3   INTO plan_table
  4  FOR  with c as
  5   (select /*+ dynamic_sampling(change_balances 2)*/
  6     account_id, balance_amount
  7      from change_balances
  8     where mod(account_id, 1000) = 0)
  9  select a.account_id, a.account_number, c.balance_amount
 10    from c, accounts a
 11   where c.account_id = a.account_id;

Explained.

SQL>
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
Plan hash value: 2360715730

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        | 73714 |  3743K| 16452   (1)| 00:03:18 |
|   1 |  NESTED LOOPS                |                        |       |       |            |          |
|   2 |   NESTED LOOPS               |                        | 73714 |  3743K| 16452   (1)| 00:03:18 |
|*  3 |    TABLE ACCESS FULL         | CHANGE_BALANCES        |   743 | 15603 |  7172   (1)| 00:01:27 |
|*  4 |    INDEX RANGE SCAN          | IX_ACCOUNTS_ACCOUNT_ID |   104 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| ACCOUNTS               |    99 |  3069 |   106   (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(MOD("ACCOUNT_ID",1000)=0)
   4 - access("ACCOUNT_ID"="A"."ACCOUNT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

22 rows selected.

Esta vez, la tabla de cuentas se adjunta a la tabla change_balances con bucles anidados y se usa un índice para leer las filas de las cuentas.

Si se elimina la sugerencia de muestreo dinámico, en el segundo caso el plan seguirá siendo el mismo que en el primer caso, y esto no es óptimo.

Los detalles sobre la sugerencia de muestreo dinámico y los posibles valores para su argumento numérico se pueden encontrar en la documentación.

Cómo guardar el plan de consulta al insertar datos a través del enlace de la base de datos

Estamos resolviendo este problema. El servidor de origen de datos tiene tablas que deben unirse y cargarse en el almacén de datos. Suponga que se escribe una vista en el servidor de origen, que contiene toda la lógica de transformación ETL necesaria. La vista está escrita de manera óptima, contiene sugerencias para el optimizador que sugieren cómo unir tablas y qué índices usar. En el lado del servidor del almacén de datos, debe hacer algo simple: insertar los datos de la vista en la tabla de destino. Y aquí pueden surgir dificultades. Si inserta en la tabla de destino con un comando como

insert into dwh_table
  (field1, field2)
  select field1, field2 from vw_for_dwh_table@xe_link;

, entonces se puede ignorar toda la lógica del plan de consulta contenida en la vista desde la cual leemos los datos a través del enlace de la base de datos. Todas las sugerencias incrustadas en esta vista se pueden ignorar.

SQL> EXPLAIN PLAN
  2   SET statement_id = 'test'
  3   INTO plan_table
  4  FOR  insert into dwh_table
  5    (field1, field2)
  6    select field1, field2 from vw_for_dwh_table@xe_link;

Explained.

SQL>
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY);
Plan hash value: 1788691278

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                  |     1 |  2015 |     2   (0)| 00:00:01 |        |      |
|   1 |  LOAD TABLE CONVENTIONAL | DWH_TABLE        |       |       |            |          |        |      |
|   2 |   REMOTE                 | VW_FOR_DWH_TABLE |     1 |  2015 |     2   (0)| 00:00:01 | XE_LI~ | R->S |
-------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT /*+ OPAQUE_TRANSFORM */ "FIELD1","FIELD2" FROM "VW_FOR_DWH_TABLE" "VW_FOR_DWH_TABLE"
       (accessing 'XE_LINK' )


16 rows selected.

Para guardar el plan de consulta en la vista, puede usar la inserción de datos en la tabla de destino desde el cursor:

declare
  cursor cr is
    select field1, field2 from vw_for_dwh_table@xe_link;
  cr_row cr%rowtype;
begin
  open cr;
  loop
    fetch cr
      into cr_row;
    insert into dwh_table
      (field1, field2)
    values
      (cr_row.field1, cr_row.field2);
    exit when cr%notfound;
  end loop;
  close cr;
end;

Consulta desde el cursor

select field1, field2 from vw_for_dwh_table@xe_link;

en lugar de insertar

insert into dwh_table
  (field1, field2)
  select field1, field2 from vw_for_dwh_table@xe_link;

guardará el plan de la solicitud, colocado en la vista en el servidor de origen.

Ejecución de procedimientos en sesiones paralelas

A menudo, la tarea consiste en iniciar varios cálculos paralelos desde algún procedimiento principal y, después de esperar a que se complete cada uno de ellos, continuar con la ejecución del procedimiento principal. Esto puede ser útil en computación paralela si los recursos del servidor lo permiten. Hay muchas maneras de hacer esto.

Describamos una implementación muy simple de tal mecanismo. Los procedimientos paralelos se realizarán en trabajos "únicos" en paralelo, mientras que el procedimiento principal esperará en un bucle hasta que se completen todos estos trabajos.

Creemos tablas con metadatos para este mecanismo. Para empezar, hagamos una tabla con grupos de procedimientos de ejecución paralela:

create table PARALLEL_PROC_GROUP_LIST
(
  group_id   INTEGER,
  group_name VARCHAR2(4000)
);
comment on column PARALLEL_PROC_GROUP_LIST.group_id
  is '    ';
comment on column PARALLEL_PROC_GROUP_LIST.group_name
  is '    ';

A continuación, crearemos una tabla con scripts que se ejecutarán en paralelo en grupos. El llenado de esta tabla puede ser estático o creado dinámicamente:

create table PARALLEL_PROC_LIST
(
  group_id    INTEGER,
  proc_script VARCHAR2(4000),
  is_active   CHAR(1) default 'Y'
);
comment on column PARALLEL_PROC_LIST.group_id
  is '    ';
comment on column PARALLEL_PROC_LIST.proc_script
  is 'Pl/sql    ';
comment on column PARALLEL_PROC_LIST.is_active
  is 'Y - active, N - inactive.          ';

Y haremos una tabla de registro, donde recopilaremos un registro de qué procedimiento se inició en qué trabajo:

create table PARALLEL_PROC_LOG
(
  run_id      INTEGER,
  group_id    INTEGER,
  proc_script VARCHAR2(4000),
  job_id      INTEGER,
  start_time  DATE,
  end_time    DATE
);
comment on column PARALLEL_PROC_LOG.run_id
  is '   run_in_parallel';
comment on column PARALLEL_PROC_LOG.group_id
  is '    ';
comment on column PARALLEL_PROC_LOG.proc_script
  is 'Pl/sql    ';
comment on column PARALLEL_PROC_LOG.job_id
  is 'Job_id ,      ';
comment on column PARALLEL_PROC_LOG.start_time
  is '  ';
comment on column PARALLEL_PROC_LOG.end_time
  is '  ';

create sequence Seq_Parallel_Proc_Log;

Ahora démosle el código del procedimiento para iniciar flujos paralelos:

create or replace procedure run_in_parallel(in_group_id integer) as
  --        parallel_proc_list.
  --  -    parallel_proc_list
  v_run_id             integer;
  v_job_id             integer;
  v_job_id_list        varchar2(32767);
  v_job_id_list_ext    varchar2(32767);
  v_running_jobs_count integer;
begin
  select seq_parallel_proc_log.nextval into v_run_id from dual;
  -- submit jobs with the same parallel_proc_list.in_group_id
  -- store seperated with ',' JOB_IDs in v_job_id_list
  v_job_id_list     := null;
  v_job_id_list_ext := null;
  for z in (select pt.proc_script
              from parallel_proc_list pt
             where pt.group_id = in_group_id
               and pt.is_active = 'Y') loop
    dbms_job.submit(v_job_id, z.proc_script);
    insert into parallel_proc_log
      (run_id, group_id, proc_script, job_id, start_time, end_time)
    values
      (v_run_id, in_group_id, z.proc_script, v_job_id, sysdate, null);
    v_job_id_list     := v_job_id_list || ',' || to_char(v_job_id);
    v_job_id_list_ext := v_job_id_list_ext || ' union all select ' ||
                         to_char(v_job_id) || ' job_id from dual';
  end loop;
  commit;
  v_job_id_list     := substr(v_job_id_list, 2);
  v_job_id_list_ext := substr(v_job_id_list_ext, 12);
  -- loop while not all jobs finished
  loop
    -- set parallel_proc_log.end_time for finished jobs
    execute immediate 'update parallel_proc_log set end_time = sysdate where job_id in (' ||
                      v_job_id_list_ext ||
                      ' minus select job from user_jobs where job in (' ||
                      v_job_id_list ||
                      ') minus select job_id from parallel_proc_log where job_id in (' ||
                      v_job_id_list || ') and end_time is not null)';
    commit;
    -- check whether all jobs finished
    execute immediate 'select count(1) from user_jobs where job in (' ||
                      v_job_id_list || ')'
      into v_running_jobs_count;
    -- if all jobs finished then exit
    exit when v_running_jobs_count = 0;
    -- sleep a little
    sys.dbms_lock.sleep(0.1);
  end loop;
end;

Veamos cómo funciona el procedimiento run_in_parallel. Creemos un procedimiento de prueba que llamaremos en sesiones paralelas.

create or replace procedure sleep(in_seconds integer) as
begin
  sys.Dbms_Lock.Sleep(in_seconds);
end;

Complete el nombre del grupo y la tabla con scripts que se ejecutarán en paralelo.

insert into PARALLEL_PROC_GROUP_LIST(group_id, group_name) values(1, ' ');

insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(5); end;', 'Y');
insert into PARALLEL_PROC_LIST(group_id, proc_script, is_active) values(1, 'begin sleep(10); end;', 'Y');

Comencemos un grupo de procedimientos paralelos.

begin
  run_in_parallel(1);
end;

Cuando termine, veamos el registro.

select * from PARALLEL_PROC_LOG;

Vemos que el tiempo de ejecución de las instancias del procedimiento de prueba cumple con las expectativas.

Tirando de las sobras

Describamos una variante de la solución de un problema bancario bastante típico de "tirar del equilibrio". Digamos que hay una tabla de hechos de cambios en los saldos de las cuentas. Se requiere indicar el saldo de la cuenta corriente para cada día del calendario (el último del día). A menudo, esta información se necesita en los almacenes de datos. Si algún día no hubo movimientos en el conteo, entonces debe repetir el último resto conocido. Si la cantidad de datos y la potencia de cálculo del servidor lo permiten, entonces puede resolver este problema utilizando una consulta SQL, sin siquiera recurrir a PL / SQL. La función last_value (* ignorar nulos) sobre (partición por * ordenar por *) nos ayudará con esto, lo que extenderá el último resto conocido a fechas posteriores en las que no hubo cambios.

Creemos una tabla y la llenemos con datos de prueba.

create table ACCOUNT_BALANCE
(
  dt           DATE,
  account_id   INTEGER,
  balance_amt  NUMBER,
  turnover_amt NUMBER
);
comment on column ACCOUNT_BALANCE.dt
  is '     ';
comment on column ACCOUNT_BALANCE.account_id
  is ' ';
comment on column ACCOUNT_BALANCE.balance_amt
  is '  ';
comment on column ACCOUNT_BALANCE.turnover_amt
  is '  ';

insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('01.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 23, 23);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 01:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 45, 22);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 1, 44, -1);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 67, 67);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('05.01.2020 20:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 77, 10);
insert into account_balance(dt, account_id, balance_amt, turnover_amt) values(to_date('07.01.2020 00:00:00','dd.mm.yyyy hh24.mi.ss'), 2, 72, -5);

La consulta a continuación resuelve nuestro problema. La subconsulta 'cld' contiene el calendario de fechas, en la subconsulta 'ab' agrupamos los saldos para cada día, en la subconsulta 'a' recordamos el listado de todas las cuentas y la fecha de inicio del historial para cada cuenta, en la subconsulta 'pre' para cada cuenta componimos un calendario de días desde el inicio de la misma cuentos. La solicitud final suma los últimos saldos de cada día al calendario de días activos de cada cuenta y los extiende a los días en los que no hubo cambios.

with cld as
 (select /*+ materialize*/
   to_date('01.01.2020', 'dd.mm.yyyy') + level - 1 dt
    from dual
  connect by level <= 10),
ab as
 (select trunc(dt) dt,
         account_id,
         max(balance_amt) keep(dense_rank last order by dt) balance_amt,
         sum(turnover_amt) turnover_amt
    from account_balance
   group by trunc(dt), account_id),
a as
 (select min(dt) min_dt, account_id from ab group by account_id),
pre as
 (select cld.dt, a.account_id from cld left join a on cld.dt >= a.min_dt)
select pre.dt,
       pre.account_id,
       last_value(ab.balance_amt ignore nulls) over(partition by pre.account_id order by pre.dt) balance_amt,
       nvl(ab.turnover_amt, 0) turnover_amt
  from pre
  left join ab
    on pre.dt = ab.dt
   and pre.account_id = ab.account_id
 order by 2, 1;

El resultado de la consulta es el esperado.

Combinando varias historias en una

Cuando se cargan datos en almacenes, el problema a menudo se resuelve cuando se necesita crear un historial único para una entidad, con un historial separado de los atributos de esta entidad que provienen de varias fuentes. Supongamos que hay alguna entidad con una clave primaria primary_key_id, sobre la cual se conoce el historial (start_dt - end_dt) de sus tres atributos diferentes, ubicados en tres tablas diferentes.

create table HIST1
(
  primary_key_id INTEGER,
  start_dt       DATE,
  attribute1     NUMBER
);

insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2014-01-01','yyyy-mm-dd'), 7);
insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2015-01-01','yyyy-mm-dd'), 8);
insert into HIST1(primary_key_id, start_dt, attribute1) values(1, to_date('2016-01-01','yyyy-mm-dd'), 9);
insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2014-01-01','yyyy-mm-dd'), 17);
insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2015-01-01','yyyy-mm-dd'), 18);
insert into HIST1(primary_key_id, start_dt, attribute1) values(2, to_date('2016-01-01','yyyy-mm-dd'), 19);

create table HIST2
(
  primary_key_id INTEGER,
  start_dt       DATE,
  attribute2     NUMBER
);
 
insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2015-01-01','yyyy-mm-dd'), 4);
insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2016-01-01','yyyy-mm-dd'), 5);
insert into HIST2(primary_key_id, start_dt, attribute2) values(1, to_date('2017-01-01','yyyy-mm-dd'), 6);
insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2015-01-01','yyyy-mm-dd'), 14);
insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2016-01-01','yyyy-mm-dd'), 15);
insert into HIST2(primary_key_id, start_dt, attribute2) values(2, to_date('2017-01-01','yyyy-mm-dd'), 16);

create table HIST3
(
  primary_key_id INTEGER,
  start_dt       DATE,
  attribute3     NUMBER
);
 
insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2016-01-01','yyyy-mm-dd'), 10);
insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2017-01-01','yyyy-mm-dd'), 20);
insert into HIST3(primary_key_id, start_dt, attribute3) values(1, to_date('2018-01-01','yyyy-mm-dd'), 30);
insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2016-01-01','yyyy-mm-dd'), 110);
insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2017-01-01','yyyy-mm-dd'), 120);
insert into HIST3(primary_key_id, start_dt, attribute3) values(2, to_date('2018-01-01','yyyy-mm-dd'), 130);

El objetivo es cargar un historial de cambios único de tres atributos en una tabla.

A continuación se muestra una consulta que resuelve este problema. Primero forma una tabla diagonal q1 con datos de diferentes fuentes para diferentes atributos (los atributos que están ausentes en la fuente se rellenan con nulos). Luego, usando la función last_value (* ignorar nulos), la tabla diagonal se contrae en un solo historial, y los últimos valores de atributos conocidos se extienden hacia las fechas en las que no hubo cambios para ellos:

select primary_key_id,
       start_dt,
       nvl(lead(start_dt - 1)
           over(partition by primary_key_id order by start_dt),
           to_date('9999-12-31', 'yyyy-mm-dd')) as end_dt,
       last_value(attribute1 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute1,
       last_value(attribute2 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute2,
       last_value(attribute3 ignore nulls) over(partition by primary_key_id order by start_dt) as attribute3
  from (select primary_key_id,
               start_dt,
               max(attribute1) as attribute1,
               max(attribute2) as attribute2,
               max(attribute3) as attribute3
          from (select primary_key_id,
                       start_dt,
                       attribute1,
                       cast(null as number) attribute2,
                       cast(null as number) attribute3
                  from hist1
                union all
                select primary_key_id,
                       start_dt,
                       cast(null as number) attribute1,
                       attribute2,
                       cast(null as number) attribute3
                  from hist2
                union all
                select primary_key_id,
                       start_dt,
                       cast(null as number) attribute1,
                       cast(null as number) attribute2,
                       attribute3
                  from hist3) q1
         group by primary_key_id, start_dt) q2
 order by primary_key_id, start_dt;

El resultado es así:

Normalizador

A veces surge el problema de normalizar los datos que venían en el formato de un campo delimitado. Por ejemplo, en forma de tabla como esta:

create table DENORMALIZED_TABLE
(
  id  INTEGER,
  val VARCHAR2(4000)
);

insert into DENORMALIZED_TABLE(id, val) values(1, 'aaa,cccc,bb');
insert into DENORMALIZED_TABLE(id, val) values(2, 'ddd');
insert into DENORMALIZED_TABLE(id, val) values(3, 'fffff,e');

Esta consulta normaliza los datos al pegar los campos enlazados por comas como varias líneas:

select id, regexp_substr(val, '[^,]+', 1, column_value) val, column_value
  from denormalized_table,
       table(cast(multiset
                  (select level
                     from dual
                   connect by regexp_instr(val, '[^,]+', 1, level) > 0) as
                  sys.odcinumberlist))
 order by id, column_value;

El resultado es así:

Representación en formato SVG

A menudo existe el deseo de visualizar de alguna manera los indicadores numéricos almacenados en la base de datos. Por ejemplo, cree gráficos, histogramas, cuadros. Las herramientas especializadas como Oracle BI pueden ayudar. Pero las licencias para estas herramientas pueden costar dinero, y configurarlas puede llevar más tiempo que escribir una consulta SQL "sobre la rodilla" en Oracle, que devolverá la imagen completa. Demostremos con un ejemplo cómo dibujar rápidamente una imagen de este tipo en formato SVG mediante una consulta.

Supongamos que tenemos una tabla con datos

create table graph_data(dt date, val number, radius number);

insert into graph_data(dt, val, radius) values (to_date('01.01.2020','dd.mm.yyyy'), 12, 3);
insert into graph_data(dt, val, radius) values (to_date('02.01.2020','dd.mm.yyyy'), 15, 4);
insert into graph_data(dt, val, radius) values (to_date('05.01.2020','dd.mm.yyyy'), 17, 5);
insert into graph_data(dt, val, radius) values (to_date('06.01.2020','dd.mm.yyyy'), 13, 6);
insert into graph_data(dt, val, radius) values (to_date('08.01.2020','dd.mm.yyyy'),  3, 7);
insert into graph_data(dt, val, radius) values (to_date('10.01.2020','dd.mm.yyyy'), 20, 8);
insert into graph_data(dt, val, radius) values (to_date('11.01.2020','dd.mm.yyyy'), 18, 9);

dt es la fecha de relevancia,

val es un indicador numérico, cuya dinámica visualizamos en el tiempo,

radio es otro indicador numérico que dibujaremos en forma de círculo con dicho radio.

Digamos algunas palabras sobre el formato SVG. Es un formato de gráficos vectoriales que se puede ver en los navegadores modernos y convertir a otros formatos gráficos. En él, entre otras cosas, puedes dibujar líneas, círculos y escribir texto:

<line x1="94" x2="94" y1="15" y2="675" style="stroke:rgb(150,255,255); stroke-width:1px"/>
<circle cx="30" cy="279" r="3" style="fill:rgb(255,0,0)"/>
<text x="7" y="688" font-size="10" fill="rgb(0,150,255)">2020-01-01</text>

A continuación se muestra una consulta SQL a Oracle que traza un gráfico a partir de los datos de esta tabla. Aquí, la subconsulta const contiene varias configuraciones constantes: tamaño de imagen, número de etiquetas en los ejes del gráfico, colores de líneas y círculos, tamaños de fuente, etc. En la subconsulta gd1, convertimos los datos de la tabla graph_data a las coordenadas xey en la figura. La subconsulta gd2 recuerda los puntos anteriores en el tiempo, desde los cuales se deben trazar las líneas hacia nuevos puntos. El bloque 'encabezado' es el encabezado de la imagen con un fondo blanco. El bloque 'líneas verticales' dibuja líneas verticales. Las etiquetas de bloque 'fechas bajo líneas verticales' fechas en el eje x. El bloque 'líneas horizontales' dibuja líneas horizontales. El bloque 'valores cerca de líneas horizontales' etiqueta los valores en el eje y. El bloque 'círculos' dibuja círculos del radio especificado en la tabla graph_data.El bloque 'gráfico de datos' crea un gráfico de la dinámica del indicador val a partir de la tabla graph_data de las líneas. El bloque 'pie de página' agrega una etiqueta final.

with const as
 (select 700 viewbox_width,
         700 viewbox_height,
         30 left_margin,
         30 right_margin,
         15 top_margin,
         25 bottom_margin,
         max(dt) - min(dt) + 1 num_vertical_lines,
         11 num_horizontal_lines,
         'rgb(150,255,255)' stroke_vertical_lines,
         '1px' stroke_width_vertical_lines,
         10 font_size_dates,
         'rgb(0,150,255)' fill_dates,
         23 x_dates_pad,
         13 y_dates_pad,
         'rgb(150,255,255)' stroke_horizontal_lines,
         '1px' stroke_width_horizontal_lines,
         10 font_size_values,
         'rgb(0,150,255)' fill_values,
         4 x_values_pad,
         2 y_values_pad,
         'rgb(255,0,0)' fill_circles,
         'rgb(51,102,0)' stroke_graph,
         '1px' stroke_width_graph,
         min(dt) min_dt,
         max(dt) max_dt,
         max(val) max_val
    from graph_data),
gd1 as
 (select graph_data.dt,
         const.left_margin +
         (const.viewbox_width - const.left_margin - const.right_margin) *
         (graph_data.dt - const.min_dt) / (const.max_dt - const.min_dt) x,
         const.viewbox_height - const.bottom_margin -
         (const.viewbox_height - const.top_margin - const.bottom_margin) *
         graph_data.val / const.max_val y,
         graph_data.radius
    from graph_data, const),
gd2 as
 (select dt,
         round(nvl(lag(x) over(order by dt), x)) prev_x,
         round(x) x,
         round(nvl(lag(y) over(order by dt), y)) prev_y,
         round(y) y,
         radius
    from gd1)
/* header */
select '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' txt
  from dual
union all
select '<svg version="1.1" width="' || viewbox_width || '" height="' ||
       viewbox_height || '" viewBox="0 0 ' || viewbox_width || ' ' ||
       viewbox_height ||
       '" style="background:yellow" baseProfile="full" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ev="http://www.w3.org/2001/xml-events">'
  from const
union all
select '<title>Test graph</title>'
  from dual
union all
select '<desc>Test graph</desc>'
  from dual
union all
select '<rect width="' || viewbox_width || '" height="' || viewbox_height ||
       '" style="fill:white" />'
  from const
union all
/* vertical lines */
select '<line x1="' ||
       to_char(round(left_margin +
                     (viewbox_width - left_margin - right_margin) *
                     (level - 1) / (num_vertical_lines - 1))) || '" x2="' ||
       to_char(round(left_margin +
                     (viewbox_width - left_margin - right_margin) *
                     (level - 1) / (num_vertical_lines - 1))) || '" y1="' ||
       to_char(round(top_margin)) || '" y2="' ||
       to_char(round(viewbox_height - bottom_margin)) || '" style="stroke:' ||
       const.stroke_vertical_lines || '; stroke-width:' ||
       const.stroke_width_vertical_lines || '"/>'
  from const
connect by level <= num_vertical_lines
union all
/* dates under vertical lines */
select '<text x="' ||
       to_char(round(left_margin +
                     (viewbox_width - left_margin - right_margin) *
                     (level - 1) / (num_vertical_lines - 1) - x_dates_pad)) ||
       '" y="' ||
       to_char(round(viewbox_height - bottom_margin + y_dates_pad)) ||
       '" font-size="' || font_size_dates || '" fill="' || fill_dates || '">' ||
       to_char(min_dt + level - 1, 'yyyy-mm-dd') || '</text>'
  from const
connect by level <= num_vertical_lines
union all
/* horizontal lines */
select '<line x1="' || to_char(round(left_margin)) || '" x2="' ||
       to_char(round(viewbox_width - right_margin)) || '" y1="' ||
       to_char(round(top_margin +
                     (viewbox_height - top_margin - bottom_margin) *
                     (level - 1) / (num_horizontal_lines - 1))) || '" y2="' ||
       to_char(round(top_margin +
                     (viewbox_height - top_margin - bottom_margin) *
                     (level - 1) / (num_horizontal_lines - 1))) ||
       '" style="stroke:' || const.stroke_horizontal_lines ||
       '; stroke-width:' || const.stroke_width_horizontal_lines || '"/>'
  from const
connect by level <= num_horizontal_lines
union all
/* values near horizontal lines */
select '<text text-anchor="end" x="' ||
       to_char(round(left_margin - x_values_pad)) || '" y="' ||
       to_char(round(viewbox_height - bottom_margin -
                     (viewbox_height - top_margin - bottom_margin) *
                     (level - 1) / (num_horizontal_lines - 1) +
                     y_values_pad)) || '" font-size="' || font_size_values ||
       '" fill="' || fill_values || '">' ||
       to_char(round(max_val / (num_horizontal_lines - 1) * (level - 1), 2)) ||
       '</text>'
  from const
connect by level <= num_horizontal_lines
union all
/* circles */
select '<circle cx="' || to_char(gd2.x) || '" cy="' || to_char(gd2.y) ||
       '" r="' || gd2.radius || '" style="fill:' || const.fill_circles ||
       '"/>'
  from gd2, const
union all
/* graph data */
select '<line x1="' || to_char(gd2.prev_x) || '" x2="' || to_char(gd2.x) ||
       '" y1="' || to_char(gd2.prev_y) || '" y2="' || to_char(gd2.y) ||
       '" style="stroke:' || const.stroke_graph || '; stroke-width:' ||
       const.stroke_width_graph || '"/>'
  from gd2, const
union all
/* footer */
select '</svg>' from dual;

El resultado de la consulta puede guardarse en un archivo con la extensión * .svg y visualizarse en un navegador. Si lo deseas, puedes utilizar cualquiera de las utilidades para convertirlo a otros formatos gráficos, colocarlo en las páginas web de tu aplicación, etc.

El resultado es la siguiente imagen:

Aplicación de búsqueda de metadatos de Oracle

Imagínese tratando de encontrar algo en el código fuente en Oracle mirando información en varios servidores a la vez. Se trata de buscar en objetos de diccionario de datos de Oracle. El lugar de trabajo para la búsqueda es la interfaz web, donde el usuario-programador ingresa la cadena de búsqueda y selecciona las casillas de verificación en las que los servidores Oracle realizarán esta búsqueda.

El motor de búsqueda web puede buscar una fila en los objetos del servidor Oracle simultáneamente en varias bases de datos diferentes del banco. Por ejemplo, puede buscar:

• Oracle 61209, ?
• accounts ( .. database link)?
• , , ORA-20001 “ ”?
• IX_CLIENTID - SQL-?
• - ( .. database link) , , , ..?
• - - ? .
• Oracle ? , wm_concat Oracle. .
• - , , ? , Oracle sys_connect_by_path, regexp_instr push_subq.

Según los resultados de la búsqueda, el usuario recibe información sobre qué servidor en el código de qué funciones, procedimientos, paquetes, desencadenadores, vistas, etc. encontró los resultados requeridos.

Describamos cómo se implementa dicho motor de búsqueda.



El lado del cliente no es complicado. La interfaz web recibe la cadena de búsqueda ingresada por el usuario, la lista de servidores para buscar y el inicio de sesión del usuario. La página web los pasa a un procedimiento almacenado de Oracle en el servidor del controlador. El historial de solicitudes al motor de búsqueda, es decir quién ejecutó qué solicitud se registra por si acaso.



Después de recibir una consulta de búsqueda, el lado del servidor en el servidor de búsqueda de Oracle ejecuta varios procedimientos en trabajos paralelos que escanean las siguientes vistas de diccionario de datos en los enlaces de la base de datos en los servidores de Oracle seleccionados en busca de la cadena deseada: dba_col_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_ba_source, dbajo_scheduler, , dba_views. Cada uno de los procedimientos, si encuentra algo, escribe lo encontrado en la tabla de resultados de búsqueda (con el ID de consulta de búsqueda correspondiente).



Cuando todos los procedimientos de búsqueda han finalizado, la parte del cliente le da al usuario todo lo que está escrito en la tabla de resultados de búsqueda con el ID de consulta de búsqueda correspondiente.

Pero eso no es todo. Además de buscar en el diccionario de datos de Oracle, la búsqueda en el repositorio de Informatica PowerCenter también se atornilló al mecanismo descrito. Informatica PowerCenter es una popular herramienta ETL utilizada por Sberbank para cargar información diversa en almacenes de datos. Informatica PowerCenter tiene una estructura de repositorio abierta y bien documentada. En este repositorio, es posible buscar información de la misma forma que en el diccionario de datos de Oracle. ¿Qué tablas y campos se utilizan en el código de descarga desarrollado con Informatica PowerCenter? ¿Qué se puede encontrar en transformaciones de puertos y consultas SQL explícitas? Toda esta información está disponible en las estructuras del repositorio y se puede encontrar. Para los conocedores de PowerCenter, escribiré que nuestro motor de búsqueda escanea las siguientes ubicaciones de repositorio en busca de asignaciones, sesiones o flujos de trabajo,que contiene la cadena de búsqueda en alguna parte: anulación de sql, atributos de mapplet, puertos, definiciones de origen en mapeos, definiciones de origen, definiciones de destino en mapeos, target_definitions, mapeos, mapplets, flujos de trabajo, worklets, sesiones, comandos, puertos de expresión, instancias de sesión, campos de definición de origen, campos de definición de destino, tareas de correo electrónico.

: , SberProfi DWH/BigData.



SberProfi DWH/BigData , Hadoop, Teradata, Oracle DB, GreenPlum, BI Qlik, SAP BO, Tableau .