Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Archiving data into another database

Re: Archiving data into another database

From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 31 Jan 2007 09:11:10 -0700
Message-Id: <20070131155444.M87102@evdbt.com> 





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...








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


Received on Wed Jan 31 2007 - 10:11:10 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US