10 astuces pour travailler avec Oracle

Il existe plusieurs pratiques Oracle chez Sberbank que vous pourriez trouver utiles. Je pense que certaines d'entre elles vous sont familières, mais nous utilisons non seulement des outils ETL pour charger, mais aussi des procédures stockées Oracle. Oracle PL / SQL implémente les algorithmes les plus complexes pour charger des données dans des stockages, où vous devez «sentir chaque octet».

• Journalisation de la compilation automatique
• Que faire si vous souhaitez créer une vue avec des paramètres
• Utilisation de statistiques dynamiques dans les requêtes
• Comment enregistrer le plan de requête lors de l'insertion de données via un lien de base de données
• Exécution de procédures dans des sessions parallèles
• Traverser les restes
• Combiner plusieurs histoires en une seule
• Normaliseur
• Rendu au format SVG
• Application de recherche de métadonnées Oracle

Journalisation de la compilation automatique

Sur certaines bases de données Oracle, Sberbank dispose d'un déclencheur de compilation qui mémorise qui, quand et ce qui a changé dans le code des objets serveur. Ainsi, l'auteur des modifications peut être établi à partir de la table du journal de compilation. Un système de contrôle de version est également implémenté automatiquement. Dans tous les cas, si le programmeur a oublié de soumettre les modifications à Git, alors ce mécanisme se couvrira. Décrivons un exemple d'implémentation d'un tel système de journalisation de compilation automatique. Une des versions simplifiées du déclencheur de compilation qui écrit dans le journal sous la forme de la table ddl_changes_log ressemble à ceci:

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;

Dans ce déclencheur, le nom et le nouveau contenu de l'objet compilé sont obtenus, complétés par le contenu précédent du dictionnaire de données et écrits dans le journal des modifications.

Que faire si vous souhaitez créer une vue avec des paramètres

Un tel désir peut souvent être visité par un développeur chez Oracle. Pourquoi est-il possible de créer une procédure ou une fonction avec des paramètres, mais aucune vue avec des paramètres d'entrée ne peut être utilisée dans les calculs? Oracle a quelque chose pour remplacer ce concept manquant, à notre avis.

Regardons un exemple. Qu'il y ait un tableau avec les ventes par division pour chaque jour.

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

Cette requête compare les ventes par département sur deux jours. Dans ce cas, 30/04/2020 et 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;

Voici un avis que j'aimerais écrire pour résumer une telle demande. Je voudrais passer des dates comme paramètres. Cependant, la syntaxe ne le permet pas.

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;

Une telle solution de contournement est suggérée. Créons un type pour la ligne à partir de cette vue.

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

Et créez un type pour une table à partir de ces chaînes.

create type t_division_sales_report_table as table of t_division_sales_report;

Au lieu d'une vue, écrivons une fonction en pipeline avec des paramètres d'entrée de date.

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;

Vous pouvez vous y référer comme ceci:

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

Cette demande nous donnera le même résultat que la demande au début de ce post avec des dates explicitement substituées.

Les fonctions pipelinées peuvent également être utiles lorsque vous devez passer un paramètre dans une requête complexe.

Par exemple, considérons une vue complexe dans laquelle le champ1, par lequel vous souhaitez filtrer les données, est caché quelque part au fond de la vue.

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

Et une requête d'une vue avec une valeur fixe de champ1 peut avoir un mauvais plan d'exécution.

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

Ceux. au lieu de filtrer d'abord deep_table par la condition field1 = 'myvalue', la requête peut d'abord joindre toutes les tables, traiter une quantité inutilement grande de données, puis filtrer le résultat par la condition field1 = 'myvalue'. Cette complexité peut être évitée en créant une fonction avec un paramètre affecté à field1 au lieu d'une vue en pipeline.

Utilisation de statistiques dynamiques dans les requêtes

