Home » RDBMS Server » Performance Tuning » After Table analyze, the number of blocks goes Down (Oracle 11gR2 )
After Table analyze, the number of blocks goes Down [message #619767] Thu, 24 July 2014 23:35 Go to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Hello,

When I moved the TABLE to another tablespace then I rebuild all the respective indexes and gathered statistics for the table,
the number of BLOCKS reduced.
So, can anyone please help me why the number of BLOCKS reduced?

Many thanks in advance.
Re: After Table analyze, the number of blocks goes Down [message #619771 is a reply to message #619767] Fri, 25 July 2014 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

When you move a table blocks are compacted, that is, generally, there is less free space in the blocks.

Re: After Table analyze, the number of blocks goes Down [message #619775 is a reply to message #619771] Fri, 25 July 2014 00:30 Go to previous messageGo to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Thanks for quick reply, so when we move the table it will compact and as a result the number of BLOCKS also reduced?

Also, does the move and reduction of blocks also affect insert query performance in future?
Re: After Table analyze, the number of blocks goes Down [message #619777 is a reply to message #619775] Fri, 25 July 2014 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sometimes yes, sometimes, no.

Re: After Table analyze, the number of blocks goes Down [message #619780 is a reply to message #619777] Fri, 25 July 2014 01:02 Go to previous messageGo to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Its related to INSERT performance?
Re: After Table analyze, the number of blocks goes Down [message #619790 is a reply to message #619780] Fri, 25 July 2014 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.

Re: After Table analyze, the number of blocks goes Down [message #619806 is a reply to message #619767] Fri, 25 July 2014 02:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
hitesh.bhatt wrote on Fri, 25 July 2014 05:35
Hello,

When I moved the TABLE to another tablespace then I rebuild all the respective indexes and gathered statistics for the table,
the number of BLOCKS reduced.
So, can anyone please help me why the number of BLOCKS reduced?

Many thanks in advance.
Think of it like this: the table is not atually moved. The underlying implementation is that a new table is created, all the rows inserted into it, the original table is dropped, and the new table renamed. You can see all this happening if you query appropriate views while the move is progress. Following the insert all the block of the new table will be fully populated, whereas the original table might have had some space emptied by deletions.
Re: After Table analyze, the number of blocks goes Down [message #620218 is a reply to message #619806] Wed, 30 July 2014 04:14 Go to previous message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Thanks for information
Previous Topic: Partitioning on a VARCHAR2 column
Next Topic: How to remove left join and ON JOIN from query and use better things in place of that
Goto Forum:
  


Current Time: Thu Mar 28 04:18:01 CDT 2024