10 truques para trabalhar com Oracle

Existem várias práticas Oracle no Sberbank que podem ser úteis. Acho que alguns deles são familiares para você, mas não usamos apenas ferramentas ETL para carregar, mas também procedimentos armazenados Oracle. O Oracle PL / SQL implementa os algoritmos mais complexos para carregar dados em armazenamentos, onde você precisa “sentir cada byte”.

• Registro de compilação automática
• O que fazer se você quiser fazer uma visão com parâmetros
• Usando estatísticas dinâmicas em consultas
• Como salvar o plano de consulta ao inserir dados via link de banco de dados
• Executar procedimentos em sessões paralelas
• Puxando as sobras
• Combinando várias histórias em uma
• Normalizador
• Renderizando em formato SVG
• Aplicativo Oracle Metadata Search

Registro de compilação automática

Em alguns bancos de dados Oracle, o Sberbank tem um gatilho de compilação que lembra quem, quando e o que mudou no código dos objetos do servidor. Assim, o autor das alterações pode ser estabelecido a partir da tabela de registro de compilação. Um sistema de controle de versão também é implementado automaticamente. Em qualquer caso, se o programador esqueceu de enviar as alterações ao Git, então esse mecanismo fará uma proteção. Vamos descrever um exemplo de implementação de tal sistema de registro de compilação automática. Uma das versões simplificadas do gatilho de compilação que grava no log na forma da tabela ddl_changes_log se parece com isto:

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;

Nesse gatilho, o nome e o novo conteúdo do objeto compilado são obtidos, complementados com o conteúdo anterior do dicionário de dados e gravados no log de alterações.

O que fazer se você quiser fazer uma visão com parâmetros

Muitas vezes, esse desejo pode ser visitado por um desenvolvedor Oracle. Por que é possível fazer um procedimento ou função com parâmetros, mas não existem visualizações com parâmetros de entrada que possam ser usados ​​em cálculos? A Oracle tem algo para substituir essa falta, em nossa opinião, conceito.

Vejamos um exemplo. Deixe haver uma tabela com vendas por divisão para cada dia.

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

Esta consulta compara as vendas por departamento em dois dias. Nesse caso, 30/04/2020 e 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;

Aqui está uma visão que eu gostaria de escrever para resumir tal pedido. Eu gostaria de passar datas como parâmetros. No entanto, a sintaxe não permite isso.

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;

Essa solução alternativa é sugerida. Vamos criar um tipo para a linha a partir desta visualização.

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

E vamos criar um tipo para uma tabela a partir dessas strings.

create type t_division_sales_report_table as table of t_division_sales_report;

Em vez de uma visualização, vamos escrever uma função em pipeline com parâmetros de entrada de data.

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;

Você pode se referir a ele assim:

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

Essa solicitação nos dará o mesmo resultado que a solicitação no início desta postagem com datas explicitamente substituídas.

Funções pipelined também podem ser úteis quando você precisa passar um parâmetro dentro de uma solicitação complexa.

Por exemplo, considere uma exibição complexa na qual o campo1, pelo qual você deseja filtrar os dados, está oculto em algum lugar profundo na exibição.

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

E uma consulta de uma visão com um valor fixo de field1 pode ter um plano de execução ruim.

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

Essa. em vez de primeiro filtrar deep_table pela condição field1 = 'myvalue', a consulta pode primeiro juntar todas as tabelas, processando uma quantidade desnecessariamente grande de dados e, em seguida, filtrar o resultado pela condição field1 = 'myvalue'. Essa complexidade pode ser evitada criando uma função com um parâmetro atribuído a field1 em vez de uma visualização em pipeline.

Usando estatísticas dinâmicas em consultas

Acontece que a mesma consulta no banco de dados Oracle processa cada vez uma quantidade diferente de dados nas tabelas e subconsultas nela utilizadas. Como você faz com que o otimizador descubra qual forma de unir tabelas desta vez e quais índices usar a cada vez? Considere, por exemplo, uma consulta que conecta uma parte dos saldos de contas que foram alterados desde o último carregamento ao diretório de contas. A parte dos saldos das contas alteradas varia muito de download para download, chegando a centenas de linhas, às vezes milhões de linhas. Dependendo do tamanho desta parcela, é necessário combinar os saldos alterados com as contas pelo método / * + use_nl * / ou pelo método / * + use_hash * /. É inconveniente coletar novamente as estatísticas todas as vezes, especialmente se o número de linhas mudar de carregamento para carregamento não na tabela associada, mas na subconsulta associada.A dica / * + dynamic_sampling () * / pode ajudar aqui. Vamos mostrar como isso afeta, usando um exemplo de solicitação. Deixe a tabela change_balances conter as mudanças nos saldos e contas - o diretório de contas. Unimos essas tabelas pelos campos account_id disponíveis em cada uma das tabelas. No início do experimento, escreveremos mais linhas nessas tabelas e não alteraremos seu conteúdo.

Primeiro, vamos pegar 10% das mudanças nos resíduos na tabela change_balances e ver o que o plano estará usando 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.

Portanto, vemos que é proposto percorrer as tabelas change_balances e accounts usando uma varredura completa e juntá-las usando um hash join.

Agora vamos reduzir drasticamente a amostra de change_balances. Vamos pegar 0,1% das alterações residuais e ver o que o plano estará usando 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.

Desta vez, a tabela de contas é anexada à tabela change_balances com loops aninhados e um índice é usado para ler linhas de contas.

Se a dica dynamic_sampling for removida, no segundo caso o plano permanecerá o mesmo que no primeiro caso, e isso não é o ideal.

Detalhes sobre a dica dynamic_sampling e valores possíveis para seu argumento numérico podem ser encontrados na documentação.

Como salvar o plano de consulta ao inserir dados via link de banco de dados

Resolvemos esse problema. O servidor de origem de dados possui tabelas que precisam ser unidas e carregadas no data warehouse. Suponha que uma exibição seja gravada no servidor de origem, que contém toda a lógica de transformação ETL necessária. A visão é escrita de forma otimizada, contém dicas para o otimizador que sugerem como unir tabelas e quais índices usar. No lado do servidor do data warehouse, você precisa fazer uma coisa simples - inserir os dados da visualização na tabela de destino. E aqui podem surgir dificuldades. Se você inserir na tabela de destino com um comando como

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

, então, toda a lógica do plano de consulta contido na visão da qual lemos os dados por meio do link do banco de dados pode ser ignorada. Todas as dicas embutidas nesta visualização podem ser ignoradas.

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 salvar o plano de consulta na visualização, você pode usar a inserção de dados na tabela de destino a partir do 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 do cursor

select field1, field2 from vw_for_dwh_table@xe_link;

ao invés de inserir

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

salvará o plano da solicitação, colocado na exibição no servidor de origem.

Executar procedimentos em sessões paralelas

Freqüentemente, a tarefa é iniciar vários cálculos paralelos de algum procedimento pai e, depois de esperar que cada um deles seja concluído, continuar executando o procedimento pai. Isso pode ser útil na computação paralela se os recursos do servidor permitirem. Existem diversas formas de fazer isto.

Vamos descrever uma implementação muito simples de tal mecanismo. Os procedimentos paralelos serão executados em trabalhos paralelos “únicos”, enquanto o procedimento pai aguardará em um loop pela conclusão de todos esses trabalhos.

Vamos criar tabelas com metadados para este mecanismo. Para começar, vamos fazer uma tabela com grupos de procedimentos de execução 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 seguir, criaremos uma tabela com scripts que serão executados paralelamente em grupos. O preenchimento desta tabela pode ser estático ou criado dinamicamente:

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.          ';

E faremos uma tabela de log, onde coletaremos um log de qual procedimento foi lançado em qual job:

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;

Agora vamos fornecer o código do procedimento para iniciar fluxos 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;

Vamos verificar como funciona o procedimento run_in_parallel. Vamos criar um procedimento de teste que chamaremos em sessões paralelas.

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

Preencha o nome do grupo e a tabela com scripts que serão executados em 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');

Vamos começar um grupo de procedimentos paralelos.

begin
  run_in_parallel(1);
end;

Quando terminar, vamos ver o log.

select * from PARALLEL_PROC_LOG;

Vemos que o tempo de execução das instâncias do procedimento de teste atende às expectativas.

Puxando as sobras

