Re: Archiving data into another database
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 31 Jan 2007 09:11:10 -0700
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:
>
> //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
> >
> >
--
//www.freelists.org/webpage/oracle-l
Other related posts:
