This discussion is archived
5 Replies Latest reply: Sep 30, 2013 12:46 AM by Principiante RSS

I need to optimize a MATERIALIZED VIEW

Principiante Newbie
Currently Being Moderated

Hello,

I have a problem with a MATERIALIZED VIEW, I need to optimize it.

This query takes 4 hours, should decrease this time

Is there anyone who can help me?

 

I attach script MATERIALIZED VIEW

 

 

 

CREATE MATERIALIZED VIEW "QTSA_CONTRACT_CONDITION"

 

  ORGANIZATION HEAP PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS FOR OLTP NOLOGGING STORAGE

  ( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "LTR_ODS_DT"

  PARTITION BY HASH ("CONTRACT_ITEM_ID")

  (PARTITION "SYS_P55162" TABLESPACE "XXXX" COMPRESS FOR OLTP ,

  PARTITION "SYS_P55163" TABLESPACE "XXXX" COMPRESS FOR OLTP ,

   PARTITION "SYS_P55164" TABLESPACE "XXXX" COMPRESS FOR OLTP ,

   PARTITION "SYS_P55165" TABLESPACE "XXXX" COMPRESS FOR OLTP )

  

   PARALLEL 2 BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND

   USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE

  

AS

  SELECT cc.* ,

    greatest( NVL(cc.list_price_value,0) ,

              NVL(cc.bc_vehicle_price_value,0) ,

              NVL(cc.ec_vehicle_price_value,0) )           

          AS unique_list_price_value ,

    greatest( NVL(cc.opt_tot_list_price_value,0) ,

              NVL(cc.opt_tot_list_price_value,0) ,

              NVL(cc.ec_optional_price_value,0) )

          AS unique_opt_list_price_value

  FROM

    (

   

   SELECT

      /*+ PARALLEL(A 4) PARALLEL(B 4) */

      A.CONTRACT_ID ,

      CASE

        WHEN GROUPING(A.POSITION_ID) = 1

        THEN '?'

        ELSE A.POSITION_ID

      END AS CONTRACT_ITEM_ID,

      -- Financial Value - 41X1    ,

      SUM(

      CASE

        WHEN B.CONDITION_TYPE_FK = '41X1'

        THEN B.TOTAL_AMT/10

        ELSE 0

      END) AS FINANCIAL_VALUE_PERCENTAGE ,

      SUM(

      CASE

        WHEN B.CONDITION_TYPE_FK = '41X1'

        THEN B.VALUE_CONDITION_AMT

        ELSE 0

      END) AS FINANCIAL_VALUE_AMOUNT ,

      MAX(

      CASE

        WHEN B.CONDITION_TYPE_FK = '41X1'

        THEN B.CURRENCY

        ELSE NULL

      END) AS FINANCIAL_VALUE_CURRENCY,

      -- CONT Total Instalment - ZIN4    ,

      SUM(

      CASE

        WHEN B.CONDITION_TYPE_FK = 'ZIN4'

        THEN B.VALUE_CONDITION_AMT

        ELSE 0

      END) AS INSTALLMENT_AMOUNT ,

      MAX(

      CASE

        WHEN B.CONDITION_TYPE_FK = 'ZIN4'

        THEN B.CURRENCY

        ELSE NULL

      END) AS INSTALLMENT_CURRENCY,

      --    CONT: Last Instllmnt - 41NT,

      SUM(

      CASE

        WHEN B.CONDITION_TYPE_FK IN ('41NT','ZIV3')

        THEN B.VALUE_CONDITION_AMT

        ELSE 0

      END) AS LAST_INSTALLMENT_AMOUNT ,

    FROM QTST_CONTRACT_PRICE_COND A

    INNER JOIN

      (SELECT

        /*+ PARALLEL(B 4) */

        b.* ,

        row_number() over (partition BY price_condition_id, position_id, condition_type_fk order by condition_counter_id DESC) rn

      FROM COPE_ORDER_CONDITION B

      WHERE B.CONDITION_TYPE_FK IN ('41X1', 'ZIN4', '41NT','ZIV3', 'ZK15','ZK05', 'ZK03', 'ZK04', 'ZK02', 'ZK00', 'ZK01', 'ZIC7', 'ZIC8', 'ZI62', 'ZID7', 'ZID8', 'ZIB1', 'ZIO5', 'ZIO6', 'ZIO7', 'ZID2','ZIT0', '41Y1', 'ZI92', '41N4', 'ZI37', 'ZID1', 'ZI38', 'ZI39', 'ZI14', 'ZID0', 'ZK06', 'ZIG9', 'ZIH0', 'ZIH1', 'ZIHC', 'ZIH4', 'ZI01', 'ZI0O', 'ZI05', 'ZIT6', 'ZIE0', 'ZIG8', 'ZIL0', 'ZIJ7', 'ZIA9', 'ZI35', 'ZI36', 'ZIN9','ZI40', 'ZI54', 'ZI55', 'ZI57', 'ZIA7', 'ZI56', 'ZI19', 'ZI18', 'ZI58', 'ZIA8', 'ZII9', 'ZIA6', '41N1', 'ZIT5', 'ZIT6', '28EP', '28ED', 'ZI14', 'ZI15', 'ZI22', 'ZI23', 'ZI24', 'ZI25', 'ZI26', 'ZI27', 'ZI28', 'ZI29', 'ZI30', 'ZI31', 'ZI32', 'ZI33', 'ZIG6', 'ZII8', 'ZIP2', 'ZI50', 'ZI52', 'ZIF3', 'ZIE7', 'ZII4', 'ZIP3', 'ZIJ8', 'ZIU0', 'ZI65', 'ZIJ3', 'ZI53', 'ZI83', 'ZI93', 'MWST', 'ZIH8', 'ZID5', 'ZID9', 'ZIL8', 'ZIP0', 'ZIP1', 'ZI16', 'ZI17', 'ZI21', 'ZI60', 'ZIB2', 'ZIO8', 'ZIV5', 'ZI09', 'ZIR1' )

      ) B

    ON (A.PRICE_CONDITION_ID   = B.PRICE_CONDITION_ID

    AND A.POSITION_ID          = B.POSITION_ID

    AND A.LEVEL_ID             = B.LEVEL_ID

    AND A.CONDITION_COUNTER_ID = B.CONDITION_COUNTER_ID

    AND B.RN                   = 1 )   

    GROUP BY A.CONTRACT_ID,

      rollup(A.POSITION_ID)

    ) cc ;

  • 1. Re: I need to optimize a MATERIALIZED VIEW
    Peter vd Zwan Expert
    Currently Being Moderated

    Hi,

     

    Not much info you give us.

    No tables structures, oracle version etc etc.

    See HOW TO: Post a SQL statement tuning request - template posting

    I would start by changing this:

     

          END) AS LAST_INSTALLMENT_AMOUNT ,

        FROM QTST_CONTRACT_PRICE_COND A

    into this:

          END) AS LAST_INSTALLMENT_AMOUNT --, take out the comma

        FROM QTST_CONTRACT_PRICE_COND A

    Then I would make a table and index for the items in the in list:

    ('41X1', 'ZIN4', '41NT','ZIV3', 'ZK15','ZK05',

            'ZK03', 'ZK04', 'ZK02', 'ZK00', 'ZK01', 'ZIC7', 'ZIC8', 'ZI62', 'ZID7', 'ZID8', 'ZIB1', 'ZIO5', 'ZIO6', 'ZIO7', 'ZID2','ZIT0', '41Y1', 'ZI92', '41N4', 'ZI37', 'ZID1', 'ZI38', 'ZI39', 'ZI14', 'ZID0', 'ZK06', 'ZIG9', 'ZIH0', 'ZIH1',

            'ZIHC', 'ZIH4', 'ZI01', 'ZI0O', 'ZI05', 'ZIT6', 'ZIE0', 'ZIG8', 'ZIL0', 'ZIJ7', 'ZIA9', 'ZI35', 'ZI36', 'ZIN9','ZI40', 'ZI54', 'ZI55', 'ZI57', 'ZIA7', 'ZI56', 'ZI19', 'ZI18', 'ZI58', 'ZIA8', 'ZII9', 'ZIA6', '41N1', 'ZIT5', 'ZIT6',

            '28EP', '28ED', 'ZI14', 'ZI15', 'ZI22', 'ZI23', 'ZI24', 'ZI25', 'ZI26', 'ZI27', 'ZI28', 'ZI29', 'ZI30', 'ZI31', 'ZI32', 'ZI33', 'ZIG6', 'ZII8', 'ZIP2', 'ZI50', 'ZI52', 'ZIF3', 'ZIE7', 'ZII4', 'ZIP3', 'ZIJ8', 'ZIU0', 'ZI65', 'ZIJ3',

            'ZI53', 'ZI83', 'ZI93', 'MWST', 'ZIH8', 'ZID5', 'ZID9', 'ZIL8', 'ZIP0', 'ZIP1', 'ZI16', 'ZI17', 'ZI21', 'ZI60', 'ZIB2', 'ZIO8', 'ZIV5', 'ZI09', 'ZIR1' )

    And list this table to COPE_ORDER_CONDITION table.

     

    Then when you send us the info needed and shown in the link above, maybe we can help more.

     

    Regards,

     

    Peter

  • 2. Re: I need to optimize a MATERIALIZED VIEW
    Principiante Newbie
    Currently Being Moderated

    Hi Peter,

     

    here is some information that you asked me


     

    SQL> show parameter optimizer

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    optimizer_capture_sql_plan_baselines boolean     FALSE

    optimizer_dynamic_sampling           integer     2

    optimizer_features_enable            string      11.2.0.3

    optimizer_index_caching              integer     0

    optimizer_index_cost_adj             integer     100

    optimizer_mode                       string      ALL_ROWS

    optimizer_secure_view_merging        boolean     TRUE

    optimizer_use_invisible_indexes      boolean     FALSE

    optimizer_use_pending_statistics     boolean     FALSE

    optimizer_use_sql_plan_baselines     boolean     TRUE

    SQL>

    SQL>

    SQL> show parameter db_file_multi

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    db_file_multiblock_read_count        integer     128

    SQL>

    SQL>

    SQL> show parameter db_block_size

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ------------------------------

    db_block_size                        integer     16384

     

    SQL> show parameter cursor_sharing

     

    NAME                                 TYPE        VALUE

    ------------------------------------ ----------- ----------------------------

    cursor_sharing                       string      EXACT

     

     

     

     

    Plan

    SELECT STATEMENT  ALL_ROWSCost : 10.770 Bytes : 8.133.477.312 Cardinality : 520.976                                                                   

        22 PX COORDINATOR                                                                 

            21 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10004 Cost : 10.770 Bytes : 8.133.477.312 Cardinality : 520.976                                                           

                20 VIEW PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D. Cost : 10.770 Bytes : 8.133.477.312 Cardinality : 520.976                                                       

                    19 SORT GROUP BY ROLLUP PARALLEL_COMBINED_WITH_PARENT Cost : 10.770 Bytes : 197.970.880 Cardinality : 520.976                                                   

                        18 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 3.435 Bytes : 197.970.880 Cardinality : 520.976                                               

                            17 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10003 Cost : 3.435 Bytes : 197.970.880 Cardinality : 520.976                                           

                                16 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT "A"."PRICE_CONDITION_ID"="B"."PRICE_CONDITION_ID" AND "A"."POSITION_ID"="B"."POSITION_ID" AND "A"."LEVEL_ID"="B"."LEVEL_ID" AND "A"."CONDITION_COUNTER_ID"="B"."CONDITION_COUNTER_ID"Cost : 3.435 Bytes : 197.970.880 Cardinality : 520.976                                       

                                    10 JOIN FILTER CREATE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0000 Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                                   

                                        9 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                               

                                            8 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                           

                                                7 VIEW PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D. "B"."RN"=1Cost : 1.905 Bytes : 139.024.038 Cardinality : 507.387                       

                                                    6 WINDOW SORT PUSHED RANK PARALLEL_COMBINED_WITH_PARENT ROW_NUMBER() OVER ( PARTITION BY NLSSORT("PRICE_CONDITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("POSITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("CONDITION_TYPE_FK",'nls_sort=''WEST_EUROPEAN''') ORDER BY INTERNAL_FUNCTION("CONDITION_COUNTER_ID") DESC )<=1Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387                   

                                                        5 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387               

                                                            4 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10000 Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387           

                                                                3 WINDOW CHILD PUSHED RANK PARALLEL_COMBINED_WITH_PARENT ROW_NUMBER() OVER ( PARTITION BY NLSSORT("PRICE_CONDITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("POSITION_ID",'nls_sort=''WEST_EUROPEAN'''),NLSSORT("CONDITION_TYPE_FK",'nls_sort=''WEST_EUROPEAN''') ORDER BY INTERNAL_FUNCTION("CONDITION_COUNTER_ID") DESC )<=1Cost : 1.905 Bytes : 48.201.765 Cardinality : 507.387       

                                                                    2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost : 1.893 Bytes : 48.201.765 Cardinality : 507.387Partition # : 16 Partitions accessed #1 - #4   

                                                                        1 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D.COPE_ORDER_CONDITION "B"."CONDITION_TYPE_FK"='28ED' OR "B"."CONDITION_TYPE_FK"='28EP' OR "B"."CONDITION_TYPE_FK"='41N1' OR "B"."CONDITION_TYPE_FK"='41N4' OR "B"."CONDITION_TYPE_FK"='41NT' OR "B"."CONDITION_TYPE_FK"='41X1' OR "B"."CONDITION_TYPE_FK"='41Y1' OR "B"."CONDITION_TYPE_FK"='MWST' OR "B"."CONDITION_TYPE_FK"='ZI01' OR "B"."CONDITION_TYPE_FK"='ZI05' OR "B"."CONDITION_TYPE_FK"='ZI09' OR "B"."CONDITION_TYPE_FK"='ZI0O' OR "B"."CONDITION_TYPE_FK"='ZI14' OR "B"."CONDITION_TYPE_FK"='ZI15' OR "B"."CONDITION_TYPE_FK"='ZI16' OR "B"."CONDITION_TYPE_FK"='ZI17' OR "B"."CONDITION_TYPE_FK"='ZI18' OR "B"."CONDITION_TYPE_FK"='ZI19' OR "B"."CONDITION_TYPE_FK"='ZI21' OR "B"."CONDITION_TYPE_FK"='ZI22' OR "B"."CONDITION_TYPE_FK"='ZI23' OR "B"."CONDITION_TYPE_FK"='ZI24' OR "B"."CONDITION_TYPE_FK"='ZI25' OR "B"."CONDITION_TYPE_FK"='ZI26' OR "B"."CONDITION_TYPE_FK"='ZI27' OR "B"."CONDITION_TYPE_FK"='ZI28' OR "B"."CONDITION_TYPE_FK"='ZI29' OR "B"."CONDITION_TYPE_FK"='ZI30' OR "B"."CONDITION_TYPE_FK"='ZI31' OR "B"."CONDITION_TYPE_FK"='ZI32' OR "B"."CONDITION_TYPE_FK"='ZI33' OR "B"."CONDITION_TYPE_FK"='ZI35' OR "B"."CONDITION_TYPE_FK"='ZI36' OR "B"."CONDITION_TYPE_FK"='ZI37' OR "B"."CONDITION_TYPE_FK"='ZI38' OR "B"."CONDITION_TYPE_FK"='ZI39' OR "B"."CONDITION_TYPE_FK"='ZI40' OR "B"."CONDITION_TYPE_FK"='ZI50' OR "B"."CONDITION_TYPE_FK"='ZI52' OR "B"."CONDITION_TYPE_FK"='ZI53' OR "B"."CONDITION_TYPE_FK"='ZI54' OR "B"."CONDITION_TYPE_FK"='ZI55' OR "B"."CONDITION_TYPE_FK"='ZI56' OR "B"."CONDITION_TYPE_FK"='ZI57' OR "B"."CONDITION_TYPE_FK"='ZI58' OR "B"."CONDITION_TYPE_FK"='ZI60' OR "B"."CONDITION_TYPE_FK"='ZI62' OR "B"."CONDITION_TYPE_FK"='ZI65' OR "B"."CONDITION_TYPE_FK"='ZI83' OR "B"."CONDITION_TYPE_FK"='ZI92' OR "B"."CONDITION_TYPE_FK"='ZI93' OR "B"."CONDITION_TYPE_FK"='ZIA6' OR "B"."CONDITION_TYPE_FK"='ZIA7' OR "B"."CONDITION_TYPE_FK"='ZIA8' OR "B"."CONDITION_TYPE_FK"='ZIA9' OR "B"."CONDITION_TYPE_FK"='ZIB1' OR "B"."CONDITION_TYPE_FK"='ZIB2' OR "B"."CONDITION_TYPE_FK"='ZIC7' OR "B"."CONDITION_TYPE_FK"='ZIC8' OR "B"."CONDITION_TYPE_FK"='ZID0' OR "B"."CONDITION_TYPE_FK"='ZID1' OR "B"."CONDITION_TYPE_FK"='ZID2' OR "B"."CONDITION_TYPE_FK"='ZID5' OR "B"."CONDITION_TYPE_FK"='ZID7' OR "B"."CONDITION_TYPE_FK"='ZID8' OR "B"."CONDITION_TYPE_FK"='ZID9' OR "B"."CONDITION_TYPE_FK"='ZIE0' OR "B"."CONDITION_TYPE_FK"='ZIE7' OR "B"."CONDITION_TYPE_FK"='ZIF3' OR "B"."CONDITION_TYPE_FK"='ZIG6' OR "B"."CONDITION_TYPE_FK"='ZIG8' OR "B"."CONDITION_TYPE_FK"='ZIG9' OR "B"."CONDITION_TYPE_FK"='ZIH0' OR "B"."CONDITION_TYPE_FK"='ZIH1' OR "B"."CONDITION_TYPE_FK"='ZIH4' OR "B"."CONDITION_TYPE_FK"='ZIH8' OR "B"."CONDITION_TYPE_FK"='ZIHC' OR "B"."CONDITION_TYPE_FK"='ZII4' OR "B"."CONDITION_TYPE_FK"='ZII8' OR "B"."CONDITION_TYPE_FK"='ZII9' OR "B"."CONDITION_TYPE_FK"='ZIJ3' OR "B"."CONDITION_TYPE_FK"='ZIJ7' OR "B"."CONDITION_TYPE_FK"='ZIJ8' OR "B"."CONDITION_TYPE_FK"='ZIL0' OR "B"."CONDITION_TYPE_FK"='ZIL8' OR "B"."CONDITION_TYPE_FK"='ZIN4' OR "B"."CONDITION_TYPE_FK"='ZIN9' OR "B"."CONDITION_TYPE_FK"='ZIO5' OR "B"."CONDITION_TYPE_FK"='ZIO6' OR "B"."CONDITION_TYPE_FK"='ZIO7' OR "B"."CONDITION_TYPE_FK"='ZIO8' OR "B"."CONDITION_TYPE_FK"='ZIP0' OR "B"."CONDITION_TYPE_FK"='ZIP1' OR "B"."CONDITION_TYPE_FK"='ZIP2' OR "B"."CONDITION_TYPE_FK"='ZIP3' OR "B"."CONDITION_TYPE_FK"='ZIR1' OR "B"."CONDITION_TYPE_FK"='ZIT0' OR "B"."CONDITION_TYPE_FK"='ZIT5' OR "B"."CONDITION_TYPE_FK"='ZIT6' OR "B"."CONDITION_TYPE_FK"='ZIU0' OR "B"."CONDITION_TYPE_FK"='ZIV3' OR "B"."CONDITION_TYPE_FK"='ZIV5' OR "B"."CONDITION_TYPE_FK"='ZK00' OR "B"."CONDITION_TYPE_FK"='ZK01' OR "B"."CONDITION_TYPE_FK"='ZK02' OR "B"."CONDITION_TYPE_FK"='ZK03' OR "B"."CONDITION_TYPE_FK"='ZK04' OR "B"."CONDITION_TYPE_FK"='ZK05' OR "B"."CONDITION_TYPE_FK"='ZK06' OR "B"."CONDITION_TYPE_FK"='ZK15'Cost : 1.893 Bytes : 48.201.765 Cardinality : 507.387Partition # : 16 Partitions accessed #1 - #1048575

                                    15 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710                                   

                                        14 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10002 Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710                               

                                            13 JOIN FILTER USE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0000 Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710                           

                                                12 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710Partition # : 21 Partitions accessed #1 - #4                       

                                                    11 TABLE ACCESS FULL PARALLEL_COMBINED_WITH_PARENT LTR_ODS_D.QTST_CONTRACT_PRICE_COND SYS_OP_BLOOM_FILTER(:BF0000,"A"."PRICE_CONDITION_ID","A"."POSITION_ID","A"."LEVEL_ID","A"."CONDITION_COUNTER_ID")Cost : 1.525 Bytes : 165.435.260 Cardinality : 1.560.710Partition # : 21 Partitions accessed #1 - #4                   

  • 3. Re: I need to optimize a MATERIALIZED VIEW
    Principiante Newbie
    Currently Being Moderated

    Any suggestions?

  • 4. Re: I need to optimize a MATERIALIZED VIEW
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    I would start by adding the actual rowcount and autotrace dump to the thread. Also, if your license allows it (i.e. it includes the Diagnostic and Tuning Pack), paste SQL real-time monitor output (the great thing about it is that you don't have to wait until the entire query completes, you can let it run for 10 minutes and then take the report, it would still contain useful information).

     

    After you post all this (and give us something to work with) trace the query. Be sure to do it in such a way that all diagnostic information is included into the trace file, i.e.:

     

    1) enable waits (dbms_monitor.trace_session_enable(waits=>true, binds=>false) or ALTER SESSION SET EVENTS '10046 trace name context forever, level 8')

    2) close the cursor before finishing tracing:

    - enable trace

    - run statement

    - exit

    that way you make sure that plan statistics will be in the trace file.

     

     

    Process the resulting trace file with tkprof and post the output here.

     

    Best regards,

      Nikolay

  • 5. Re: I need to optimize a MATERIALIZED VIEW
    Principiante Newbie
    Currently Being Moderated

    I solved it by changing the select containing constant values.


    I followed the advice of Peter:

     
    Before:
    SELECT
    /*+ PARALLEL(B 4) */
    b.* ,
    row_number() over (partition BY price_condition_id, position_id, condition_type_fk order by condition_counter_id DESC) rn
    FROM COPE_ORDER_CONDITION B
    WHERE B.CONDITION_TYPE_FK IN ('41X1', 'ZIN4', '41NT','ZIV3', 'ZK15','ZK05', 'ZK03', 'ZK04', 'ZK02', 'ZK00', 'ZK01', 'ZIC7', 'ZIC8', 'ZI62', 'ZID7', 'ZID8', 'ZIB1', 'ZIO5', 'ZIO6', 'ZIO7', 'ZID2','ZIT0', '41Y1', 'ZI92', '41N4', 'ZI37', 'ZID1', 'ZI38', 'ZI39', 'ZI14', 'ZID0', 'ZK06', 'ZIG9', 'ZIH0', 'ZIH1', 'ZIHC', 'ZIH4', 'ZI01', 'ZI0O', 'ZI05', 'ZIT6', 'ZIE0', 'ZIG8', 'ZIL0', 'ZIJ7', 'ZIA9', 'ZI35', 'ZI36', 'ZIN9','ZI40', 'ZI54', 'ZI55', 'ZI57', 'ZIA7', 'ZI56', 'ZI19', 'ZI18', 'ZI58', 'ZIA8', 'ZII9', 'ZIA6', '41N1', 'ZIT5', 'ZIT6', '28EP', '28ED', 'ZI14', 'ZI15', 'ZI22', 'ZI23', 'ZI24', 'ZI25', 'ZI26', 'ZI27', 'ZI28', 'ZI29', 'ZI30', 'ZI31', 'ZI32', 'ZI33', 'ZIG6', 'ZII8', 'ZIP2', 'ZI50', 'ZI52', 'ZIF3', 'ZIE7', 'ZII4', 'ZIP3', 'ZIJ8', 'ZIU0', 'ZI65', 'ZIJ3', 'ZI53', 'ZI83', 'ZI93', 'MWST', 'ZIH8', 'ZID5', 'ZID9', 'ZIL8', 'ZIP0', 'ZIP1', 'ZI16', 'ZI17', 'ZI21', 'ZI60', 'ZIB2', 'ZIO8', 'ZIV5', 'ZI09', 'ZIR1' )
     
    After:
    I made a table and index for the items in the in list:
     
    SELECT
    /*+ PARALLEL(B 4) PARALLEL(T 4) */
    b.* ,
    row_number() over (partition BY b.price_condition_id, b.position_id, b.condition_type_fk order by b.condition_counter_id DESC) rn
    FROM COPE_ORDER_CONDITION B, COPE_CONDITION_TYPE T
    WHERE B.CONDITION_TYPE_FK = T.CONDITION_TYPE_FK

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points