| a question [message #59439] |
Thu, 20 November 2003 12:41  |
sidd
Messages: 130
Registered: May 2003
|
Senior Member |
|
|
if I do
create table a_temp as select * from a; and i drop table 'a' and rename a_temp to 'a' will this take care of fragmentation?
|
|
|
|
|
|
|
|
| Re: a question [message #59444 is a reply to message #59443] |
Thu, 20 November 2003 14:53  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
again, what type of fragmentation within the table ?
I've heard of people rebuilding the table to reduce the number of extents(thinking thats fragmentation) and I just want to make sure that I am not encouraging that by answering your question.
alter table 'table_name' move;
is an easier way ,but again I dont recommend rebuilding the table just to reduce the number of extents.
-Thiru
|
|
|
|
| Re: a question [message #59445 is a reply to message #59444] |
Thu, 20 November 2003 14:58 |
sidd
Messages: 130
Registered: May 2003
|
Senior Member |
|
|
|
am using 8i, and i used the scripts in this site to find out the High Water Mark. it looks like used blks and HWM blks are not same, so i thought its framented. i already analyze the table and computed statistics on the table.
|
|
|
|
| Re: a question [message #59448 is a reply to message #59442] |
Fri, 21 November 2003 03:05 |
Djay
Messages: 2
Registered: November 2003
|
Junior Member |
|
|
Hi,
It would make sense for you to drop the table and recreate it in the following scenarios
a) If the table is having indexes associated with it and also lot of inserts and deletes take place on the table. In case of deletes and inserts happen, the table size as such do not grow, since the space below HWM is utilised (except in some cases like parallel DML, or DIRECT import or sqlloader). However the deletes do not free the space below HWM for INDEX and subsequent inserts happen above the HWM of the index.
b) If you want to reorg the storage characteristics of the table.
In both the cases the best way is
create table a_temp as select * from a; and then truncate table 'a'
In case need be modify the storage parameters of 'a' and then insert all the data from a_temp to 'a'
when you create table a_temp as select * from a, the table a_temp do not have the constraints you define on 'a' and hence the suggestion to truncate 'a' rather than drop 'a'
Hope this helps.
Cheers
Djay
|
|
|
|
| Re: a question [message #59450 is a reply to message #59448] |
Fri, 21 November 2003 05:21 |
sidd
Messages: 130
Registered: May 2003
|
Senior Member |
|
|
Well, This table gets updated and inserted rows daily, and this is a very big table. i have 100 columns in this table, what would be the good storage parameters for this table ? appreciate your help.
regards.
|
|
|
|
| Re: a question [message #59451 is a reply to message #59445] |
Fri, 21 November 2003 05:39 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
In most cases (where there are deletes etc), you will have some blocks below the HWM that are not used and hence they wont be the same. Those will be in the Freelist of the table and will be subsequently used for inserts. This is pretty normal .
A good case for resetting the HWM is when the table has say 10 Million rows, you delete 9 million rows , and the table growth for say the next 2 years will be 1-2 million rows. Now you want to reset the HWM to reclaim all that space and to make your full table scans scan only upto 2-3 million rows.
Again, Alter table move is a much simpler solution ..
-Thiru
|
|
|
|
| Re: a question [message #59453 is a reply to message #59451] |
Fri, 21 November 2003 09:20 |
sidd
Messages: 130
Registered: May 2003
|
Senior Member |
|
|
|
how would i determain what pctfree and pctused should i use for my table with 100 fields in it. is there any docs that i can look in. or any math i sould do b4 i create my table.
|
|
|
|
| Re: a question [message #59454 is a reply to message #59448] |
Fri, 21 November 2003 09:40 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
a) "However the deletes do not free the space below HWM for INDEX and subsequent inserts happen above the HWM of the index."
Incorrect assumption. Space freed up by deletes in Indexes DO GET REUSED , whenever possible.
For eg)
thiru@9.2.0:SQL>analyze table t compute statistics;
Table analyzed.
thiru@9.2.0:SQL>select index_name,leaf_blocks from user_indexes where table_name='T';
INDEX_NAME LEAF_BLOCKS
-------------------- -----------
T_IDX 362
thiru@9.2.0:SQL>select segment_name,blocks,bytes from user_segments where segment_name='T_IDX';
SEGMENT_NAME BLOCKS BYTES
------------------------------ ---------- ----------
T_IDX 512 2097152
-- Lets delete some rows
thiru@9.2.0:SQL>delete from t where rownum < 10000;
9999 rows deleted.
thiru@9.2.0:SQL>commit;
Commit complete.
thiru@9.2.0:SQL>analyze table t compute statistics;
Table analyzed.
-- The number of Leaf blocks dont decrease as expected
thiru@9.2.0:SQL>select index_name,leaf_blocks from user_indexes where table_name='T';
INDEX_NAME LEAF_BLOCKS
-------------------- -----------
T_IDX 362
-- Nows lets insert some rows
thiru@9.2.0:SQL>insert into t select * from all_objects where rownum < 5000;
4999 rows created.
thiru@9.2.0:SQL>commit;
Commit complete.
thiru@9.2.0:SQL>analyze table t compute statistics;
Table analyzed.
thiru@9.2.0:SQL>select index_name,leaf_blocks from user_indexes where table_name='T';
INDEX_NAME LEAF_BLOCKS
-------------------- -----------
T_IDX 362
-- The number of Leaf blocks and the total number of Index blocks DO NOT INCREASE. This means the freed up space (by deletes) have been reused.
thiru@9.2.0:SQL>select segment_name,blocks,bytes from user_segments where segment_name='T_IDX';
SEGMENT_NAME BLOCKS BYTES
------------------------------ ---------- ----------
T_IDX 512 2097152
-- this shows that the Inserts do reuse the space below HWM whenever possible. The Index expands only when needed.
b) If you want to alter the storage characterstics of the table, you dont have to recreate it always. Use Alter table storage/MOVE instead. Further if you use Locally Managed tablespaces with automatic segment space management, you can forget about the storage characterstics.
-Thiru
|
|
|
|
| Re: a question [message #59455 is a reply to message #59453] |
Fri, 21 November 2003 09:43 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Look at the ORacle documentation at tahiti.oracle.com
Move to Locally managed tablespaces with automatic segment space management and loose your storage parameters..
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
