Schlagwort-Archive: ORACLE

dwh-consult

PLSQL-Case

Schreibe einen Kommentar

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

dwh-consult

Bulk Processing

Schreibe einen Kommentar

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

dwh-consult

Adding Attribute with default to compressed Table

Schreibe einen Kommentar

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;

dwh-consult

Combination of SCD2 Histories

Schreibe einen Kommentar

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

dwh-consult

Filling not complete SCD2 History

Schreibe einen Kommentar

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