RE: ** high water mark for small tables
Date: Mon, 27 Jul 2009 18:06:08 -0400
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.
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
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
March 7 - 11, 2010
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
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.
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