DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: materialized view takes hours

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    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)

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    crystal ball broken today, how about a query plan?
    Jeff Hunter

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    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
  •  


Click Here to Expand Forum to Full Width