Category Archives: dwh-consult

Temporary tables

Prinzip der temporären Tabellen

Die Daten werden automatisch vom Datenbanksystem gelöscht.
Die Lebensdauer ist einstellbar: bis zum Ende der laufenden Transaktion oder bis zum Ende der Session.
Die Daten der parallel laufenden Sessions sind automatisch vollständig voneinander getrennt. Es ist nicht notwendig, die Daten über zusätzliche Spalten den einzelnen Sessions zuzuordnen.
Aufgrund der automatischen Trennung der Daten zwischen den einzelnen Sessions sind keine Sperren notwendig und auch nicht verfügbar.
Typische Anwendung: komplexe Auswertungen, die Zwischendaten halten müssen.
Es werden keine Redo-Informationen geschrieben.
Die Verwendung von temporären Tabellen bietet deutliche Performance-Vorteile gegenüber “normalen” Tabellen.

Wichtigste Einschränkungen

Es sind keine foreign key constraints mit temporärer Tabelle als Ziel erlaubt.
Eine temporäre Tabelle ist nicht als index-organized table definierbar.

Beispiele

Daten werden bis Transaktionsende gehalten

create global temporary table tmp_artikel (
artikel_nr number(5),
umsatz number(10,2))
on commit delete rows;

Die Daten können mit den üblichen Befehlen (insert, update, delete, select) bearbeitet werden.
Nach dem Ende der Transaktion ist die Tabelle automatisch leer.

Daten werden bis Sessionende gehalten

create global temporary table tmp_artikel (
artikel_nr number(5),
umsatz number(10,2))
on commit preserve rows;

Die Daten dieser temporären Tabelle sind so lange verfügbar, bis die Datenbank-Session beendet wird.

Escaping special Characters in SQL

Sonderzeichen/Special Character in SQL escaping/entwerten

Escaping in a SQL-Statement All Tables starting with ‘T_’.

select * from user_tables
where table_name like 'T\_%' escape '\';

All tables without underscore (‘_’)

select * from user_tables
where table_name not like '%\_%' excape '\';

Escaping in a SQL-Plus-Session: All Tables, starting with ‘T_’.

set escape '\'
select * from user_tables
where table_name like 'T\_%';

Searching in Data-Dictionary-Views for Views user_views, all_views…

select * from user_views
where dbms_xmlgen.getxml
('select text from user_views where view_name = || view_name || ')
like '%w_spci_order_types%'

Installation ORACLE Database 11.2 on DEBIAN Linux

ORACLE-DB Installation DEBIAN Linux (Lenny)
Oracle DB 11.2 on DEBIAN
http://www.oxalis.de/Oracle11gR2aufDebianLenny.html

ORACLE-DB Installation SUSE Linux
Package-Dependencies/Abhängigkeiten/Requirements for SUSE-Linux at Paket/Package orarun.rpm.

ORACLE-DB InstallationORACLE Enterprise Linux
Package-Dependencies/Abhängigkeiten/Requirements for ORACLE Enterprise Linux in Paket/Package oracle-validated.rpm.

PLSQL-Case

CASE-Ausdruck
Gibt einen Wert zurück/returns a value

set serveroutput on
declare
myid NUMBER;
myresult VARCHAR2(10);
begin
myresult :=
case
when myid=null then 'true'
when myid is null then 'is null'
else 'false'
end;
dbms_output.put_line(myresult);
END;
/

CASE-Anweisung
Führt eine Aktion aus / Processes an action

set serveroutput on
declare
myid NUMBER;
myresult VARCHAR2(10);
begin
case
when myid=null then
myresult :='true';
when myid is null then
myresult :='is null';
else myresult :='false';
end case;
dbms_output.put_line(myresult);
END;
/

Bulk Processing

CREATE OR REPLACE PROCEDURE COPY_EMPS(limit_in IN PLS_INTEGER DEFAULT 100) is
-- EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('emp_bulk_test', 'emp_bulk_errors');
-- create table emp_bulk_test as select * from employees;
-- truncate table emp_bulk_test;
CURSOR employees_cur
IS
SELECT * FROM employees;
TYPE employees_rec IS TABLE OF employees_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_rec employees_rec;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_rec LIMIT limit_in;
FOR indx IN 1 .. l_rec.COUNT
LOOP
insert into emp_bulk_test
(employee_id
,first_name
,last_name
,email
,phone_number
,hire_date
,job_id
,salary
,commission_pct
,manager_id
,department_id)
VALUES (l_rec(indx).employee_id
,l_rec(indx).first_name
,l_rec(indx).last_name
,l_rec(indx).email
,l_rec(indx).phone_number
,l_rec(indx).hire_date
,l_rec(indx).job_id
,l_rec(indx).salary
,l_rec(indx).commission_pct
,l_rec(indx).manager_id
,l_rec(indx).department_id)
LOG ERRORS INTO emp_bulk_errors
;
END LOOP;
commit;
dbms_output.put_line('commit!');
EXIT WHEN l_rec.COUNT < limit_in; END LOOP; CLOSE employees_cur; end copy_emps; /

