Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Checking the rebuildability of an index

RE: Checking the rebuildability of an index

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Tue, 06 Aug 2002 13:13:41 -0800
Message-ID: <F001.004ACAEB.20020806131341@fatcity.com> 





Ethan,



Are you talking about this? last part of Tom Kyte reply and this relating to 
8i...not 7.3.4



Make sure to BENCHMARK yourself here.  Before rebuilding -- capture 
statistics,


performance metrics, IO's, etc -- against that index.  After rebuilding --
compare the results.  If you got nothing back, no increased performance,
decreased IO, etc -- don't rebuild it again later -- it doesn't buy you
anything.



Yes, I agree with this but interface tables behaving totally differently as 
these tables involves thousands of rows (and not couple of rows)which are 
inserted and deleted and indexes getting more and more space with a lot of 
holes in it hamperring the performance. The most used interface table is 
GL_INTERFACE having 6 indexes and invloving thousands of rows everday and 
there are some TEMP type of tables (customised) which involves 2 to 3 
hundred thousands rows involved for deletion at one time so those indexes 
are not releasing space besides performance of such tables become slow day 
by day.


This is 7.3.4.5 database with Oracle Financials 10.7 char. To get release 
space and to reduce HWM on this tables we have to truncate such tables from 
time to time once we find there are no rows exist at that time and it also 
improves performance of these tables.



Here, as we have no option to change code of Oracle Financials , but to 
manage objects at our advantage based on our experience and contribution of 
this list...



I hope this clarifies...



Regards


Mohammad Rafiq







Reply-To: ORACLE-L_at_fatcity.com


To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Tue, 06 Aug 2002 12:13:23 -0800



OK, but first read the article that Jesse shared,
http://asktom.oracle.com/pls/ask/f?p=4950:8:1134696::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:2290062993260,%7Bindex%7D%20and%20%7Brebuild%7D, then tell
us why the INTERFACE tables don't match that scenario.  I am all for getting
at the truth here and I am not exactly sure what it is.  I do know that Tom
and Jonathan usually run a lot of tests and provide performance data to
substantiate there findings.  I wouldn't mind seeing some based on the
INTERFACE tables you speak of.  Also what good is releasing space if it is
just going to be grabbed again the next time the INTERFACE table is used?



Ethan Post


(972) 577-6552


perotdba (AIM), epost1 (Yahoo)






-----Original Message-----



Sent: Tuesday, August 06, 2002 2:34 PM


To: Multiple recipients of list ORACLE-L




Ethan,



With due respect to  both Tom Kyte and Jonathan Lewis,if database has tables



with lot of deletions like Oracle Financials 'INTERFACE' tables, regular
rebuilding of indexes really help in keeping good performance and release of



good amount of space. If you don't rebuild indexes for a while on such
tables then performance degrades very fast.



Regards


Rafiq






Reply-To: ORACLE-L_at_fatcity.com


To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: Tue, 06 Aug 2002 10:38:23 -0800



If memory serves correct (and it might not) both Tom Kyte and Jonathan Lewis
have stated that in most circumstances rebuilding indexes is useless.  I
think Tom may have said he could count the # of times he had to rebuild an
index on one hand (something like that).  Thus, I have stopped worrying
about this one so much and will only entertain rebuilds if I start to see
performance issues.  Of course if you have the time and need to look busy
keep rebuilding those indexes :)



By the way the reason it is useless is not that there is never a performance
gain but that most indexes that need to be rebuild quickly degrade again and
the period that one experiences a performance gain is minimal.  Operating
off a poor memory here so hopefully I have not misrepresented anyone.



Ethan Post


perotdba (AIM), epost1 (Yahoo)






-----Original Message-----



Sent: Tuesday, August 06, 2002 1:05 PM


To: Multiple recipients of list ORACLE-L




Actually, I do the 'validate structure' as well, and neglected to include
it in my previous post.



Jared




  > also send the HELP command for other information (like subscribing).
  >



--





Sign-up for your own FREE Personalized E-mail at Mail.com
http://www.mail.com/?sr=signup



Get 4 DVDs for $.49 cents! plus shipping & processing. Click to join.
http://adfarm.mediaplex.com/ad/ck/990-1736-3566-59



--



Please see the official ORACLE-L FAQ: http://www.orafaq.com


--



Author: Rajesh Dayal


    INET: rdayal73_at_techie.com




--



Please see the official ORACLE-L FAQ: http://www.orafaq.com


--



Author: Post, Ethan


    INET: Ethan.Post_at_ps.net


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).








Chat with friends online, try MSN Messenger: http://messenger.msn.com



--



Please see the official ORACLE-L FAQ: http://www.orafaq.com


--



Author: Mohammad Rafiq


   INET: rafiq9857_at_hotmail.com


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


--



Please see the official ORACLE-L FAQ: http://www.orafaq.com


--



Author: Post, Ethan


   INET: Ethan.Post_at_ps.net


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).








Send and receive Hotmail on your mobile device: http://mobile.msn.com



-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 



Author: Mohammad Rafiq


  INET: rafiq9857_at_hotmail.com


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message


to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Aug 06 2002 - 16:13:41 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US