Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unanswered: Query optimzation

    ---Compare data for ALL accounts for a given date in
    -- PR_CONV_FACT table with CJO_ACTION_TXN and CJO_ACTION_TXN_ITEM tables.



    procedure testFactEqualsCJOItemAll (p_date1 IN DATE, p_date2 IN DATE)
    IS
    v_test_name VARCHAR2(50) := ' testFactEqualsCJOItemAll';
    v_err VARCHAR2(500);

    v_sale$_catitem_amount_usd NUMBER;
    v_sale_amount_adv_curr NUMBER;
    v_sale_amount_pub_curr NUMBER;
    v_comm$_catitem NUMBER;
    v_pub_comm_amount NUMBER;
    v_catitems_items NUMBER;
    v_cjo_date DATE;
    v_sale$_cat NUMBER;
    v_comm$_cat NUMBER;
    v_cj_fee_cat NUMBER;
    v_cat_num_actions NUMBER;
    v_fact_sale_amount NUMBER;
    v_fact_sale_adv_amount NUMBER;
    v_fact_sale_pub_amount NUMBER;
    v_fact_comm_amount NUMBER;
    v_fact_comm_adv_amount NUMBER;
    v_fact_comm_pub_amount NUMBER;
    v_fact_fee_amount NUMBER;
    v_fact_fee_adv_amount NUMBER;
    v_fact_item_quantity NUMBER;
    v_fact_sales NUMBER;



    v_started DATE := SYSDATE;
    BEGIN
    v_test_name := 'testFactEqualsCJOItemAll';




    SELECT --+ parallel (cat_item,4) parallel (cat, 4)
    SUM(quantity*cat_item.sale_amount_usd) as sale_amount_usd,
    SUM(quantity*cat_item.sale_amount_adv_curr) as sale_amount_adv_curr,
    SUM(quantity* cat_item.sale_amount_pub_curr) as sale_amount_pub_curr,
    SUM(SIGN(quantity)*cat_item.pub_comm_usd) as comm$_catitem,
    SUM(SIGN(quantity)* cat_item.pub_comm_pub_curr) as pub_comm_amount,
    SUM(cat_item.quantity) as catitem_items
    INTO
    v_sale$_catitem_amount_usd, v_sale_amount_adv_curr, v_sale_amount_pub_curr, v_comm$_catitem, v_pub_comm_amount, v_catitems_items
    FROM cjo_action_txn_item@cjo_source.cj.com cat_item, cjo_action_txn@cjo_source.cj.com cat
    WHERE 1=1
    AND cat.id = cat_item.cjo_action_txn_id
    AND trunc_event_date >= p_date1
    AND trunc_event_date < p_date2
    AND cat.posting_date <= (
    SELECT new_time(highmark,'gmt','pst') highmark_pst
    FROM dw_load_history
    WHERE id = (SELECT MAX(id) FROM dw_load_history) );






    SELECT --+ parallel (cat,4)


    SUM(num_actions) as cat_num_actions
    ,NVL(SUM(cj_fee_usd), 0) as cj_fee_usd
    INTO
    v_cat_num_actions, v_cj_fee_cat
    FROM cjo_action_txn@cjo_source.cj.com cat
    WHERE 1=1
    AND trunc_event_date >= p_date1
    AND trunc_event_date < p_date2
    AND cat.posting_date <= (
    SELECT new_time(highmark,'gmt','pst') highmark_pst
    FROM dw_load_history
    WHERE id = (SELECT MAX(id) FROM dw_load_history) )



    --

    SELECT --+ parallel (f,4)

    SUM(f.sale_amount) sale_amount
    ,SUM(f.sale_adv_amount) sale_adv_amount
    ,SUM(f.sale_pub_amount) as sale_pub_amount
    ,SUM(f.comm_amount) comm_amount
    ,SUM(f.comm_adv_amount) comm_adv_amount
    ,SUM(f.comm_pub_amount) comm_pub_amount
    ,SUM(f.fee_amount) fee_amount
    ,SUM(F.Fee_Adv_Amount) fee_adv_amount
    ,SUM(item_quantity) items
    ,SUM(event_count) sales
    INTO
    v_fact_sale_amount, v_fact_sale_adv_amount, v_fact_sale_pub_amount, v_fact_comm_amount, v_fact_comm_adv_amount,
    v_fact_comm_pub_amount, v_fact_fee_amount, v_fact_fee_adv_amount, v_fact_item_quantity, v_fact_sales
    FROM pr_conv_fact f
    WHERE day >= p_date1
    AND day < p_date2;






    --
    assertEquals(v_test_name,'items',p_date1,v_catitem s_items, v_fact_item_quantity);
    assertEquals(v_test_name,'sales',p_date1,v_cat_num _actions,v_fact_sales);
    assertEquals(v_test_name,'sale_amount',p_date1,v_s ale$_catitem_amount_usd,v_fact_sale_amount);
    assertEquals(v_test_name,'sale_adv_amount',p_date1 ,v_sale_amount_adv_curr,v_fact_sale_adv_amount);
    assertEquals(v_test_name,'sale_pub_amount',p_date1 ,v_sale_amount_pub_curr,v_fact_sale_pub_amount);
    assertEquals(v_test_name,'comm_usd',p_date1,v_comm $_catitem,v_fact_comm_amount);
    assertEquals(v_test_name,'comm_pub_amount',p_date1 ,v_pub_comm_amount,v_fact_comm_pub_amount);
    assertEquals(v_test_name,'fee_usd',p_date1, v_cj_fee_cat, v_fact_fee_amount);

    EXCEPTION WHEN OTHERS THEN
    v_err := SQLCODE || ' - ' || SQLERRM;
    INSERT INTO dw_tests VALUES( SYSTIMESTAMP, 'DW_TESTS ' || v_test_name, NULL, SYSTIMESTAMP, 'N',v_err);
    COMMIT;
    RAISE;
    END;


    /



    It takes 3-4 hours to execute the following query for one month.

    Does anybody know how to optimize this query?


    SELECT --+ parallel (cat_item,4) parallel (cat, 4)
    SUM(quantity*cat_item.sale_amount_usd) as sale_amount_usd,
    SUM(quantity*cat_item.sale_amount_adv_curr) as sale_amount_adv_curr,
    SUM(quantity* cat_item.sale_amount_pub_curr) as sale_amount_pub_curr,
    SUM(SIGN(quantity)*cat_item.pub_comm_usd) as comm$_catitem,
    SUM(SIGN(quantity)* cat_item.pub_comm_pub_curr) as pub_comm_amount,
    SUM(cat_item.quantity) as catitem_items
    INTO
    v_sale$_catitem_amount_usd, v_sale_amount_adv_curr, v_sale_amount_pub_curr, v_comm$_catitem, v_pub_comm_amount, v_catitems_items
    FROM cjo_action_txn_item@cjo_source.cj.com cat_item, cjo_action_txn@cjo_source.cj.com cat
    WHERE 1=1
    AND cat.id = cat_item.cjo_action_txn_id
    AND trunc_event_date >= p_date1
    AND trunc_event_date < p_date2
    AND cat.posting_date <= (
    SELECT new_time(highmark,'gmt','pst') highmark_pst
    FROM dw_load_history
    WHERE id = (SELECT MAX(id) FROM dw_load_history) );

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Create a view in @cjo_source.cj.com with that (or similar) query to do the bulk computations at the source db.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I would setup a local materilized view that contains the already summed information and use the MVIEW in the query.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •