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.
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]
|