Il arrive que la même requête dans la base de données Oracle traite à chaque fois une quantité différente de données dans les tables et sous-requêtes qui y sont utilisées. Comment faire en sorte que l'optimiseur détermine de quelle manière joindre des tables cette fois-ci et quels index utiliser à chaque fois? Prenons, par exemple, une requête qui connecte une partie des soldes de compte qui ont changé depuis le dernier téléchargement dans le répertoire des comptes. La part des soldes de compte modifiés varie considérablement d'un téléchargement à l'autre, s'élevant à des centaines de lignes, parfois des millions de lignes. En fonction de la taille de cette partie, il est nécessaire de combiner les soldes modifiés avec des comptes soit par la méthode / * + use_nl * /, soit par la méthode / * + use_hash * /. Il n'est pas pratique de collecter à nouveau les statistiques à chaque fois, en particulier si le nombre de lignes change de chargement en chargement non pas dans la table jointe, mais dans la sous-requête jointe.L'indice / * + dynamic_sampling () * / peut venir à la rescousse ici. Voyons comment cela affecte, en utilisant un exemple de demande. Laissez la table change_balances contenir les modifications des soldes et des comptes - le répertoire des comptes. Nous joignons ces tables par les champs account_id disponibles dans chacune des tables. Au début de l'expérience, nous écrirons plus de lignes dans ces tableaux et ne changerons pas leur contenu.

Tout d'abord, prenez 10% des changements dans les résidus dans la table change_balances et voyez ce que le plan utilisera 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.

Ainsi, nous voyons qu'il est proposé de parcourir les tables change_balances et accounts en utilisant un scan complet et de les joindre en utilisant une jointure par hachage.

Maintenant, réduisons considérablement l'échantillon de change_balances. Prenons 0,1% des changements résiduels et voyons ce que le plan utilisera 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.

Cette fois, la table accounts est associée à la table change_balances avec des boucles imbriquées et un index est utilisé pour lire les lignes des comptes.

Si l'indication dynamic_sampling est supprimée, dans le second cas, le plan restera le même que dans le premier cas, et ce n'est pas optimal.

Des détails sur l'indication dynamic_sampling et les valeurs possibles de son argument numérique se trouvent dans la documentation.

Comment enregistrer le plan de requête lors de l'insertion de données via un lien de base de données

Nous résolvons ce problème. Le serveur de source de données contient des tables qui doivent être jointes et chargées dans l'entrepôt de données. Supposons qu'une vue soit écrite sur le serveur source, qui contient toute la logique de transformation ETL nécessaire. La vue est écrite de manière optimale, elle contient des astuces pour l'optimiseur qui suggèrent comment joindre des tables et quels index utiliser. Du côté serveur de l'entrepôt de données, vous devez faire une chose simple: insérer les données de la vue dans la table cible. Et ici, des difficultés peuvent survenir. Si vous insérez dans la table cible avec une commande comme

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

, alors toute la logique du plan de requête contenue dans la vue à partir de laquelle nous lisons des données via le lien de base de données peut être ignorée. Tous les conseils intégrés dans cette vue peuvent être ignorés.

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.

Pour enregistrer le plan de requête dans la vue, vous pouvez utiliser l'insertion de données dans la table cible à partir du curseur:

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;

Requête depuis le curseur

select field1, field2 from vw_for_dwh_table@xe_link;

par opposition à insérer

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

enregistre le plan de la requête, posé dans la vue sur le serveur source.

Exécution de procédures dans des sessions parallèles

Souvent, la tâche consiste à démarrer plusieurs calculs parallèles à partir d'une procédure parente et, après avoir attendu la fin de chacun d'eux, à poursuivre l'exécution de la procédure parente. Cela peut être utile dans le calcul parallèle si les ressources du serveur le permettent. Il existe de nombreuses façons de procéder.

Décrivons une implémentation très simple d'un tel mécanisme. Les procédures parallèles seront exécutées dans des travaux «ponctuels» parallèles, tandis que la procédure parente attendra en boucle l'achèvement de tous ces travaux.

Créons des tables avec des métadonnées pour ce mécanisme. Pour commencer, créons un tableau avec des groupes de procédures parallèles:

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

Ensuite, nous allons créer une table avec des scripts qui seront exécutés en parallèle en groupes. Le remplissage de ce tableau peut être statique ou créé dynamiquement:

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

Et nous allons créer une table de journal, où nous collecterons un journal de quelle procédure a été lancée dans quel travail:

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;

Maintenant, donnons le code de la procédure de démarrage de flux parallèles:

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;

Voyons comment fonctionne la procédure run_in_parallel. Créons une procédure de test que nous appellerons dans des sessions parallèles.

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

Renseignez le nom du groupe et la table avec les scripts qui seront exécutés en parallèle.

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

Commençons un groupe de procédures parallèles.

begin
  run_in_parallel(1);
end;

Une fois terminé, voyons le journal.

select * from PARALLEL_PROC_LOG;

On voit que le temps d'exécution des instances de procédure de test répond aux attentes.

Traverser les restes

