Home » RDBMS Server » Performance Tuning » 2 tables same rowcount different size
2 tables same rowcount different size [message #243635] Fri, 08 June 2007 02:43 Go to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
Hi there,

Hope you guys can help me out?
I have got 2 tables in 2 environments (production, acceptance)
They do not differ in row count or table structure, but they do differ in size.
In other words: 2 tables are identical however the size on disk is different. How do I optimize the tables?
Block size for both environment is 16k.

Thanks so much!
Re: 2 tables same rowcount different size [message #243637 is a reply to message #243635] Fri, 08 June 2007 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "optimize".

Regards
Michel
Re: 2 tables same rowcount different size [message #243641 is a reply to message #243637] Fri, 08 June 2007 03:14 Go to previous messageGo to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
Michel Cadot wrote on Fri, 08 June 2007 02:56
Define "optimize".

Regards
Michel



Reduce them in size.

I have found an article on that says space of deleted rows is not actually freed.

Therefore the following statements should do the trick:

alter table tablename move;
alter index index_table rebuild;

Have anybody used them, should I know more before I use these statements?

@Michel: thanks for your swift reply!
Re: 2 tables same rowcount different size [message #243646 is a reply to message #243641] Fri, 08 June 2007 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this statement will do the trick.
What do you want to know more? They rebuild the objects with the default storage parameters. If you want other parameters then use related storage clause.
They lock the objects unless you use the "online" keyword.

Regards
Michel
Re: 2 tables same rowcount different size [message #243650 is a reply to message #243635] Fri, 08 June 2007 03:46 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Quote:
2 tables are identical however the size on disk is different


Looks at the no. of extents allocated. Are they same?

Brayan
Re: 2 tables same rowcount different size [message #243653 is a reply to message #243650] Fri, 08 June 2007 04:11 Go to previous messageGo to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
Brayan wrote on Fri, 08 June 2007 03:46
Hi,

Quote:
2 tables are identical however the size on disk is different


Looks at the no. of extents allocated. Are they same?

Brayan


@Michel I did some more research there are no delete procedures performed on the table as far as I can see...

Therefore
@Brayant:
I executed this one
select EXTENTS from dba_segments where segment_name ='TABLE_NAME';

EXTENTS
----------------------
122
1 rows selected
EXTENTS
----------------------
660
1 rows selected

Where can I find more info about EXTENTS then?
Is it the correct way to retrieve the EXTENT parameter?
Re: 2 tables same rowcount different size [message #243654 is a reply to message #243635] Fri, 08 June 2007 04:21 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

So the extents are different in two environment, so the size. Embarassed

Just google for docs.

May be this doc will help you.

Brayan
Re: 2 tables same rowcount different size [message #243676 is a reply to message #243635] Fri, 08 June 2007 06:12 Go to previous messageGo to next message
Duke
Messages: 14
Registered: August 2006
Junior Member
<edit> sorry stupid question this was....

Still confused how to get the tables the same size....

[Updated on: Fri, 08 June 2007 06:30]

Report message to a moderator

Re: 2 tables same rowcount different size [message #243683 is a reply to message #243635] Fri, 08 June 2007 06:57 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

create/alter table's with same stoarage parameters.
Insert records/ alter table.. move.
Check the extents & size.

Brayan.
Previous Topic: Pipelining Table Functions
Next Topic: Oracle 9i Performance and Tuning question
Goto Forum:
  


Current Time: Sun Dec 11 06:12:57 CST 2016

Total time taken to generate the page: 0.11726 seconds