-
materialized view takes hours
This materized view takes 17 hours. the two tables have 4 million rows approximately. POS_MODS has 4mil and item_master has 10 mil rows.
IS there anything I can do speed up building materialize views.
Code:
create materialized VIEW POSINT.POSITEMPROMOTIONS_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS
SELECT
pm.pricing_method,
pm.prom_type,
pm.create_date,
pm.approval_date,
pm.start_date,
pm.start_time,
pm.item,
pm.discount_type,
pm.discount_amt,
pm.threshold_num,
pm.prom_id,
pm.seq_num,
pm.store,
pm.end_date,
pm.end_time,
pm.ends_in,
pm.immediate_dl,
getnetcost(storeitems.item_parent, pm.store, pm.prom_id) netcost,
getmfgroupid(pm.store, pm.pricing_method, pm.prom_id, pm.seq_num) mf_group_id
FROM
pricemethod pm,
(select /*+ INDEX(pm XXX_POS_MODS_REF_ITEM)*/
pm.store, pm.ref_item, im.item_parent, im.item_grandparent
from
pos_mods pm, item_master im
where
pm.ref_item = im.item) storeitems
WHERE
pm.store = storeitems.store
and (pm.item = storeitems.ref_item OR pm.item = storeitems.item_parent OR pm.item = storeitems.item_grandparent)
-
crystal ball broken today, how about a query plan?
Jeff Hunter
-
Originally posted by marist89
crystal ball broken today, how about a query plan?
Plus there's two user-defined functions in there ... no matter how efficient the SQL, those functions could be the hold-up, so try the MV query without them and see what that does.
-
Code:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 42494 | 7635K| | 14927 |
|* 1 | HASH JOIN | | 42494 | 7635K| 8040K| 14927 |
| 2 | VIEW | PRICEMETHOD | 51087 | 7433K| | 22 |
| 3 | UNION-ALL | | | | | |
|* 4 | HASH JOIN | | 51568 | 4633K| | 164 |
|* 5 | HASH JOIN SEMI | | 111 | 7215 | | 102 |
|* 6 | HASH JOIN | | 2239 | 113K| | 98 |
|* 7 | TABLE ACCESS BY INDEX ROWID | PROMHEAD | 100 | 2800 | | 66 |
| 8 | INDEX FULL SCAN | PROMHEAD_I1 | 167 | | | 3 |
|* 9 | TABLE ACCESS FULL | PROMSTORE | 3442 | 82608 | | 31 |
| 10 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
| 11 | VIEW | VW_NSO_4 | 23 | 299 | | 2 |
|* 12 | TABLE ACCESS FULL | STORE_SEED_SCHEDULE | 23 | 276 | | 2 |
| 13 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
|* 14 | TABLE ACCESS FULL | PROMSKU | 46560 | 1227K| | 61 |
|* 15 | HASH JOIN SEMI | | 15 | 1440 | | 38 |
|* 16 | HASH JOIN | | 310 | 25730 | | 35 |
| 17 | INDEX FULL SCAN | PK_PROM_MIX_MATCH_BUY | 14 | 252 | | 2 |
| 18 | NESTED LOOPS | | 111 | 7215 | | 32 |
|* 19 | HASH JOIN | | 5 | 205 | | 7 |
|* 20 | TABLE ACCESS FULL | PROMHEAD | 100 | 2800 | | 4 |
| 21 | TABLE ACCESS FULL | PROM_MIX_MATCH_HEAD | 5 | 65 | | 2 |
|* 22 | INDEX RANGE SCAN | PROMSTORE_I2_LDS | 7 | 168 | | 5 |
| 23 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
| 24 | VIEW | VW_NSO_3 | 23 | 299 | | 2 |
|* 25 | TABLE ACCESS FULL | STORE_SEED_SCHEDULE | 23 | 276 | | 2 |
| 26 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
|* 27 | HASH JOIN SEMI | | 3 | 312 | | 32 |
| 28 | NESTED LOOPS | | 67 | 6097 | | 29 |
| 29 | NESTED LOOPS | | 3 | 201 | | 14 |
| 30 | NESTED LOOPS | | 3 | 117 | | 8 |
| 31 | NESTED LOOPS | | 3 | 66 | | 5 |
| 32 | TABLE ACCESS FULL | PROM_THRESHOLD_DETAIL | 3 | 39 | | 2 |
| 33 | TABLE ACCESS BY INDEX ROWID| PROM_THRESHOLD_HEAD | 1 | 9 | | 1 |
|* 34 | INDEX UNIQUE SCAN | PK_PROM_THRESHOLD_HEAD | 1 | | | |
|* 35 | INDEX RANGE SCAN | PK_PROM_THRESHOLD_SKU | 1 | 17 | | 1 |
|* 36 | TABLE ACCESS BY INDEX ROWID | PROMHEAD | 1 | 28 | | 2 |
|* 37 | INDEX RANGE SCAN | PROMHEAD_I1 | 1 | | | 1 |
|* 38 | INDEX RANGE SCAN | PROMSTORE_I2_LDS | 22 | 528 | | 5 |
| 39 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
| 40 | VIEW | VW_NSO_2 | 23 | 299 | | 2 |
|* 41 | TABLE ACCESS FULL | STORE_SEED_SCHEDULE | 23 | 276 | | 2 |
| 42 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
|* 43 | HASH JOIN | | 11 | 1243 | 2816K| 1620 |
|* 44 | HASH JOIN | | 29050 | 2468K| | 623 |
|* 45 | TABLE ACCESS FULL | CLEAR_SUSP_HEAD | 101 | 2727 | | 2 |
|* 46 | HASH JOIN | | 1842K| 105M| | 604 |
|* 47 | HASH JOIN | | 1394 | 34850 | | 15 |
| 48 | VIEW | VW_NSO_1 | 23 | 299 | | 2 |
| 49 | SORT UNIQUE | | 23 | 276 | | |
|* 50 | TABLE ACCESS FULL | STORE_SEED_SCHEDULE | 23 | 276 | | 2 |
| 51 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
| 52 | TABLE ACCESS FULL | PRICE_ZONE_GROUP_STORE | 28310 | 331K| | 9 |
| 53 | TABLE ACCESS FULL | CLEAR_SUSP_DETAIL | 749K| 25M| | 558 |
|* 54 | TABLE ACCESS FULL | CLEAR_RESET_CALC | 494K| 12M| | 667 |
| 55 | TABLE ACCESS FULL | PERIOD | 1 | 8 | | 2 |
|* 56 | HASH JOIN | | 2122K| 70M| 37M| 13264 |
| 57 | INDEX FAST FULL SCAN | ITEM_MASTER_I8_LDS | 1074K| 25M| | 739 |
|* 58 | TABLE ACCESS FULL | POS_MODS | 2122K| 20M| | 11211 |
-------------------------------------------------------------------------------------------------------
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|