Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in01.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:45 -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:45 +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: 36 X-Usenet-Provider: http://www.giganews.com X-AuthenticatedUsername: NoAuthUser X-Trace: sv3-nMAH6Sx57/aHrV2Vo377tTWNntmdxaOdzjO12AJFOypAEqszviLaZZW37iqyPo5VsKldykJ2+cyRn0m!A+Bfk85c+kk9qx7n1KoTZ9+y3yvs5k1SVQVtOFk2AHCussjGT44XtP3/S0CVX2l6ykEgsTA5z5Of 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:445381 X-Received-Date: Fri, 06 Jun 2008 08:52:45 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 ? > > Thank you for your help, > xtanto. By the way, you are allowed to create indexes on the tables that sit under materialized views - just don't make them unique indexes -- 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