Re: Caching for "LIKE" queries?

On Mar 20, 12:30 pm, "fraserofthenight" <fraseroftheni..._at_gmail.com> wrote:
> Hi,
>
> I was wondering what sort of caching is done for repeated queries, and
> how long results stay in the cache. For example, suppose I had a table
> named "files" in which there was a (varchar, nonclustered-indexed)
> field called "name", taht is queried with a query similar to "SELECT *
> FROM files WHERE name LIKE (?)". If one user queried "harry" and the
> next user queried "harry potter", would any caching have been done, as
> teh results of the second query would most likely be a subset of the
> results of the first. What if the files table had been changed in
> between?
>
> Specifically, I'm wondering if I should implement a cache at the
> application level (or in the database, since all the queries are
> called using stored procs -- is this a good idea at all?), or whether
> the database will take care of that for me.
>
> Thanks,
> Fraser

This is the function of the buffer cache in the SGA. See the concepts manual at tahiti.oracle.com. It depends on the totality of what is being done to the database and the structure of the tables. With proper design, Oracle will usually have several rows stored in a block, so if the blocks wind up in the buffer cache, similar rows will likely be there. Which blocks are kept is determined by a least recently used algorithm, which can be modified based on how Oracle is accessing the table. This is determined by the access paths the optimizer decides on. If you use proper relational design techniques, you will likely be working in the same direction as the optimizer. You can see which blocks are in the cache with certain views such as v $bh, see the performance tuning manual for basics when you have an actual system working.

Oracle is particularly good with issues of multiple users accessing and changing rows, so it is really worth your while not to reinvent this wheel. In other words, let Oracle handle transaction control, don't try to do things like some other dbms engines do, anything you can do as a set in SQL is better than playing with cursors. If there winds up being a problem anyways, there is instrumentation in the db to deal with it.

It generally is good to use stored procedures, the database is the place to keep stuff. Doing things in the application layer inevitably leads to someone doing it wrong.

Highly recommend books by Tom Kyte to see how and why to actually do this stuff, after you go through the concepts manual.

jg

--
@home.com is bogus.
http://www.bmezine.com/news/people/A10101/addsub/