Oracleを操作するための10の秘訣

Sberbankには、役立つと思われるOracleのプラクティスがいくつかあります。なじみのあるものもあると思いますが、ロードにはETLツールだけでなく、Oracleのストアドプロシージャも使用しています。Oracle PL / SQLは、「すべてのバイトを感じる」必要があるストレージにデータをロードするための最も複雑なアルゴリズムを実装しています。

• 自動コンパイルログ
• パラメータを使用してビューを作成する場合の対処方法
• クエリでの動的統計の使用
• データベースリンクを介してデータを挿入するときにクエリプランを保存する方法
• 並列セッションでのプロシージャの実行
• 残り物を引っ張る
• 複数のストーリーを1つに組み合わせる
• ノーマライザー
• SVG形式でのレンダリング
• Oracleメタデータ検索アプリケーション

自動コンパイルログ

一部のOracleデータベースでは、Sberbankには、サーバーオブジェクトのコードで誰が、いつ、何が変更されたかを記憶するコンパイルトリガーがあります。したがって、変更の作成者は、コンパイルログテーブルから確認できます。バージョン制御システムも自動的に実装されます。いずれにせよ、プログラマーが変更をGitに送信するのを忘れた場合、このメカニズムはヘッジします。このような自動コンパイルログシステムの実装例を説明しましょう。ddl_changes_logテーブルの形式でログに書き込むコンパイルトリガーの簡略化されたバージョンの1つは、次のようになります。

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;

このトリガーでは、コンパイルされたオブジェクトの名前と新しいコンテンツが取得され、データディクショナリから以前のコンテンツが補足され、変更ログに書き込まれます。

パラメータを使用してビューを作成する場合の対処方法

このような要望は、Oracleの開発者がよく訪れることができます。パラメータを使用してプロシージャまたは関数を作成できるのに、計算に使用できる入力パラメータを含むビューがないのはなぜですか？オラクルには、私たちの意見では、この欠けている概念を置き換えるものがあります。

例を見てみましょう。毎日の部門別の売上高の表があるとします。

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

このクエリは、2日間の部門別の売上を比較します。この場合、2020年4月30日と2020年9月11日です。

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;

そのような要望をまとめるために書きたいと思います。日付をパラメータとして渡したいのですが。ただし、構文ではこれは許可されていません。

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;

この回避策をお勧めします。このビューから線の型を作成してみましょう。

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

そして、そのような文字列からテーブルの型を作成します。

create type t_division_sales_report_table as table of t_division_sales_report;

ビューの代わりに、日付入力パラメーターを使用してパイプライン関数を作成しましょう。

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;

あなたはそれをこのように参照することができます：

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

このリクエストでは、この投稿の冒頭にあるリクエストと同じ結果が得られますが、日付が明示的に置き換えられています。

パイプライン化された関数は、複雑なリクエスト内でパラメーターを渡す必要がある場合にも役立ちます。

たとえば、データをフィルタリングするfield1がビューの奥のどこかに隠されている複雑なビューについて考えてみます。

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

また、field1の値が固定されているビューからのクエリでは、実行計画が不適切な場合があります。

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

それら。最初にdeep_tableを条件field1 = 'myvalue'でフィルタリングする代わりに、クエリは最初にすべてのテーブルを結合し、不必要に大量のデータを処理してから、条件field1 = 'myvalue'で結果をフィルタリングできます。パイプラインビューの代わりに、値がfield1に割り当てられているパラメーターを使用して関数を作成すると、この複雑さを回避できます。

クエリでの動的統計の使用

Oracleデータベース内の同じクエリが、テーブル内の異なる量のデータとそこで使用されるサブクエリを処理するたびに処理されることがあります。今回はどの方法でテーブルを結合し、どのインデックスを毎回使用するかをオプティマイザにどのように判断させますか？たとえば、最後のロード以降に変更されたアカウント残高の一部をアカウントディレクトリに接続するクエリについて考えてみます。変更されたアカウント残高の部分はダウンロードごとに大きく異なり、数百行、場合によっては数百万行になります。この部分のサイズに応じて、/ * + use_nl * /メソッド、または/ * + use_hash * /メソッドのいずれかによって、変更された残高をアカウントと組み合わせる必要があります。結合されたテーブルではなく、結合されたサブクエリで行数がロードごとに変化する場合は特に、毎回統計を再収集するのは不便です。ヒント/ * + dynamic_sampling（）* /はここで助けになります。リクエストの例を使用して、それがどのように影響するかを示しましょう。テーブルchange_balancesに、残高の変更とアカウント（アカウントのディレクトリ）が含まれているとします。これらのテーブルは、各テーブルで使用可能なaccount_idフィールドによって結合されます。実験の開始時に、これらのテーブルにさらに行を書き込み、その内容を変更しません。

まず、change_balancesテーブルの残差の変更の10％を取得し、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.

したがって、フルスキャンを使用してchange_balancesテーブルとaccountsテーブルを調べ、ハッシュ結合を使用してそれらを結合することが提案されていることがわかります。

それでは、change_balancesからサンプルを大幅に減らしましょう。残りの変更の0.1％を取り、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.

今回は、accountsテーブルがネストされたループでchange_balancesテーブルにアタッチされ、インデックスを使用してアカウントから行が読み取られます。

dynamic_samplingヒントが削除された場合、2番目のケースでは、計画は最初のケースと同じままであり、これは最適ではありません。

dynamic_samplingヒントとその数値引数の可能な値の詳細は、ドキュメントに記載されています。

データベースリンクを介してデータを挿入するときにクエリプランを保存する方法

私たちはこの問題を解決しています。データソースサーバーには、結合してデータウェアハウスにロードする必要のあるテーブルがあります。必要なすべてのETL変換ロジックを含むビューがソースサーバーに書き込まれているとします。ビューは最適に記述されており、テーブルを結合する方法と使用するインデックスを提案するオプティマイザーのヒントが含まれています。データウェアハウスのサーバー側では、簡単なことを行う必要があります。ビューからターゲットテーブルにデータを挿入します。そして、ここで問題が発生する可能性があります。次のようなコマンドでターゲットテーブルに挿入する場合

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

の場合、データベースリンクを介してデータを読み取るビューに含まれるクエリプランのすべてのロジックを無視できます。このビューに組み込まれているすべてのヒントは無視できます。

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.

ビューにクエリプランを保存するには、カーソルからターゲットテーブルへのデータの挿入を使用できます。

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;

カーソルからのクエリ

select field1, field2 from vw_for_dwh_table@xe_link;

挿入するのではなく

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

ソースサーバーのビューに配置されたリクエストのプランを保存します。

並列セッションでのプロシージャの実行

多くの場合、タスクは、いくつかの親プロシージャからいくつかの並列計算を開始し、それぞれが完了するのを待った後、親プロシージャの実行を続行することです。これは、サーバーリソースで許可されている場合、並列コンピューティングで役立ちます。これを行うには多くの方法があります。

このようなメカニズムの非常に単純な実装について説明しましょう。並列プロシージャは並列の「ワンタイム」ジョブで実行され、親プロシージャはこれらすべてのジョブの完了をループで待機します。

このメカニズムのメタデータを使用してテーブルを作成しましょう。まず、並列実行プロシージャのグループを含むテーブルを作成しましょう。

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

次に、グループで並行して実行されるスクリプトを含むテーブルを作成します。このテーブルの入力は、静的または動的に作成できます。

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

そして、ログテーブルを作成します。ここで、どのプロシージャがどのジョブで起動されたかのログを収集します。

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;

次に、並列ストリームを開始するための手順のコードを示します。

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;

run_in_parallelプロシージャがどのように機能するかを確認しましょう。並列セッションで呼び出すテストプロシージャを作成しましょう。

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

グループの名前と、並行して実行されるスクリプトをテーブルに入力します。

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

並列手順のグループを開始しましょう。

begin
  run_in_parallel(1);
end;

終了したら、ログを見てみましょう。

select * from PARALLEL_PROC_LOG;

テストプロシージャインスタンスの実行時間は期待を満たしていることがわかります。

