| write as a single statement [message #436178] |
Tue, 22 December 2009 00:52  |
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 #437302 is a reply to message #436178] |
Sun, 03 January 2010 09:46  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
