** high water mark for small tables
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 26 Jul 2009 17:45:38 -0700 (PDT)
Hi,
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.
Thanks
Other related posts:
