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

<krislioe_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 ?
>

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