RE: ** high water mark for small tables

From: Bobak, Mark <>
Date: Mon, 27 Jul 2009 18:06:08 -0400
Message-ID: <>

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: [] On Behalf Of Ric Van Dyke Sent: Monday, July 27, 2009 5:56 PM
To:; Cc:; 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: [] On Behalf Of Jared Still Sent: Monday, July 27, 2009 1:08 PM
Cc:; Subject: Re: ** high water mark for small tables

On Mon, Jul 27, 2009 at 5:15 AM, Mark W. Farnham <<>> 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

-- Received on Mon Jul 27 2009 - 17:06:08 CDT

Original text of this message