Décrivons une variante de résolution d'un problème bancaire assez typique de «faire la balance». Disons qu'il existe un tableau des faits des changements dans les soldes des comptes. Il est nécessaire d'indiquer le solde du compte courant pour chaque jour du calendrier (le dernier de la journée). Ces informations sont souvent nécessaires dans les entrepôts de données. Si un jour il n'y a pas eu de mouvements dans le décompte, vous devez répéter le dernier reste connu. Si la quantité de données et la puissance de calcul du serveur le permettent, vous pouvez résoudre ce problème à l'aide d'une requête SQL, sans même recourir à PL / SQL. La fonction last_value (* ignore nulls) over (partition by * order by *) nous aidera dans ce domaine, ce qui étendra le dernier reste connu aux dates suivantes dans lesquelles il n'y a pas eu de changements.

Créons une table et remplissons-la de données de test.

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 requête ci-dessous résout notre problème. La sous-requête 'cld' contient le calendrier des dates, dans la sous-requête 'ab' on regroupe les soldes pour chaque jour, dans la sous-requête 'a' on se souvient de la liste de tous les comptes et de la date de début de l'historique pour chaque compte, dans la sous-requête 'pre' pour chaque compte on compose un calendrier des jours à partir du début histoires. La demande finale ajoute les derniers soldes de chaque jour au calendrier des jours actifs de chaque compte et les étend aux jours où il n'y a pas eu de changement.

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;

Le résultat de la requête est comme prévu.

Combiner plusieurs histoires en une seule

Lors du chargement de données dans des stockages, le problème est souvent résolu lorsque vous devez créer un historique unique pour une entité, en ayant un historique séparé des attributs de cette entité provenant de diverses sources. Supposons qu'il existe une entité avec une clé primaire primary_key_id, dont l'historique (start_dt - end_dt) de ses trois attributs différents est connu, situé dans trois tables différentes.

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

L'objectif est de charger un historique des modifications unique de trois attributs dans une table.

Vous trouverez ci-dessous une requête qui résout ce problème. Il forme d'abord une table diagonale q1 avec des données provenant de différentes sources pour différents attributs (les attributs absents de la source sont remplis avec des valeurs nulles). Ensuite, à l'aide de la fonction last_value (* ignore nulls), la table diagonale est réduite en un seul historique et les dernières valeurs d'attribut connues sont étendues aux dates auxquelles il n'y a pas eu de changements pour elles:

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;

Le résultat est comme ceci:

Normaliseur

Parfois, le problème se pose de normaliser des données qui se présentaient sous le format d'un champ délimité. Par exemple, sous la forme d'un tableau comme celui-ci:

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

Cette requête normalise les données en collant les champs liés par des virgules sur plusieurs lignes:

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;

Le résultat est comme ceci:

Rendu au format SVG

On souhaite souvent visualiser d'une manière ou d'une autre les indicateurs numériques stockés dans la base de données. Par exemple, créez des graphiques, des histogrammes, des graphiques. Des outils spécialisés tels qu'Oracle BI peuvent vous aider. Mais les licences pour ces outils peuvent coûter de l'argent, et leur mise en place peut prendre plus de temps que d'écrire une requête SQL "sur le genou" à Oracle, qui retournera l'image finale. Démontrons avec un exemple comment dessiner rapidement une telle image au format SVG à l'aide d'une requête.

Supposons que nous ayons une table avec des données

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 est la date de pertinence,

val est un indicateur numérique dont nous visualisons la dynamique au fil du temps, le

rayon est un autre indicateur numérique que nous dessinerons sous la forme d'un cercle avec un tel rayon.

Disons quelques mots sur le format SVG. Il s'agit d'un format graphique vectoriel qui peut être visualisé dans les navigateurs modernes et converti en d'autres formats graphiques. Dans celui-ci, entre autres, vous pouvez dessiner des lignes, des cercles et écrire du texte:

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

