Re: Caching a huge table's data in memory

Hi,

Allow me to be the first of many to ask - why do you want to cache a huge table in memory? Important questions include:

• Define huge (both in depth [row count] and width [row size])?
• What type of queries are you running against this table?
• What access paths are being used (ie: via index or frequent full table scans)?
• What is currently in memory and going to be thrown out to make room for this table? (RAM is a bit like a bus - there are a set number of seats and you can't let more people on without kicking someone out [despite what those wierd products in the early 90's tried to convince us])

"alter table [table_name] cache;" is a valid command as far as I know but I'm sure some experts here will better explain the inner workings of Oracle. Reading Oracle documentation talks about caching small tables which are subject to full table scans. The cache instruction (I believe) simply tells Oracle to put them at the end of the list of things to throw out, instead of the start. If Oracle had the room is would cache the huge table anyway without that parameter.

It sounds a little like a developer has told you to cache the table because someone told him to make the query run faster. Unfortunately it's rarely that simple.

Regards,

     Mark.

                                                                                                                                     
                    "Krishnaswamy, Ranganath"                                                                                        
                    <Ranganath.Krishnaswamy_at_blr.hpsg       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    lobal.com>                             cc:                                                                       
                    Sent by: root_at_fatcity.com              Subject:     Caching a huge table's data in memory                        
                                                                                                                                     
                                                                                                                                     
                    07/01/2003 15:53                                                                                                 
                    Please respond to ORACLE-L                                                                                       
                                                                                                                                     
                                                                                                                                     

Hi List,

           I have a requirement for caching the data of a huge table in memory.
Is it just running the command Alter table <tablename> cache or something else? What are the pros and cons of caching a table's data? Could anybody advise me in this regard? Any help in this regard is very much appreciated.

Thanks and Regards,

Ranganath
WARNING: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
  INET: Ranganath.Krishnaswamy_at_blr.hpsglobal.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).