------=_Part_1247_32469310.1209473983973
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Thanx for the help.
Need some more help.
"table1" has two indices
unique indx1 = "pkfld"
unique indx2 = "fkfld1,fkfld2"
did following steps in the listed order -
1. vacuumed the whole DB
2. "table1"
RecCnt ==> 11970789
Size ==> 2702.41 MB
3.update "table1" set fld7 = 1000 where fld1/1000000 = 999 ;
this UPDATED 1230307 records
4. checked "table1" size again
Reccnt => 11970789
Size ==> 2996.57MB
5. Again did the update, update "table1" set fld7 = 1000 where
fld1/1000000
= 999 ;
this UPDATED 1230307 records
6. Got "table1" size as
RecCnt ==> 11970789
Size ==> 3290.64
7. Updated again, update "table1" set fld7 = 1000 where fld1/1000000 = 999
;
this UPDATED 1230307 records
6. "table1" size as
RecCnt ==> 11970789
Size ==> 3584.66
Found that the size increased gradually. Is HOT working over here ??
Guide me if im doing something wrong.
~ Gauri
On Tue, Apr 29, 2008 at 4:55 PM, Pavan Deolasee <pavan.deolasee@[EMAIL PROTECTED]
>
wrote:
> On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
> <meetgaurikanekar@[EMAIL PROTECTED]
> wrote:
>
> >
> > Do we need to do any special config changes or any other setting for
HOT
> to
> > work??
>
> No. HOT is enabled by default, on all tables. There is no way and need
> to disable it.
>
> >
> > Any special guideline to follow to make HOT working??
> >
>
> You can do couple of things to benefit from HOT.
>
> 1. HOT addresses a special, but common case where UPDATE operation
> does not change any of the index keys. So check if your UPDATE changes
> any of the index keys. If so, see if you can avoid having index
> involving that column. Of course, I won't advocate dropping an index
> if it would drastically impact your frequently run queries.
>
> 2. You may leave some free space in the heap (fillfactor less than
> 100). My recommendation would be to leave space worth of one row or
> slightly more than that to let first UPDATE be an HOT update.
> Subsequent UPDATEs in the page may reuse the dead row created by
> earlier UPDATEs.
>
> 3. Avoid any long running transactions.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>
--
Regards
Gauri
------=_Part_1247_32469310.1209473983973
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Thanx for the help. <br><br>Need some more help.<br><br>"table1"
has two indices<br>unique indx1 = "pkfld"<br>unique indx2 =
"fkfld1,fkfld2"<br><br>did following steps in the listed order
-<br>
<br>1. vacuumed the whole DB<br>2. "table1" <br>
RecCnt ==> 11970789 <br> Size ==>
2702.41 MB<br>3.update "table1" set fld7 = 1000 where
fld1/1000000 = 999 ;<br> this UPDATED 1230307
records<br>
4. checked "table1" size again<br>
Reccnt => 11970789<br> Size ==>
2996.57MB<br>5. Again did the update, update "table1" set fld7 =
1000 where fld1/1000000 = 999 ;<br> this UPDATED 1230307
records<br>
6. Got "table1" size as<br> RecCnt ==>
11970789<br> Size ==> 3290.64<br>7. Updated again, update
"table1" set fld7 = 1000 where fld1/1000000 = 999 ;<br>
this UPDATED 1230307 records<br>
6. "table1" size as<br>
RecCnt ==> 11970789<br>
Size ==> 3584.66<br><br>Found that the size increased
gradually. Is HOT working over here ??<br>Guide me if im doing something
wrong.<br><br>~ Gauri<br><br><div class="gmail_quote">On Tue, Apr 29, 2008
at 4:55 PM, Pavan Deolasee <<a
href="mailto:pavan.deolasee@[EMAIL PROTECTED]
">pavan.deolasee@[EMAIL PROTECTED]
>>
wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204,
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="Ih2E3d">On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar<br>
<<a
href="mailto:meetgaurikanekar@[EMAIL PROTECTED]
">meetgaurikanekar@[EMAIL PROTECTED]
>>
wrote:<br>
<br>
><br>
> Do we need to do any special config changes or any other setting for
HOT to<br>
> work??<br>
<br>
</div>No. HOT is enabled by default, on all tables. There is no way and
need<br>
to disable it.<br>
<div class="Ih2E3d"><br>
><br>
> Any special guideline to follow to make HOT working??<br>
><br>
<br>
</div>You can do couple of things to benefit from HOT.<br>
<br>
1. HOT addresses a special, but common case where UPDATE operation<br>
does not change any of the index keys. So check if your UPDATE changes<br>
any of the index keys. If so, see if you can avoid having index<br>
involving that column. Of course, I won't advocate dropping an
index<br>
if it would drastically impact your frequently run queries.<br>
<br>
2. You may leave some free space in the heap (fillfactor less than<br>
100). My recommendation would be to leave space worth of one row or<br>
slightly more than that to let first UPDATE be an HOT update.<br>
Subsequent UPDATEs in the page may reuse the dead row created by<br>
earlier UPDATEs.<br>
<br>
3. Avoid any long running transactions.<br>
<div><div></div><div class="Wj3C7c"><br>
Thanks,<br>
Pavan<br>
<br>
--<br>
Pavan Deolasee<br>
EnterpriseDB <a href="http://www.enterprisedb.com"
target="_blank">http://www.enterprisedb.com</a><br>
</div></div></blockquote></div><br><br clear="all"><br>--
<br>Regards<br>Gauri
------=_Part_1247_32469310.1209473983973--
|