残り物を引っ張る

「バランスをとる」というかなり典型的な銀行の問題を解決する変形について説明しましょう。アカウントの残高の変化の事実の表があるとしましょう。カレンダーの各日（その日の最後の日）の現在のアカウント残高を示す必要があります。このような情報は、データウェアハウスで必要になることがよくあります。ある日、カウントに動きがなかった場合は、最後の既知の残りを繰り返す必要があります。サーバーのデータ量と計算能力が許せば、PL / SQLに頼ることなく、SQLクエリを使用してこの問題を解決できます。 last_value（* nullを無視）over（partition by * order by *）関数は、これに役立ちます。これにより、最後の既知の残りが、変更がなかった後続の日付に拡張されます。

テーブルを作成して、テストデータを入力してみましょう。

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

以下のクエリは問題を解決します。'cld'サブクエリには日付のカレンダーが含まれ、 'ab'サブクエリでは各日の残高をグループ化し、 'a'サブクエリではすべてのアカウントのリストと各アカウントの履歴開始日を記憶し、各アカウントの 'pre'サブクエリでは最初からの日数のカレンダーを作成します物語。最後のリクエストでは、各日の最後の残高が各アカウントのアクティブな日のカレンダーに追加され、変更がなかった日に拡張されます。

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;

クエリの結果は期待どおりです。

複数のストーリーを1つに組み合わせる

データをストレージにロードするときに、さまざまなソースから取得したこのエンティティの属性の個別の履歴を持つ、エンティティの単一の履歴を作成する必要がある場合、問題は解決されることがよくあります。 3つの異なるテーブルに、3つの異なる属性の履歴（start_dt-end_dt）がわかっているプラ​​イマリキーprimary_key_idを持つエンティティがあるとします。

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

目標は、3つの属性の単一の変更履歴を1つのテーブルにロードすることです。

以下は、この問題を解決するクエリです。最初に、さまざまな属性のさまざまなソースからのデータを使用して対角テーブルq1を形成します（ソースに存在しない属性はnullで埋められます）。次に、last_value（* nullを無視）関数を使用して、対角テーブルが単一の履歴に折りたたまれ、最後の既知の属性値が、変更がなかった日付まで拡張されます：

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;

結果は次のようになります。

ノーマライザー

区切りフィールドの形式で送信されたデータを正規化すると、問題が発生することがあります。たとえば、次のようなテーブルの形式で：

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

このクエリは、コンマにリンクされたフィールドを複数の行として貼り付けることにより、データを正規化します。

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;

結果は次のようになります。

SVG形式でのレンダリング

多くの場合、データベースに保存されている数値指標を何らかの形で視覚化したいという要望があります。たとえば、グラフ、ヒストグラム、チャートを作成します。OracleBIなどの専用ツールが役立ちます。ただし、これらのツールのライセンスにはコストがかかる可能性があり、それらのセットアップには、完成した画像を返すOracleへの「オンザニー」SQLクエリの記述よりも時間がかかる可能性があります。クエリを使用して、このような画像をSVG形式ですばやく描画する方法を例を挙げて説明しましょう。

データを含むテーブルがあるとします

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は関連性のある日付、

valは数値インジケーターであり、そのダイナミクスは時間の経過とともに視覚化され

ます。radiusは、そのような半径の円の形で描画するもう1つの数値インジケーターです。

SVGフォーマットについて少し話しましょう。これは、最新のブラウザーで表示したり、他のグラフィック形式に変換したりできるベクトルグラフィック形式です。その中で、とりわけ、あなたは線、円を描き、テキストを書くことができます：

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