Adding Attribute with default to compressed Table

First Possibility: Adding the Attribute in one alter step

— mark table as uncompressed (only mark, here no uncompressing!)
ALTER TABLE <table> nocompress;
/
–uncompress table in the same tablespace
ALTER TABLE l<table> move;
/

ALTER TABLE <table> nocompress;
/

— Add Attribute with defaultvalue
ALTER TABLE <table> ADD ( start_dt DATE DEFAULT TO_DATE(‘31.12.9999′,’dd.mm.yyyy’) NOT NULL );
/

— compress table
ALTER TABLE load_dim_business_parties compress;
/

Second Possibility: Adding the Attribute without not null / default value an do an separate update
Here is the possibility to do the update in parallel.

BEGIN
EXECUTE IMMEDIATE ‘drop table compress_add_attribute’;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

CREATE TABLE compress_add_attribute(id number) compress;

BEGIN
FOR ii in 1 .. 10000
loop
insert into compress_add_attribute (id) values(ii);
end loop;
commit;
END;

——————————————————————————————-
——————————————————————————————-

— restartability
BEGIN
EXECUTE IMMEDIATE ‘ALTER TABLE compress_add_attribute ADD ( begin_dt DATE, end_dt DATE)’;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/

COMMENT ON COLUMN compress_add_attribute.begin_dt IS ‘Beginn History.’;
/
COMMENT ON COLUMN compress_add_attribute.end_dt IS ‘End History.’;
/

DECLARE

l_row_cnt_1      NUMBER;
l_row_cnt_2      NUMBER;
l_tablespace   VARCHAR2(30);

BEGIN

pkg_utl_log.log ( ‘Start update compress_add_attribute: ‘
|| TO_CHAR ( SYSDATE
, ‘dd.mm.yyyy hh24:mi:ss’ ) );

EXECUTE IMMEDIATE ‘ALTER SESSION FORCE PARALLEL DML PARALLEL 8’;

EXECUTE IMMEDIATE ‘ALTER SESSION FORCE PARALLEL DDL PARALLEL 8’;

EXECUTE IMMEDIATE ‘ALTER TABLE compress_add_attribute NOCOMPRESS’;

— Select the tablespace with the most free space
SELECT tablespace_name
INTO l_tablespace
FROM (SELECT tablespace_name
FROM ts_usage
WHERE tablespace_name LIKE ‘TS_WORK’ –place here some %-pattern
ORDER BY free_gb DESC
)
WHERE ROWNUM = 1;

EXECUTE IMMEDIATE ‘ALTER TABLE compress_add_attribute MOVE TABLESPACE ‘
|| l_tablespace;

UPDATE compress_add_attribute
SET begin_dt = to_date(‘19000101′,’yyyymmdd’);

l_row_cnt_1   := sql%ROWCOUNT;

COMMIT;

UPDATE compress_add_attribute
SET end_dt   = to_date(‘99991231′,’yyyymmdd’);

l_row_cnt_2   := sql%ROWCOUNT;

COMMIT;

pkg_utl_log.log(‘compress_add_attribute: updated begin_dt/end_dt ‘||l_row_cnt_1|| ‘/’||l_row_cnt_2|| ‘ rows).’);

EXECUTE IMMEDIATE   ‘ALTER TABLE compress_add_attribute COMPRESS’;

— Select the tablespace with the most free space.
SELECT tablespace_name
INTO l_tablespace
FROM (   SELECT tablespace_name
FROM ts_usage
WHERE tablespace_name LIKE ‘TS_WORK’ –place here some %-pattern
ORDER BY free_gb DESC
)
WHERE ROWNUM = 1;

EXECUTE IMMEDIATE   ‘ALTER TABLE compress_add_attribute MOVE TABLESPACE ‘
|| l_tablespace;

EXECUTE IMMEDIATE ‘ALTER TABLE compress_add_attribute
MODIFY ( begin_dt NOT NULL, end_dt NOT NULL )’;

pkg_utl_log.log ( ‘Finished update compress_add_attribute: ‘
|| TO_CHAR ( SYSDATE
, ‘dd.mm.yyyy hh24:mi:ss’ ) );
EXCEPTION

WHEN OTHERS THEN
pkg_utl_log.log ( ‘IN EXCEPTION’ );
pkg_utl_log.log ( SQLERRM
, pkg_utl_log.gc_fatal
, SQLCODE );
RAISE;
END;

