Home » RDBMS Server » Performance Tuning » write as a single statement (Databse 10.1,xp)
write as a single statement [message #436178] Tue, 22 December 2009 00:52 Go to next message
krajasekhar.v
Messages: 36
Registered: May 2007
Location: bangalore
Member
Hi,
I need to help write as single insert statement instead of insert and update or give me new idea to approach since this update statement taking more time.

1) first i'm inserting data into table like below.
INSERT INTO SS_ITEM_STAT_OPN(
        REP_ID, ITEM_ID, REPORT_COLUMN_DISPLAY, REPORT_COLUMN_SEQ_NO, BEGIN_VALUE, END_VALUE)
        SELECT 
        LN_SEQ_REP_ID, ITEM_ID, REPORT_COLUMN_DISPLAY, REPORT_COLUMN_SEQ_NO, BEGIN_VALUE, END_VALUE
        FROM VW_SCALE_SCORE_CONFIG CROSS JOIN REP_ITEM_STAT_OPN 
        WHERE REP_ID = LN_SEQ_REP_ID;


and i'm updating same table using for loop,for loop purpose i'm using below statement
SELECT ITEM_ID 
        BULK COLLECT INTO TV_REP_ITEM_STAT_OPN
        FROM REP_ITEM_STAT_OPN
        WHERE REP_ID = LN_SEQ_REP_ID;


and i'm updating like below.
FOR LN_SSI_OPN IN NVL(TV_SS_ITEM_STAT_OPN.FIRST,1)..NVL(TV_SS_ITEM_STAT_OPN.LAST,0)
        LOOP
            UPDATE SS_ITEM_STAT_OPN SET 
            TOT_STUD_ITEM_SS             =     NVL((SELECT COUNT(*) 
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B
                                            WHERE    SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                                    AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                            AND     SS_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID 
                                            AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                            AND ROUND(A.SCALE_SCORE) > BEGIN_VALUE
                                            AND ROUND(A.SCALE_SCORE) <= END_VALUE
                                            AND CONDITION_COLUMN= 'SCALE_SCORE'
                                            GROUP BY ITEM_ID,END_VALUE),0),
            TOT_STUD_CORR_ANS_ITEM_SS     =     NVL((SELECT COUNT(*) 
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B
                                            WHERE SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                                    AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                            AND  SS_ITEM_STAT_OPN.ITEM_ID = A.ITEM_ID 
                                            AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                            AND ROUND(A.SCALE_SCORE) > BEGIN_VALUE 
                                            AND ROUND(A.SCALE_SCORE) <= END_VALUE
                                            AND CONDITION_COLUMN= 'SCALE_SCORE'
                                            AND RESPONSE_IS_CORRECT = 1
                                            GROUP BY ITEM_ID,END_VALUE),0),
            TOT_STUD_SS                 =  NVL((SELECT COUNT(*) FROM 
                                            (SELECT A.TEST_SESSION_DETAIL_ID
                                            FROM PSYCHOMETRIC_ITEM_STAT_OPN A, VW_SCALE_SCORE_CONFIG B,SS_ITEM_STAT_OPN
                                            WHERE SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
                                            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                                            AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE)
                                            AND SS_ITEM_STAT_OPN.END_VALUE = B.END_VALUE
                                            AND ROUND(A.SCALE_SCORE) >B.BEGIN_VALUE
                                            AND ROUND(A.SCALE_SCORE) <= B.END_VALUE
                                            AND CONDITION_COLUMN= 'SCALE_SCORE'
                                            GROUP BY A.TEST_SESSION_DETAIL_ID),VW_SCALE_SCORE_CONFIG
                                            WHERE END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE
                                            GROUP BY END_VALUE),0)
            WHERE    REP_ID = LN_SEQ_REP_ID
            AND     SS_ITEM_STAT_OPN.ITEM_ID=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).ITEM_ID
            AND     (SS_ITEM_STAT_OPN.BEGIN_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).BEGIN_VALUE
                    AND    SS_ITEM_STAT_OPN.END_VALUE=TV_SS_ITEM_STAT_OPN(LN_SSI_OPN).END_VALUE);
            COMMIT;

can some one help to give idea .
Re: write as a single statement [message #436183 is a reply to message #436178] Tue, 22 December 2009 01:02 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>can some one help to give idea .
post EXPLAIN_PLAN
Re: write as a single statement [message #437302 is a reply to message #436178] Sun, 03 January 2010 09:46 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Look at the MERGE statement.

This is better for this scenario to using UPDATE as it gives you the ability to provide a properly tuned subquery containing all the rows you want to update, rather than UPDATE which makes you use correlated subqueries and is generally unpleasant to read and write.
Previous Topic: Current snap shot level?
Next Topic: Instance profiling
Goto Forum:
  


Current Time: Sat Oct 01 04:08:11 CDT 2016

Total time taken to generate the page: 0.05496 seconds