Vamos descrever uma variante da solução de um problema bancário bastante típico de “puxar o equilíbrio”. Digamos que haja uma tabela de fatos de variações nos saldos das contas. É necessário indicar o saldo da conta corrente para cada dia do calendário (o último do dia). Essas informações são freqüentemente necessárias em data warehouses. Se em algum dia não houver movimentos na contagem, será necessário repetir o último restante conhecido. Se a quantidade de dados e o poder de computação do servidor permitir, você pode resolver esse problema usando uma consulta SQL, sem precisar recorrer ao PL / SQL. A função last_value (* ignorar nulos) sobre (partição por * ordem por *) nos ajudará com isso, o que estenderá o último resto conhecido para datas subsequentes nas quais não houve alterações.

Vamos criar uma tabela e preenchê-la com dados de teste.

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);

A consulta abaixo resolve nosso problema. A subconsulta 'cld' contém um calendário de datas, na subconsulta 'ab' agrupamos os saldos de cada dia, na subconsulta 'a' lembramos a lista de todas as contas e a data de início do histórico para cada conta, na subconsulta 'pré' para cada conta compomos um calendário de dias a partir do início dela histórias. A solicitação final adiciona os últimos saldos de cada dia ao calendário de dias ativos de cada conta e os estende aos dias em que não houve alterações.

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;

O resultado da consulta é o esperado.

Combinando várias histórias em uma

Ao carregar dados em armazenamentos, o problema geralmente é resolvido quando você precisa construir um único histórico para uma entidade, tendo um histórico separado dos atributos dessa entidade que vêm de várias fontes. Suponha que haja alguma entidade com uma chave primária primary_key_id, sobre a qual é conhecido o histórico (start_dt - end_dt) de seus três atributos diferentes, localizados em três tabelas 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);

O objetivo é carregar um único histórico de alterações de três atributos em uma tabela.

Abaixo está uma consulta que resolve esse problema. Ele primeiro forma uma tabela diagonal q1 com dados de diferentes fontes para diferentes atributos (atributos que estão ausentes na fonte são preenchidos com nulos). Em seguida, usando a função last_value (* ignorar nulos), a tabela diagonal é reduzida em um único histórico e os últimos valores de atributos conhecidos são estendidos para as datas em que não houve alterações para eles:

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;

O resultado é assim:

Normalizador

Às vezes, surge o problema de normalizar dados que vieram no formato de um campo delimitado. Por exemplo, na forma de uma tabela 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 os dados colando os campos vinculados a vírgulas como várias linhas:

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;

O resultado é assim:

Renderizando em formato SVG

Freqüentemente, há um desejo de visualizar de alguma forma os indicadores numéricos armazenados no banco de dados. Por exemplo, construa gráficos, histogramas, tabelas. Ferramentas especializadas como Oracle BI podem ajudar. Mas as licenças para essas ferramentas podem custar dinheiro, e configurá-las pode levar mais tempo do que escrever uma consulta SQL "no joelho" para a Oracle, que retornará a imagem finalizada. Vamos demonstrar com um exemplo como desenhar rapidamente uma imagem no formato SVG usando uma consulta.

Suponha que temos uma tabela com dados

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 é a data de relevância,

val é um indicador numérico, cuja dinâmica visualizamos ao longo do tempo,

raio é outro indicador numérico que desenharemos na forma de um círculo com esse raio.

Vamos dizer algumas palavras sobre o formato SVG. É um formato gráfico vetorial que pode ser visualizado em navegadores modernos e convertido para outros formatos gráficos. Nele, entre outras coisas, você pode desenhar linhas, círculos e escrever 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>

Abaixo está uma consulta SQL ao Oracle que traça um gráfico a partir dos dados desta tabela. Aqui, a subconsulta const contém várias configurações constantes - tamanho da imagem, número de rótulos nos eixos do gráfico, cores da linha e do círculo, tamanhos de fonte, etc. Na subconsulta gd1, convertemos os dados da tabela graph_data para as coordenadas xey na figura. A subconsulta gd2 lembra os pontos anteriores no tempo, a partir dos quais as linhas devem ser traçadas para novos pontos. O bloco 'cabeçalho' é o cabeçalho da imagem com um fundo branco. O bloco de 'linhas verticais' desenha linhas verticais. As 'datas sob as linhas verticais' bloqueiam as datas no eixo x. O bloco de 'linhas horizontais' desenha linhas horizontais. O bloco 'valores próximos às linhas horizontais' rotula os valores no eixo y. O bloco 'círculos' desenha círculos do raio especificado na tabela graph_data.O bloco 'dados do gráfico' constrói um gráfico da dinâmica do indicador val da tabela graph_data das linhas. O bloco 'rodapé' adiciona uma tag 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;

O resultado da consulta pode ser salvo em um arquivo com a extensão * .svg e visualizado em um navegador. Se desejar, você pode usar qualquer um dos utilitários para convertê-lo em outros formatos gráficos, colocá-lo nas páginas da web de seu aplicativo, etc.

O resultado é a seguinte imagem:

Aplicativo Oracle Metadata Search

Imagine tentar encontrar algo no código-fonte do Oracle olhando as informações em vários servidores ao mesmo tempo. Trata-se de pesquisar objetos de dicionário de dados Oracle. O local de trabalho para a pesquisa é a interface da web, onde o usuário-programador insere a string de pesquisa e seleciona as caixas de seleção nos servidores Oracle para realizar essa pesquisa.

O mecanismo de busca na web é capaz de pesquisar uma linha em objetos do servidor Oracle simultaneamente em diversos bancos de dados do banco. Por exemplo, você pode pesquisar:

• 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.

Com base nos resultados da pesquisa, o usuário recebe informações sobre qual servidor no código de quais funções, procedimentos, pacotes, gatilhos, visualizações, etc. encontrou os resultados necessários.

Vamos descrever como esse mecanismo de pesquisa é implementado.



O lado do cliente não é complicado. A interface da web recebe a string de pesquisa inserida pelo usuário, a lista de servidores a serem pesquisados ​​e o login do usuário. A página da web os passa para um procedimento armazenado Oracle no servidor manipulador. O histórico de solicitações ao mecanismo de pesquisa, ou seja, quem executou a solicitação é registrado apenas no caso.



Depois de receber uma consulta de pesquisa, o backend no servidor de pesquisa Oracle executa vários procedimentos em trabalhos paralelos que examinam as seguintes visualizações de dicionário de dados nos links de banco de dados nos servidores Oracle selecionados em busca da string desejada: dba_col_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_tabsba_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_tabsba_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_tabsba_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_tabsba_comments_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_tabsba_comments, dbaobs_scheduler , dba_views. Cada um dos procedimentos, se for encontrado algo, grava o encontrado na tabela de resultados da pesquisa (com o ID da consulta de pesquisa correspondente).



Quando todos os procedimentos de pesquisa terminam, a parte do cliente fornece ao usuário tudo o que está escrito na tabela de resultados da pesquisa com o ID da consulta de pesquisa correspondente.

Mas isso não é tudo. Além de pesquisar no dicionário de dados Oracle, a pesquisa no repositório Informatica PowerCenter também foi anexada ao mecanismo descrito. Informatica PowerCenter é uma ferramenta ETL popular usada pelo Sberbank para carregar várias informações em data warehouses. O Informatica PowerCenter tem uma estrutura de repositório aberta e bem documentada. Neste repositório, é possível pesquisar informações da mesma forma que no dicionário de dados Oracle. Quais tabelas e campos são usados ​​no código de download desenvolvido com o Informatica PowerCenter? O que pode ser encontrado em transformações de porta e consultas SQL explícitas? Todas essas informações estão disponíveis nas estruturas do repositório e podem ser encontradas. Para conhecedores do PowerCenter, escreverei que nosso mecanismo de pesquisa examina os seguintes locais de repositório em busca de mapeamentos, sessões ou fluxos de trabalho,contendo a string de pesquisa em algum lugar: substituição de sql, atributos de mapplet, portas, definições de origem em mapeamentos, definições de origem, definições de destino em mapeamentos, target_definitions, mappings, mapplets, workflows, worklets, sessões, comandos, portas de expressão, instâncias de sessão, campos de definição de origem, campos de definição de destino, tarefas de e-mail.

: , SberProfi DWH/BigData.



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