Combination of SCD2 Histories

This is a solution for combinate two scd2-histories into one new scd2-history

drop table hist1 purge;
drop table hist2 purge;
create table hist1
(
hist1_id varchar2(10)
, join_key number
, begin_dt date
, end_dt   date
);
create table hist2
(
hist2_id varchar2(10)
, join_key number
, begin_dt date
, end_dt   date
);

insert into hist1(hist1_id, join_key, begin_dt, end_dt)
values(‘A’,11,to_date(‘20100101′,’yyyymmdd’),to_date(‘20120601′,’yyyymmdd’));
insert into hist1(hist1_id, join_key, begin_dt, end_dt)
values(‘B’,11,to_date(‘20110601′,’yyyymmdd’),to_date(‘20121201′,’yyyymmdd’));
insert into hist2(hist2_id, join_key, begin_dt, end_dt)
values(‘1’,11,to_date(‘20110101′,’yyyymmdd’),to_date(‘20120201′,’yyyymmdd’));
insert into hist2(hist2_id, join_key, begin_dt, end_dt)
values(‘2’,11,to_date(‘20110201′,’yyyymmdd’),to_date(‘20120701′,’yyyymmdd’));
insert into hist2(hist2_id, join_key, begin_dt, end_dt)
values(‘3’,11,to_date(‘20110701′,’yyyymmdd’),to_date(‘20121101′,’yyyymmdd’));
commit;

create or replace view hist1_hist2 as
WITH — all distinct dates in both histories
days AS (SELECT   begin_dt dt FROM hist1
UNION
SELECT   end_dt FROM hist1
UNION
SELECT   begin_dt FROM hist2
UNION
SELECT   end_dt FROM hist2),
history1 AS (SELECT   d.dt,
h1.hist1_id,
h1.join_key,
h1.begin_dt,
h1.end_dt
FROM   days d, hist1 h1
WHERE   d.dt >= h1.begin_dt(+) AND d.dt < h1.end_dt(+)),
history2 AS (SELECT   d.dt,
h2.hist2_id,
h2.join_key,
h2.begin_dt,
h2.end_dt
FROM   days d, hist2 h2
WHERE   d.dt >= h2.begin_dt(+) AND d.dt < h2.end_dt(+))
SELECT   hist1_id,
hist2_id,
join_key,
CASE
WHEN h1_begin_dt < h2_begin_dt THEN h2_begin_dt
ELSE h1_begin_dt
END
AS begin_dt,
CASE WHEN h1_end_dt > h2_end_dt THEN h2_end_dt ELSE h1_end_dt END
AS end_dt
—     , h1_begin_dt
—     , h1_end_dt
—     , h2_begin_dt
—     , h2_end_dt
FROM   (  SELECT   –DISTINCT                                  —     , h_1.dt
h_1.hist1_id,
h_2.hist2_id,
h_1.join_key,
h_1.begin_dt AS h1_begin_dt,
h_1.end_dt AS h1_end_dt,
h_2.begin_dt AS h2_begin_dt,
h_2.end_dt AS h2_end_dt
FROM   history1 h_1, history2 h_2
WHERE   1 = 1 AND h_1.dt = h_2.dt AND h_1.join_key = h_2.join_key
)
;

Filling not complete SCD2 History

This is a solution if the scd-2 history of a dimension is not complete so there history is holey. This SQL adds all missing data.

SELECT   -98,
a.party_id_2,
1 as relation_type_id,
a.end_dt,
b.begin_dt
FROM   (SELECT   ROWNUM rn1,
a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   (  SELECT   DISTINCT a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   VW_PROSPECTS_TO_CUSTOMERS a,
VW_PROSPECTS_TO_CUSTOMERS b
WHERE   a.party_id_2 = b.party_id_2
AND a.begin_dt <> b.begin_dt
ORDER BY   a.party_id_2, a.begin_dt, a.end_dt) a) a,
(SELECT   ROWNUM rn2,
a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   (  SELECT   DISTINCT a.PARTY_ID_1,
a.PARTY_ID_2,
a.RELATION_TYPE_ID,
a.BEGIN_DT,
a.END_DT
FROM   VW_PROSPECTS_TO_CUSTOMERS a,
VW_PROSPECTS_TO_CUSTOMERS b
WHERE   a.party_id_2 = b.party_id_2
AND a.begin_dt <> b.begin_dt
ORDER BY   a.party_id_2, a.begin_dt, a.end_dt) a) b
WHERE       a.party_id_2 = b.party_id_2
AND rn1 = rn2 – 1
AND a.end_dt <> b.begin_dt