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;


RUN_ID GROUP_ID PROC_SCRIPT JOB_ID HEURE DE DÉBUT HEURE DE FIN
1 1 commencer Ă  dormir (5); fin; 1 11/09/2020 15:00:51 11/09/2020 15:00:56
1 1 commencer Ă  dormir (10); fin; 2 11/09/2020 15:00:51 11/09/2020 15:01:01


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.

DT IDENTIFIANT DE COMPTE BALANCE_AMT TURNOVER_AMT
01/01/2020 1 23 23
02.01.2020 1 23 0
03/01/2020 1 23 0
04/01/2020 1 23 0
01/05/2020 1 44 21
06.01.2020 1 44 0
07.01.2020 1 44 0
01/08/2020 1 44 0
09/01/2020 1 44 0
10.01.2020 1 44 0
01/05/2020 2 77 77
06.01.2020 2 77 0
07.01.2020 2 72 -cinq
01/08/2020 2 72 0
09/01/2020 2 72 0
10.01.2020 2 72 0


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:

PRIMARY_KEY_ID START_DT END_DT ATTRIBUT1 ATTRIBUT2 ATTRIBUT3
1 01/01/2014 31/12/2014 7 NUL NUL
1 01.01.2015 31.12.2015 8 4 NUL
1 01/01/2016 31/12/2016 neuf cinq Dix
1 01.01.2017 31.12.2017 neuf 6 20
1 01.01.2018 31.12.9999 neuf 6 trente
2 01/01/2014 31/12/2014 17 NUL NUL
2 01.01.2015 31.12.2015 dix-huit Quatorze NUL
2 01/01/2016 31/12/2016 19 quinze 110
2 01.01.2017 31.12.2017 19 seize 120
2 01.01.2018 31.12.9999 19 seize 130


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:

ID VAL COLUMN_VALUE
1 aaa 1
1 cccc 2
1 bb 3
2 jjj 1
3 fffff 1
3 e 2


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 .



All Articles