以下は、このテーブルのデータからグラフをプロットするOracleへのSQLクエリです。ここで、constサブクエリには、画像サイズ、チャート軸上のラベルの数、線と円の色、フォントサイズなど、さまざまな定数設定が含まれています。 gd1サブクエリでは、graph_dataテーブルのデータを図のx座標とy座標に変換します。 gd2サブクエリは、前の時点を記憶しており、そこから新しいポイントに線を引く必要があります。 'header'ブロックは、背景が白い画像のヘッダーです。 'verticallines'ブロックは垂直線を描画します。 '垂直線の下の日付'ブロックは、x軸の日付にラベルを付けます。 'horizo​​ntallines'ブロックは水平線を描画します。 「水平線の近くの値」ブロックは、y軸上の値にラベルを付けます。 'circles'ブロックは、graph_dataテーブルに指定された半径の円を描画します。'graph data'ブロックは、行のgraph_dataテーブルからvalインジケーターのダイナミクスのグラフを作成します。 'footer'ブロックは、末尾のタグを追加します。

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;

クエリ結果は、拡張子が* .svgのファイルに保存して、ブラウザで表示できます。必要に応じて、任意のユーティリティを使用して、他のグラフィック形式に変換したり、アプリケーションのWebページに配置したりできます。

結果は次の図です。

Oracleメタデータ検索アプリケーション

複数のサーバーの情報を一度に検索して、Oracleのソースコードで何かを見つけようとしていると想像してみてください。これは、Oracleデータディクショナリオブジェクトの検索に関するものです。検索の作業場所は、ユーザープログラマーが検索文字列を入力し、この検索を実行するOracleサーバーのチェックボックスを選択するWebインターフェイスです。

Web検索エンジンは、銀行の複数の異なるデータベースでOracleサーバーオブジェクトの行を同時に検索できます。たとえば、次を検索できます。

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

検索結果に基づいて、ユーザーには、どの関数、プロシージャ、パッケージ、トリガー、ビューなどのコード内のどのサーバーに関する情報が提供されます。必要な結果が見つかりました。

このような検索エンジンがどのように実装されているかを説明しましょう。



クライアント側は複雑ではありません。 Webインターフェイスは、ユーザーが入力した検索文字列、検索するサーバーのリスト、およびユーザーのログインを受け取ります。 Webページは、それらをハンドラーサーバー上のOracleストアドプロシージャに渡します。検索エンジンへのリクエストの履歴、つまり念のため、誰がどのリクエストを実行したかがログに記録されます。



検索クエリを受信した後、Oracle検索サーバーのサーバー側は、選択したOracleサーバー上のデータベースリンク上の次のデータディクショナリビューをスキャンして、目的の文字列を検索する並列ジョブでいくつかのプロシージャを実行します：dba_col_comments、dba_jobs、dba_mviews、dba_objects、dba_scheduler_jobs、dba_ba_source、dba_scheduler_job 、dba_views。各プロシージャは、何かが見つかった場合、検索結果テーブルに（対応する検索クエリIDを使用して）見つかったものを書き込みます。



すべての検索手順が完了すると、クライアントパーツは、対応する検索クエリIDを使用して検索結果テーブルに書き込まれるすべてのものをユーザーに提供します。

しかし、それだけではありません。 Oracleデータ辞書の検索に加えて、InformaticaPowerCenterリポジトリでの検索も説明されているメカニズムに組み込まれました。 Informatica PowerCenterは、Sberbankがさまざまな情報をデータウェアハウスにロードするために使用する人気のあるETLツールです。 Informatica PowerCenterには、オープンで十分に文書化されたリポジトリ構造があります。このリポジトリでは、Oracleデータディクショナリと同じ方法で情報を検索できます。 Informatica PowerCenterで開発されたダウンロードコードで使用されているテーブルとフィールドは何ですか？ポート変換と明示的なSQLクエリには何がありますか？このすべての情報は、リポジトリの構造で利用可能であり、見つけることができます。 PowerCenterの愛好家のために、私たちの検索エンジンがマッピング、セッション、またはワークフローを検索するために次のリポジトリの場所をスキャンすることを書きます。検索文字列をどこかに含む：sqlオーバーライド、マプレット属性、ポート、マッピングのソース定義、ソース定義、マッピングのターゲット定義、target_definitions、マッピング、マプレット、ワークフロー、ワークレット、セッション、コマンド、式ポート、セッションインスタンス、ソース定義フィールド、ターゲット定義フィールド、電子メールタスク。

: , SberProfi DWH/BigData.



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