Re: Caching for "LIKE" queries?

On Mar 20, 1:10 pm, DA Morgan <damor..._at_psoug.org> wrote:
> fraserofthenight 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
>
> Based on what you've written my assumption would be that you are
> brand new to Oracle.
>
> No data type named VARCHAR
> No such thing as a nonclustered-index
> a field called "name" would be misusing a keyword
> etc.
>
> Result sets are not cached ... blocks may be.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel,

I am not sure what you mean when you say there is no data type as varchar. I know that it is preferred to use varchar2, but as far as I can tell, varchar is still supported:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as xxx

xxx_at_xxx-dev> declare

            2     v  varchar(30);
            3  begin
            4     v := 'Hello World';
            5     dbms_output.put_line( v );
            6  end;
            7  /

Hello World

PL/SQL procedure successfully completed

xxx_at_xxx-dev> create table varchar_test ( test_name varchar(30) );

Table created Received on Tue Mar 20 2007 - 22:59:40 CDT