Re: Caching for "LIKE" queries?

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 20 Mar 2007 21:27:26 -0700
Message-ID: <1174451244.996584@bubbleator.drizzle.com>

Vince wrote:
> 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

SQL> CREATE TABLE t (

   2 testcol VARCHAR(30));

Table created.

SQL> desc t

  Name                                      Null?    Type
  ----------------------------------------- -------- ------------
  TESTCOL                                            VARCHAR2(30)

SQL> Compare what is created with with the DDL.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Mar 20 2007 - 23:27:26 CDT