Re: reduce table fragmentation by design
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 25 Jan 2011 17:18:43 -0700
LSC,
Attached is a SQL*Plus script with a demo test case for partitioned
UNION-ALL views, in case you're interested in seeing how easy it
is? I've had this script (or one like it) since Oracle7 days...
Hope this helps...
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal => 13087 West Cedar Drive #225, Lakewood CO 80228
website => http://www.EvDBT.com/
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
On 1/25/2011 1:41 PM, Stephens, Chris wrote:
Why not just roll your own weekly partitions?
A table for each week + UNION ALL view + some
PL/SQL to truncate oldest weekly table + possibly some
modifications to either the existing loading procedures.
Hi
We have a database which receives statistics data every 5
minutes and this data goes to a heap table, not partitioned
because this is Standard Edition 10.2.0.4.
The table has around 50 million of rows, most data are
inserted using insert into select........., around 80%, the
rest 20% plain insert into... values....
The table is formed by a surrogate key, a timestamp and
several columns which stores numbers, every week or so there
is a purge process whcih retains 45 days data, the column used
to purge is the timestamp.
The problem is after sometime the table is so fragmentated
that to read 10000 rows through 10000 index scans each row is
stored in different data blocks. So basically for each 10000
scan 10000 blocks must be read (plus the index scan but that
is little, only 60 blocks or so). The way to relieve this
performance problem is rebuild the table and the indexes.
I wonder if following would help to reduce fragmentation, in
the insert... select statement add an order by timestamp
clause so all rows are inserted in order, since the purge
process is based on timestamp this will remove rows
sequentially (located in same extent).
I have some other ideas such as shrink table compact space
once a couple of times a day and shrink the table once 2 days,
then every month rebuild the table once using alter table
move.
I am also thiking to move the table to manually segment space
management with old freelists and pctused.
Thanks
--
LSC
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity
to which it is addressed and may contain information that is
privileged, confidential and exempt from disclosure under
applicable law. If the reader of this message is not the
intended recipient or the employee or agent responsible for
delivering this message to the intended recipient, you are
hereby notified that any dissemination, distribution or copying
of this communication is strictly prohibited. If you have
received this communication in error, please notify us
immediately by email reply.
|
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
Other related posts:
