Re: Archiving data into another database

Amen to Lex!  Included below is a SQL*Plus script that illustrates "poor-
man's partitioning" via a simple (and uncommented) example.

Side note of interest:  When Gary Dodge and I were finishing our first 
book, "Oracle8 Data Warehousing" in 1998, we had included extensive 
documentation on the use of "partition UNION-ALL views" in the chapter on 
partitioning, as such documentation was sorely lacking at the time as it is 
today.  However, Oracle's reviewers (as we were both employees of Oracle 
this was a stipulation) objected strenuously, to the point of threats to 
cancel the book, if we included *anything* about PV's, claiming that they 
were no longer relevant in a book about Oracle8.  Absurdly untrue, as plenty 
of databases using PV's persist to this day, but dangerous to the marketing 
mindset which correctly anticipated the demand for the pricey "partitioning 
option".  So, we were forced to remove everything about PV's, as if it 
didn't exist, as if they were mistakes found during technical review.  I 
think one paragraph stating that PV's were "an option" was permitted to 
remain, but nothing else.

So, I post the code below mainly out of long-deferred spite... :-)

Hope this helps, if anyone is intererested...



------------------ Begin SQL*Plus script ------------------ 
set echo on feedback on timing on
set pagesize 100 linesize 130 trimout on trimspool on pause off

spool pvtest

drop table pvtest_1;
drop table pvtest_2;
drop table pvtest_3;
drop table pvtest_4;
drop view pvtest;

create table pvtest_1
(
        col1            number(10)      not null,
        col2            number(10)      not null,
        col3            number(10)      not null,
        col4            number(10)      not null
);
alter table pvtest_1
        add constraint pvtest_1_pk
                primary key (col1)
                        using index;
alter table pvtest_1
        add constraint pvtest_1_ck1
                check (col2 >= 0 and col2 < 10);

create table pvtest_2
(
        col1            number(10)      not null,
        col2            number(10)      not null,
        col3            number(10)      not null,
        col4            number(10)      not null
);
alter table pvtest_2
        add constraint pvtest_2_pk
                primary key (col1)
                        using index;
alter table pvtest_2
        add constraint pvtest_2_ck1
                check (col2 >= 10 and col2 < 20);

create table pvtest_3
(
        col1            number(10)      not null,
        col2            number(10)      not null,
        col3            number(10)      not null,
        col4            number(10)      not null
);
alter table pvtest_3
        add constraint pvtest_3_pk
                primary key (col1)
                        using index;
alter table pvtest_3
        add constraint pvtest_3_ck1
                check (col2 >= 20 and col2 < 30);

create table pvtest_4
(
        col1            number(10)      not null,
        col2            number(10)      not null,
        col3            number(10)      not null,
        col4            number(10)      not null
);
alter table pvtest_4
        add constraint pvtest_4_pk
                primary key (col1)
                        using index;
alter table pvtest_4
        add constraint pvtest_4_ck1
                check (col2 >= 30 and col2 < 40);

alter session set partition_view_enabled = true;

create view pvtest
as
select * from pvtest_1
union all
select * from pvtest_2
union all
select * from pvtest_3
union all
select * from pvtest_4;

create or replace trigger ins_pvtest
    instead of insert
    on pvtest for each row
begin
    --
    if :new.col2 >= 0 and :new.col2 < 10 then
        --
        insert into pvtest_1
        values (:new.col1, :new.col2, :new.col3, :new.col4);
        --
    elsif :new.col2 >= 10 and :new.col2 < 20 then
        --
        insert into pvtest_2
        values (:new.col1, :new.col2, :new.col3, :new.col4);
        --
    elsif :new.col2 >= 20 and :new.col2 < 30 then
        --
        insert into pvtest_3
        values (:new.col1, :new.col2, :new.col3, :new.col4);
        --
    elsif :new.col2 >= 30 and :new.col2 < 40 then
        --
        insert into pvtest_4
        values (:new.col1, :new.col2, :new.col3, :new.col4);
        --
    else
        --
        raise_application_error(-20000, 'Value of COL2 out of bounds');
        --
    end if;
    --
end ins_pvtest;
/
show errors

create or replace trigger upd_pvtest
    instead of update
    on pvtest for each row