Vous trouverez ci-dessous une requête SQL adressée à Oracle qui trace un graphique à partir des données de cette table. Ici, la sous-requête const contient divers paramètres constants - taille de l'image, nombre d'étiquettes sur les axes du graphique, couleurs des lignes et des cercles, tailles de police, etc. Dans la sous-requête gd1, nous convertissons les données de la table graph_data en coordonnées x et y de la figure. La sous-requête gd2 se souvient des points précédents dans le temps, à partir desquels les lignes doivent être tracées vers de nouveaux points. Le bloc «en-tête» est l'en-tête de l'image avec un fond blanc. Le bloc «lignes verticales» trace des lignes verticales. Le bloc «dates sous les lignes verticales» étiquette les dates sur l'axe des x. Le bloc «lignes horizontales» trace des lignes horizontales. Le bloc «valeurs près des lignes horizontales» étiquette les valeurs sur l'axe y. Le bloc «cercles» dessine des cercles du rayon spécifié dans la table graph_data.Le bloc 'graph data' construit un graphique de la dynamique de l'indicateur val à partir de la table graph_data à partir des lignes. Le bloc 'footer' ajoute une balise de fin.

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;

Le résultat de la requête peut être enregistré dans un fichier avec l'extension * .svg et affiché dans un navigateur. Si vous le souhaitez, vous pouvez utiliser l'un des utilitaires pour le convertir dans d'autres formats graphiques, le placer sur les pages Web de votre application, etc.

L'image résultante est:

Application de recherche de métadonnées Oracle

Imaginez essayer de trouver quelque chose dans le code source sur Oracle en regardant des informations sur plusieurs serveurs à la fois. Il s'agit de rechercher dans les objets du dictionnaire de données Oracle. Le lieu de travail pour la recherche est l'interface Web, où l'utilisateur-programmeur entre la chaîne de recherche et sélectionne les cases à cocher sur les serveurs Oracle pour effectuer cette recherche.

Le moteur de recherche Web est capable de rechercher une ligne dans des objets serveur Oracle simultanément dans plusieurs bases de données différentes de la banque. Par exemple, vous pouvez rechercher:

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

Sur la base des résultats de la recherche, l'utilisateur reçoit des informations sur quel serveur dans le code de quelles fonctions, procédures, packages, déclencheurs, vues, etc. trouvé les résultats requis.

Décrivons comment un tel moteur de recherche est mis en œuvre.



Le côté client n'est pas compliqué. L'interface Web reçoit la chaîne de recherche saisie par l'utilisateur, la liste des serveurs à rechercher et la connexion de l'utilisateur. La page Web les transmet à une procédure stockée Oracle sur le serveur de gestionnaire. L'historique des requêtes au moteur de recherche, c'est-à-dire qui a exécuté quelle demande est enregistrée au cas où.



Après avoir reçu une demande de recherche, le côté serveur du serveur de recherche Oracle exécute plusieurs procédures dans des travaux parallèles qui analysent les vues de dictionnaire de données suivantes sur les liens de base de données sur les serveurs Oracle sélectionnés à la recherche de la chaîne souhaitée: dba_col_comments, dba_jobs, dba_mviews, dba_objects, dba_scheduler_jobs, dba_tabsource, dba_scheduler_schedbajobs, dba_scheduler_jobs , dba_views. Chacune des procédures, si quelque chose est trouvé, écrit le trouvé dans la table des résultats de la recherche (avec l'ID de requête de recherche correspondant).



Lorsque toutes les procédures de recherche sont terminées, la partie client donne à l'utilisateur tout ce qui est écrit dans la table des résultats de recherche avec l'ID de requête de recherche correspondant.

Mais ce n'est pas tout. Outre la recherche dans le dictionnaire de données Oracle, la recherche dans le référentiel Informatica PowerCenter a également été vissée dans le mécanisme décrit. Informatica PowerCenter est un outil ETL populaire utilisé par Sberbank pour charger diverses informations dans des entrepôts de données. Informatica PowerCenter possède une structure de référentiel ouverte et bien documentée. Sur ce référentiel, il est possible de rechercher des informations de la même manière que dans le dictionnaire de données Oracle. Quelles tables et champs sont utilisés dans le code de téléchargement développé avec Informatica PowerCenter? Que peut-on trouver dans les transformations de port et les requêtes SQL explicites? Toutes ces informations sont disponibles dans les structures du référentiel et peuvent être trouvées. Pour les connaisseurs de PowerCenter, j'écrirai que notre moteur de recherche scanne les emplacements de référentiel suivants à la recherche de mappages, de sessions ou de flux de travail,contenant la chaîne de recherche quelque part: remplacement sql, attributs de mapplet, ports, définitions de source dans les mappages, définitions de source, définitions de cible dans les mappages, target_definitions, mappings, mapplets, workflows, worklets, sessions, commandes, ports d'expression, instances de session, champs de définition source, champs de définition cible, tâches de courrier électronique.

: , SberProfi DWH/BigData.



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