** high water mark for small tables

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Sun, 26 Jul 2009 17:45:38 -0700 (PDT)
Message-ID: <469840.64890.qm_at_web57505.mail.re1.yahoo.com>


I am looking at high water mark for tables and doing exp/imp or truncate or move of tables to improve performance by eliminating fragmentation/lowering HWM.

For smaller tables : I am assuming exp/imp or move need not be done since the table is small anyway and will not make much improvement. Especially tables less than 1 MB. Please let me know if any benefit to do this for small/tiny tables. Any other things to keep in mind before doing this or particular tables to avoid. I am saving the statistics. I also think sometimes if the small table is in join with other big tables : it could degrade performance after this if explain plan chooses to use index for small table instead of big or changes plan in some other ways. Any such experiences?

The reason to do this is : when this was done for some big tables : there was improvement  :especially for full table scan. There was improvement for some index with rebuild for access when single sql had multiple index access. So in some cases for big table/index : there is improvement.

In any case : the question here is about tiny tables (less than 1 MB) : is there any gain to do rebuild for tiny table? Has anyone seen any improvement with rebuild of tiny table and what is exact scenario. Thanks a lot.


Received on Sun Jul 26 2009 - 19:45:38 CDT

Original text of this message