begin
--
if nvl(:new.col2, -1) = nvl(:new.col2, -1) then
        --
        if :new.col2 >= 0 and :new.col2 < 10 then
                --
                update pvtest_1
                set     col1 = :new.col1,
                        col2 = :new.col2,
                        col3 = :new.col3,
                        col4 = :new.col4
                where col1 = :old.col1
                and   col2 = :old.col2
                and   col3 = :old.col3
                and   col4 = :old.col4;
                --
        elsif :new.col2 >= 10 and :new.col2 < 20 then
                --
                update pvtest_2
                set     col1 = :new.col1,
                        col2 = :new.col2,
                        col3 = :new.col3,
                        col4 = :new.col4
                where col1 = :old.col1
                and   col2 = :old.col2
                and   col3 = :old.col3
                and   col4 = :old.col4;
                --
        elsif :new.col2 >= 20 and :new.col2 < 30 then
                --
                update pvtest_3
                set     col1 = :new.col1,
                        col2 = :new.col2,
                        col3 = :new.col3,
                        col4 = :new.col4
                where col1 = :old.col1
                and   col2 = :old.col2
                and   col3 = :old.col3
                and   col4 = :old.col4;
                --
        elsif :new.col2 >= 30 and :new.col2 < 40 then
                --
                update pvtest_4
                set     col1 = :new.col1,
                        col2 = :new.col2,
                        col3 = :new.col3,
                        col4 = :new.col4
                where col1 = :old.col1
                and   col2 = :old.col2
                and   col3 = :old.col3
                and   col4 = :old.col4;
                --
        else
                --
                raise_application_error(-20000, 'Value of COL2 out of 
bounds');
                --
        end if;
        --
elsif nvl(:new.col2, -1) <> nvl(:new.col2, -1) then
        --
        raise_application_error(-20000, 'Cannot update COL2 because I am 
lazy');
        --
end if;
--
end upd_pvtest;
/
show errors

create or replace trigger del_pvtest
    instead of delete
    on pvtest for each row
begin
    --
    if :old.col2 >= 0 and :old.col2 < 10 then
        --
        delete pvtest_1
        where col1 = :old.col1
        and   col2 = :old.col2
        and   col3 = :old.col3
        and   col4 = :old.col4;
        --
    elsif :old.col2 >= 10 and :old.col2 < 20 then
        --
        delete pvtest_2
        where col1 = :old.col1
        and   col2 = :old.col2
        and   col3 = :old.col3
        and   col4 = :old.col4;
        --
    elsif :old.col2 >= 20 and :old.col2 < 30 then
        --
        delete pvtest_3
        where col1 = :old.col1
        and   col2 = :old.col2
        and   col3 = :old.col3
        and   col4 = :old.col4;
        --
    elsif :old.col2 >= 30 and :old.col2 < 40 then
        --
        delete pvtest_4
        where col1 = :old.col1
        and   col2 = :old.col2
        and   col3 = :old.col3
        and   col4 = :old.col4;
        --
    else
        --
        raise_application_error(-20000, 'Value of COL2 out of bounds');
        --
    end if;
    --
end del_pvtest;
/
show errors

begin
        for i in 0..39 loop
                insert into pvtest values (i, i, i, i);
        end loop;
end;
/
show errors

commit;

exec dbms_stats.gather_table_stats(user, 'PVTEST_1',cascade=>TRUE);
exec dbms_stats.gather_table_stats(user, 'PVTEST_2',cascade=>TRUE);
exec dbms_stats.gather_table_stats(user, 'PVTEST_3',cascade=>TRUE);
exec dbms_stats.gather_table_stats(user, 'PVTEST_4',cascade=>TRUE);

set autotrace on
select count(*) from pvtest where col1 = 15;
select count(*) from pvtest where col2 = 15;
select count(*) from pvtest where col1 between 11 and 19;
select count(*) from pvtest where col2 between 11 and 19;
select count(*) from pvtest where col1 between 15 and 35;
select count(*) from pvtest where col2 between 15 and 35;
set autotrace off

update pvtest set col3 = 120 where col1 = 20;
select * from pvtest where col1 = 20;

delete pvtest where col1 = 25;
select * from pvtest where col1 = 25;

spool off
------------------ End SQL*Plus script ------------------ 

On Wed, 31 Jan 2007 16:37:36 +0100 (CET), Carel-Jan Engel wrote
> Which brings me to a reference to a link to the archives of this list:
> 
> http://www.freelists.org/archives/oracle-l/06-2005/msg00782.html
> 
> A post linking to 'poor man's partitioning' by Lex de Haan, who left 
> this planet(R) at Febr. 1st 2006. It is a year ago, tomorrow. He's 
> missed by many.
> 
> Regards, Carel-Jan
> 
> ===
> If you think education is expensive, try ignorance. (Derek Bok)
> ===
> 
> >  Having had good results with partitioning at my last job, that would be
> > my
> > preference.  Unfortunately, partitioning isn't an option when using
> > Standard
> > Edition One as we are.  The company isn't yet in a position financially 
to
> > pay the licensing fees for an editiion that supports partitioning.  This
> > is
> > why I'm looking for other options.  I still am leaning towards an archive
> > table in the same database.
> >
> > Sandy
> >
> >
--
http://www.freelists.org/webpage/oracle-l

Other related posts: