RE: ** high water mark for small tables
Date: Mon, 27 Jul 2009 18:06:08 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F1C773B39_at_AAPQMAILBX02V.proque.st>
Agreed. If you have to hit most of the table blocks, you can't beat an FTS. What I think Jared was getting at, is that there is (or at least used to be) a myth that small tables did not have to be indexed. It can easily be demonstrated that an index can be beneficial, even for a single-row table.
-Mark
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke
Sent: Monday, July 27, 2009 5:56 PM
To: jkstill_at_gmail.com; mwf_at_rsiz.com
Cc: ajoshi977_at_yahoo.com; oracle-l_at_freelists.org
Subject: RE: ** high water mark for small tables
Or not.
All depends on what you are retrieving, when hitting most of the blocks of a table, a FTS is the way to go no matter how big or small the table is.
Another application solution that might help this is to cache the small table somewhere in the app. Then stop hitting the table at all. Of course this would only work with a static table, and could be a nightmare to recode the app to do this.
Oracle's results cache will effetely do this for you starting in 11.
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 Jared Still Sent: Monday, July 27, 2009 1:08 PM
To: mwf_at_rsiz.com
Cc: ajoshi977_at_yahoo.com; oracle-l_at_freelists.org Subject: Re: ** high water mark for small tables
On Mon, Jul 27, 2009 at 5:15 AM, Mark W. Farnham <mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>> wrote:
... So if you have an itsy bitsy table and it is read a lot by FTS inside loops that can really add up.
And in such a case, serious consideration should be given to creating an appropriate index.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 27 2009 - 17:06:08 CDT