Re: FTS on small Materialized View, should I cache it in the KEEP Pool ?

On Jun 6, 7:52 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <krisl..._at_gmail.com> wrote in message
>
> news:6e592059-5080-4908-a847-d7921412100a_at_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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Hi,
Thank you for your reply.

>you are allowed to create indexes on the tables that sit under materialized views
What dou you mean that that ?

Doesn't the running query access the MV itself, not the base table ?

So what is the benefit of creating index on its base table ?

Thank you,
xtanto Received on Sun Jun 08 2008 - 03:08:33 CDT