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