- 自动编译记录
- 如果要使用参数创建视图怎么办
- 在查询中使用动态统计信息
- 通过数据库链接插入数据时如何保存查询计划
- 在并行会话中运行过程
- 通过剩菜剩饭
- 将多个故事合并为一个
- 归一化
- SVG格式渲染
- Oracle元数据搜索应用程序
自动编译记录
在某些Oracle数据库上，Sberbank有一个编译触发器，可以记住服务器对象代码中的更改对象，更改时间和更改内容。因此，可以从编译日志表中确定更改的作者。版本控制系统也会自动实现。无论如何，如果程序员忘记将更改提交给Git，则此机制将套期保值。让我们描述这种自动编译日志记录系统的实现示例。编译触发器的简化版本之一以ddl_changes_log表的形式写入日志，如下所示：
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的开发人员经常可以满足这种需求。为什么可以创建带有参数的过程或函数，但是没有可用于计算的带有输入参数的视图？在我们看来，Oracle可以用某种方法来替代这种缺失的概念。
让我们来看一个例子。假设有一张每天的销售额表。
create table DIVISION_SALES
(
division_id INTEGER,
dt DATE,
sales_amt NUMBER
);此查询比较两天内的部门销售额。在这种情况下，为04/30/2020和09/11/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;我想写一个观点来总结这样的请求。我想将日期作为参数传递。但是，语法不允许这样做。
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';那些。查询可以先连接所有表，处理不必要的大量数据，然后再按条件field1 ='myvalue'过滤结果，而不是先按条件field1 ='myvalue'过滤deep_table。通过使用分配给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和account表，并使用哈希联接将它们联接。
现在，让我们从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表，并使用索引从accounts中读取行。
如果删除了dynamic_sampling提示，则在第二种情况下，该计划将与第一种情况相同，这不是最佳选择。
可以在文档中找到有关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;| RUN_ID | GROUP_ID | PROC_SCRIPT | 职位编号 | 开始时间 | 时间结束 |
| 1个 | 1个 | 开始入睡（5）; 结束; | 1个 | 09/11/2020 15:00:51 | 09/11/2020 15:00:56 |
| 1个 | 1个 | 开始睡眠（10）; 结束; | 2 | 09/11/2020 15:00:51 | 09/11/2020 15:01:01 |
我们看到测试过程实例的执行时间达到了预期。
通过剩菜剩饭
让我们描述一种解决“拉余额”的相当典型的银行问题的变体。假设有一张帐户余额变化事实表。必须指出日历每一天（该天的最后一个）的当前帐户余额。数据仓库中经常需要此类信息。如果某天计数没有变化，那么您需要重复最后一次已知的余数。如果服务器的数据量和计算能力允许，那么您可以使用SQL查询来解决此问题，而无需诉诸PL / SQL。函数（按*划分为*排序为*）上的last_value（*忽略null）将在此方面为我们提供帮助，它将最后一个已知的余数延展到随后的日期，这些日期没有变化。
让我们创建一个表并用测试数据填充它。
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;
查询结果与预期的一样。
| DT | 帐户ID | BALANCE_AMT | TURNOVER_AMT |
| 2020年1月1日 | 1个 | 23 | 23 |
| 2020年1月2日 | 1个 | 23 | 0 |
| 2020年3月1日 | 1个 | 23 | 0 |
| 2020/04/01 | 1个 | 23 | 0 |
| 2020年1月5日 | 1个 | 44 | 21 |
| 2020年6月1日 | 1个 | 44 | 0 |
| 2020年7月1日 | 1个 | 44 | 0 |
| 2020年1月8日 | 1个 | 44 | 0 |
| 2020/09/01 | 1个 | 44 | 0 |
| 2020年1月1日 | 1个 | 44 | 0 |
| 2020年1月5日 | 2 | 77 | 77 |
| 2020年6月1日 | 2 | 77 | 0 |
| 2020年7月1日 | 2 | 72 | -五 |
| 2020年1月8日 | 2 | 72 | 0 |
| 2020/09/01 | 2 | 72 | 0 |
| 2020年1月1日 | 2 | 72 | 0 |
将多个故事合并为一个
当将数据加载到存储中时，当您需要为实体建立单个历史记录时，通常会解决该问题，该实体具有来自各种来源的该实体属性的单独历史记录。假设存在一个具有主键primary_key_id的实体，位于这三个不同的表中，关于该实体的三个不同属性的历史记录（start_dt-end_dt）已知。
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);目标是将三个属性的单个更改历史记录加载到一个表中。
下面是解决此问题的查询。它首先用来自不同来源的数据针对不同的属性形成对角线表q1（源中不存在的属性用空值填充）。然后，使用last_value（* ignore nulls）函数，将对角线表折叠为单个历史记录，并将最后一个已知的属性值扩展到它们没有更改的日期：
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;结果是这样的：
| PRIMARY_KEY_ID | START_DT | END_DT | 属性1 | 属性2 | 属性3 |
| 1个 | 2014年1月1日 | 2014年12月31日 | 7 | 空值 | 空值 |
| 1个 | 2015年1月1日 | 2015年12月31日 | 8 | 4 | 空值 |
| 1个 | 2016年1月1日 | 2016年12月31日 | 九 | 五 | 十 |
| 1个 | 2017年1月1日 | 2017年12月31日 | 九 | 6 | 20 |
| 1个 | 01.01.2018 | 31.12.9999 | 九 | 6 | 三十 |
| 2 | 2014年1月1日 | 2014年12月31日 | 17 | 空值 | 空值 |
| 2 | 2015年1月1日 | 2015年12月31日 | 十八 | 十四 | 空值 |
| 2 | 2016年1月1日 | 2016年12月31日 | 19 | 十五 | 110 |
| 2 | 2017年1月1日 | 2017年12月31日 | 19 | 十六 | 120 |
| 2 | 01.01.2018 | 31.12.9999 | 19 | 十六 | 130 |
归一化
有时会出现规范化以分隔字段格式出现的数据的问题。例如，以这样的表格形式：
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;结果是这样的：
| ID | VAL值 | COLUMN_VALUE |
| 1个 | aa | 1个 |
| 1个 | cccc | 2 |
| 1个 | bb | 3 |
| 2 | ddd | 1个 |
| 3 | ff | 1个 |
| 3 | Ë | 2 |
SVG格式渲染
通常，人们希望以某种方式可视化存储在数据库中的数字指示器。例如，构建图形，直方图，图表。专用工具（例如Oracle BI）可以提供帮助。但是，使用这些工具的许可证可能要花钱，并且设置它们所需的时间可能比向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是另一个数字指标，我们将以具有此半径的圆形绘制。
我们来谈谈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子查询会记住先前的时间点，从这些时间点需要将线绘制到新的点。 “标题”块是具有白色背景的图像的标题。 “垂直线”块绘制垂直线。 “垂直线下的日期”块标签的日期在x轴上。 “水平线”块绘制水平线。 ``水平线附近的值''块在y轴上标记值。 'circles'块在graph_data表中绘制指定半径的圆。“图形数据”块从线的graph_data表构建val指示器动态图。 “页脚”块添加了结尾标记。
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的文件中，并在浏览器中查看。如果需要，可以使用任何实用程序将其转换为其他图形格式，将其放置在应用程序的网页上，等等。
结果如下图：
Oracle元数据搜索应用程序
想象一下，试图通过一次在多台服务器上查找信息来在Oracle源代码中找到一些东西。这是关于搜索Oracle数据字典对象。搜索的工作地点是Web界面，用户程序员在其中输入搜索字符串并选择要在其上执行此搜索的Oracle服务器的复选框。
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界面接收用户输入的搜索字符串，要搜索的服务器列表以及用户的登录名。该网页将它们传递给处理程序服务器上的Oracle存储过程。向搜索引擎的请求的历史记录，即谁执行了哪个请求，以防万一。
收到搜索查询后，Oracle搜索服务器上的服务器端会在并行作业中运行多个过程，这些过程在选定的Oracle服务器上的数据库链接上扫描以下数据字典视图以搜索所需的字符串：dba_col_comments，dba_jobs，dba_mviews，dba_objects，dba_scheduler_jobs，dba_tabsource，dba_tabsource，dba_tabsource，dba_tabsource，dba_tabsource，dba_tabsource ，dba_views。每个过程（如果找到了内容）都会将找到的内容写到搜索结果表中（带有相应的搜索查询ID）。
完成所有搜索过程后，客户端部分将为用户提供在搜索结果表中写入的所有内容以及相应的搜索查询ID。
但这还不是全部。除了在Oracle数据字典中进行搜索外，在Informatica PowerCenter信息库中的搜索也被固定在所描述的机制中。 Informatica PowerCenter是Sberbank使用的一种流行的ETL工具，用于将各种信息加载到数据仓库中。 Informatica PowerCenter具有一个开放的，文档齐全的存储库结构。在此存储库上，可以按照与Oracle数据字典中相同的方式搜索信息。 Informatica PowerCenter开发的下载代码中使用了哪些表和字段？在端口转换和显式SQL查询中可以找到什么？所有这些信息都可以在存储库的结构中找到，并且可以找到。对于PowerCenter鉴赏家，我会写道，我们的搜索引擎会扫描以下存储库位置，以查找映射，会话或工作流，在某处包含搜索字符串：sql覆盖，mapplet属性，端口，映射中的源定义，源定义，映射中的目标定义，target_definitions，映射，mapplet，工作流，工作集，会话，命令，表达式端口，会话实例，源定义字段，目标定义字段，电子邮件任务。
: , SberProfi DWH/BigData.
SberProfi DWH/BigData , Hadoop, Teradata, Oracle DB, GreenPlum, BI Qlik, SAP BO, Tableau .