RE: ** high water mark for small tables

Any table that that occupies fewer blocks after a rebuild type operation will have improved performance for a full table scan since fewer blocks have to read for the full table scan. It's likely on smaller tables the improvement wouldn't be enough for most folks to notice. Of course if you have a small table that is being hammered by many queries all doing full table scans, then the individual query improvement might be small but the fact that each query is doing less work (fewer LIOs) then the over all system is likely to improve.  

Of course some times it's good to have data spread over many blocks too. RAC environments can benefit from data being sparsely packed data since this can mean that there is less pressure for the blocks to be pinged between nodes. But this has more to do with the setting of PCTFREE and not the HWM, so that is likely a different discussion anyway.  

As a side note, I find it funny in a sick way that these days we talk about table less then a Meg as Tiny...  

Ric Van Dyke

Hotsos Enterprises

Hotsos Symposium

March 7 - 11, 2010

Be there.    

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of A Joshi Sent: Sunday, July 26, 2009 8:46 PM
To: oracle-l_at_freelists.org
Subject: ** high water mark for small tables  

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  

--
http://www.freelists.org/webpage/oracle-l