Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!news1.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local02.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail NNTP-Posting-Date: Fri, 06 Jun 2008 07:52:02 -0500 From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server References: <6e592059-5080-4908-a847-d7921412100a@v26g2000prm.googlegroups.com> Subject: Re: FTS on small Materialized View, should I cache it in the KEEP Pool ? Date: Fri, 6 Jun 2008 13:52:03 +0100 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-RFC2646: Format=Flowed; Original Message-ID: Lines: 39 X-Usenet-Provider: http://www.giganews.com X-AuthenticatedUsername: NoAuthUser X-Trace: sv3-uyj1azUeKttBmTYUtf3tmYjTWiqh9ZvbAttlBauDvZox6jwio+XX+IxFY8BOACWDv0m/VT0GuGnBla3!8nhqHpgeHgd57fptI6H7PPDU3L4r1DGesM+EOXk8lBI1l9VkQ74cqHMdooc4GHdO//uO3Cnek3sP X-Complaints-To: abuse@btinternet.com X-DMCA-Complaints-To: abuse@btinternet.com X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.39 Xref: usenetserver.com comp.databases.oracle.server:445380 X-Received-Date: Fri, 06 Jun 2008 08:52:03 EDT (text.usenetserver.com) wrote in message news:6e592059-5080-4908-a847-d7921412100a@v26g2000prm.googlegroups.com... > Hi all, > > I have a small MV (1773 rows) that is used in a Query JOIN (the query > & the explain plan is attached below). Although I already create index > for the MV, it is always FTS in the query. > I read a Tuning tips, that FTS on small table should be cached in the > KEEP POOL, with this command : > ALTER TABLE ITT.MV_CONVERT_UOM STORAGE (BUFFER_POOL KEEP); > > Should I do this ? > That looks like a 10g plan. The small tables in the keep pool thing was only relevant in 8i and 9i where there was a bug relating to touch counts on small tables subject to tablescans. If the table deserves to be cached, it will stay in the cache without any help. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html