FTS on small Materialized View, should I cache it in the KEEP Pool ?
kris...@gmail.com
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.
Query & explain PLAN :
SELECT so_id_hdr, product_ord, qty_ord, UOM, MV.UOM_B, MV.UOM_K
FROM SALESORDER_D SOD
JOIN MV_CONVERT_UOM MV ON MV.PRODUCT = SOD.PRODUCT_ORD
WHERE SO_id_hdr = 31944
[pre]
Plan hash value:
1323612888-----------------------------------------------------------------------------------------------------------------------------------|
Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib
|-----------------------------------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT | | 5 |
225 | 5 (20)| 00:00:01 | | | || 1 | PX
COORDINATOR | | |
| | | | | || 2 | PX
SEND QC (RANDOM) | :TQ10001 | 5 | 225 |
5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) ||* 3 | HASH
JOIN | | 5 | 225 | 5
(20)| 00:00:01 | Q1,01 | PCWP | || 4 | BUFFER
SORT | | | |
| | Q1,01 | PCWC | || 5 | PX
RECEIVE | | 5 | 135 | 2
(0)| 00:00:01 | Q1,01 | PCWP | || 6 | PX SEND
BROADCAST | :TQ10000 | 5 | 135 | 2 (0)|
00:00:01 | | S->P | BROADCAST || 7 | TABLE ACCESS BY
INDEX ROWID| SALESORDER_D | 5 | 135 | 2 (0)| 00:00:01
| | | ||* 8 | INDEX RANGE
SCAN | SALESORDER_D_FKH | 5 | | 1 (0)|
00:00:01 | | | || 9 | PX BLOCK
ITERATOR | | 1773 | 31914 | 2 (0)|
00:00:01 | Q1,01 | PCWC | || 10 | MAT_VIEW ACCESS
FULL | MV_CONVERT_UOM | 1773 | 31914 | 2 (0)|
00:00:01 | Q1,01 | PCWP |
|-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation
id):--------------------------------------------------- 3 -
access("MV"."PRODUCT"="SOD"."PRODUCT_ORD") 8 -
access("SOD"."SO_ID_HDR"=31944)
[/pre]
Jonathan Lewis
<kris...@gmail.com> wrote in message
news:6e592059-5080-4908...@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
Jonathan Lewis
>
> 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
kris...@gmail.com
wrote:
> <krisl...@gmail.com> wrote in message
>
> news:6e592059-5080-4908...@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
>
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.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
Jonathan Lewis
<kris...@gmail.com> wrote in message
news:69dda8b0-da20-4b0d...@t12g2000prg.googlegroups.com...
>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 ?
>
A materialized view is defined through a select statement:
create materialized view xxx
as
select ...
This results in the creation of an object of type "materialized view"
called XXX and an object of type table calls XXX. My reference
to a table "under the materialized" view was about the table XXX,
not to any of the tables in the select statement.
--
Regards
Jonathan Lewis