| Table rebuild should be done after removing most of its data? [message #569234] |
Tue, 23 October 2012 06:32  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi All,
We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.
However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it help to rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?
I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?
Thanks
|
|
|
|
|
|
| Re: Table rebuild should be done after removing most of its data? [message #569238 is a reply to message #569236] |
Tue, 23 October 2012 07:12  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Thank you Michel!! I just saw that this shrink operation also requires to "enable row movement". My table is heavily used by the application. So when I do this in Produciton, alter table shrink, are there any other impact?
I mean can this SHRINK operation be done online (when the table is accessible to the users?) or do we need to stop users access and run this command?
Thanks
|
|
|
|
|
|
| Re: Table rebuild should be done after removing most of its data? [message #570132 is a reply to message #569234] |
Mon, 05 November 2012 17:47 |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
"alter table ... move;" invalidates indexes where "alter table ... shrink space;" does not.
Unless the table is heavily accessed, you can do the "alter table ... shrink space;" while the table is being accessed. The "alter table ... shrink space;" will
1) Move all rows in the table to the same extents.
2) Reset the rowid's in the indexes so that the indexes will NOT become unusable.
3) Remove extra extents from the table once the rows are copied.
ENDOCP1P > alter table alan enable row movement;
Table altered.
ENDOCP1P > alter table alan shrink space;
Table altered.
ENDOCP1P > alter table alan disable row movement;
Table altered.
I do snapshots on gv$segment_statistics to see if the table is being accessed. If not, I can slip in a lot of maintenance during lulls in the batch jobs.
ENDOCP1P > @logical605
GIG_PER_MINUTE INSTANC STATISTIC_NAME TYPE OBJECT_NAME
-------------- ------- -------------- ----- ----------------------------
.001 NDOCP1 logical reads TABLE SYS.SEG$
.001 NDOCP1 logical reads INDEX SYS.I_OBJ4
.001 NDOCP1 logical reads INDEX SYS.I_COL3
.001 NDOCP1 logical reads TABLE SYS.JOB$
.001 NDOCP1 logical reads INDEX SYS.I_SYN2
.001 NDOCP1 logical reads INDEX SYS.I_TS#
.001 NDOCP1 logical reads INDEX SYS.I_OBJ5
.003 NDOCP1 logical reads INDEX SYS.I_COL1
.003 NDOCP1 logical reads INDEX SYS.I_COL2
.003 NDOCP1 logical reads TABLE SYS.FILE$
.004 NDOCP1 logical reads INDEX SYS.I_OBJ#
.025 NDOCP2 logical reads TABLE SYS.IND$
.034 NDOCP4 logical reads TABLE SYS.IND$
.042 NDOCP4 logical reads INDEX SYS.I_IND1
.045 NDOCP3 logical reads TABLE SYS.IND$
.048 NDOCP3 logical reads INDEX SYS.I_IND1
.048 NDOCP1 logical reads INDEX SYS.I_IND1
.053 NDOCP2 logical reads INDEX SYS.I_IND1
.104 NDOCP3 logical reads TABLE SYS.OBJ$
.104 NDOCP4 logical reads TABLE SYS.OBJ$
.107 NDOCP2 logical reads TABLE SYS.OBJ$
.116 NDOCP3 logical reads TABLE SYS.USER$
.123 NDOCP4 logical reads TABLE SYS.USER$
.135 NDOCP2 logical reads TABLE SYS.USER$
.170 NDOCP1 logical reads TABLE SYS.OBJ$
.217 NDOCP1 logical reads TABLE SYS.USER$
.258 NDOCP1 logical reads TABLE SYS.TS$
.267 NDOCP1 logical reads INDEX SYS.I_OBJ1
.422 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_R
.595 NDOCP2 logical reads TABLE SYS.TS$
.602 NDOCP2 logical reads INDEX SYS.I_OBJ1
.608 NDOCP3 logical reads TABLE SYS.TS$
.624 NDOCP4 logical reads TABLE SYS.TS$
.640 NDOCP3 logical reads INDEX SYS.I_OBJ1
.642 NDOCP4 logical reads INDEX SYS.I_OBJ1
.776 NDOCP2 logical reads TABLE NFLPROD.DM_SYSOBJECT_S
.844 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D8000010A
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000109
1.649 NDOCP2 logical reads INDEX NFLPROD.D_1F000D5D80000146
2.451 NDOCP2 logical reads INDEX NFLPROD.DM_SYSOBJECT_R_COMP2
2.451
----------
sum 13.421
logical605.sql looks like the following:
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
column statistic_name format a14
column object_name format a50
column object_type format a11
column gig_per_minute format 9999.999
column type format a5
column instance format a7
SET WRAP OFF
SET TRUNC OFF
SET LINES 200
break on report
compute sum of gig_per_minute break on report
compute sum of buf_per_min break on report
compute sum of buf_per_sec break on report
create table gv$segment_statistic1 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
execute dbms_lock.sleep(5);
create table gv$segment_statistic2 as
select * from gv$segment_statistics
where upper(statistic_name) like '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
set wrap off
set pages 50
set termout on
select b.value last, a.value first,(b.value-a.value)*12 buf_per_min,
(b.value-a.value)*12/60 buf_per_sec,
(b.value-a.value)*12*8192/1024/1024/1024*1 GIG_PER_MINUTE,
i.instance_name instance,a.statistic_name,a.object_type type,
a.owner||'.'||a.object_name||' '||a.subobject_name object_name
from gv$segment_statistic2 b,gv$segment_statistic1 a,gv$instance i
where a.statistic_name=b.statistic_name and a.inst_id=b.inst_id
and b.inst_id=i.inst_id and a.inst_id=i.inst_id
and a.object_type=b.object_type
and a.owner||'.'||a.object_name||' '||a.subobject_name
=b.owner||'.'||b.object_name||' '||b.subobject_name
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
set termout on
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
