Home » SQL & PL/SQL » SQL & PL/SQL » Update Showing different results?? (XP,Oracle 10g)
Update Showing different results?? [message #313798] Mon, 14 April 2008 10:36 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member


/*Notice: Formatted SQL is not the same as input*/

CREATE OR REPLACE PROCEDURE PR_BMS_UPDATE AS
CURSOR BMS_CUR IS
SELECT /*+ paralle(BASE_LIVE_SITES_1,5,1) */SITE_NO, CAS_SEGMENT
FROM BASE_LIVE_SITES_1
WHERE MAIN_SITE = 'Y';
--and rownum<=10000;

TYPE SITE_TAB IS TABLE OF BASE_LIVE_SITES_1.SITE_NO%TYPE;
V_SITE_NO SITE_TAB;
TYPE CAS_TAB IS TABLE OF BASE_LIVE_SITES_1.CAS_SEGMENT%TYPE;
V_CAS CAS_TAB;
LN_UPDATED_RECORDS NUMBER := 0;
rows_affected varchar2(40);
BEGIN
OPEN BMS_CUR;
LOOP
FETCH BMS_CUR BULK COLLECT
INTO V_SITE_NO, V_CAS LIMIT 10000;

FORALL I IN 1 .. V_SITE_NO.COUNT

UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = V_CAS(I)
WHERE A.SITE_NO = V_SITE_NO(I);

--COMMIT;
LN_UPDATED_RECORDS := LN_UPDATED_RECORDS + V_SITE_NO.COUNT;


EXIT WHEN BMS_CUR%NOTFOUND;
END LOOP;
rows_affected:= to_char(sql%rowcount);

if sql%rowcount>=0 then
DBMS_OUTPUT.PUT_LINE('TOTAL' || LN_UPDATED_RECORDS || '' ||''||
'ROWS UPDATED');
DBMS_OUTPUT.PUT_LINE('TOTAL' || rows_affected || '' ||''||
'ROWS UPDATED');
end if;

COMMIT;
END PR_BMS_UPDATE;


SQL> exec PR_BMS_UPDATE;

TOTAL 961793 ROWS UPDATED
TOTAL 1793 ROWS UPDATED

PL/SQL procedure successfully completed.

Elapsed: 00:00:42.36

2nd Program

here i create a temp table of the same query as of the cursor in the 1st program
create table base_live_temp
as
SELECT /*+ paralle(BASE_LIVE_SITES_1,5,1) */SITE_NO, CAS_SEGMENT
FROM BASE_LIVE_SITES_1
WHERE MAIN_SITE = 'Y';



CREATE OR REPLACE PROCEDURE PR_BMS_UPDATE_TEMP AS
rows_affected varchar2(40);
BEGIN

UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = (select /*+ paralle(b,5,1) */
b.CAS_SEGMENT
from base_live_temp b
WHERE A.SITE_NO = b.site_no);


rows_affected:= to_char(sql%rowcount);

if sql%rowcount >= 0 then
DBMS_OUTPUT.PUT_LINE('TOTAL' ||''|| rows_affected || '' || '' ||
'ROWS UPDATED');
end if;
commit;

END PR_BMS_UPDATE_TEMP;

Elapsed: 00:00:44.47
SQL> exec PR_BMS_UPDATE_TEMP;
TOTAL 961789 ROWS UPDATED


Please suggest,How can this be different??
Re: Update Showing different results?? [message #313800 is a reply to message #313798] Mon, 14 April 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not formatted... as always.

What don't you understand in OraFAQ Forum Guide, "How to format your post?" section and usage of code tags?

Regards
Michel


Re: Update Showing different results?? [message #313801 is a reply to message #313798] Mon, 14 April 2008 10:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I genuinely do not understand you. You have been asked innumerable times to use code tags when you post code. Why do you refuse to do so? Are you ignorant and are just ignoring the guidelines or do you truly not understand what you are doing wrong?
Re: Update Showing different results?? [message #313802 is a reply to message #313798] Mon, 14 April 2008 10:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
As Michael and Pablolee have mentioned, you will get good answers only when you follow the guidelines and post in the required format. So please try to adhere to that. Now coming back to your question
First Update
UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = V_CAS(I)
WHERE A.SITE_NO = V_SITE_NO(I);

Second update

UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = (select /*+ paralle(b,5,1) */
b.CAS_SEGMENT
from base_live_temp b
WHERE A.SITE_NO = b.site_no);


Don't you think by looking at the above statement you are comparing apples with oranges ? Also what is the use of the hint you have given. Luckily Oracle will ignore if it is not a valid hint.


Regards

Raj
Re: Update Showing different results?? [message #313843 is a reply to message #313798] Mon, 14 April 2008 20:43 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
*Notice: Formatted SQL is not the same as input*/

CREATE OR REPLACE PROCEDURE PR_BMS_UPDATE AS
CURSOR BMS_CUR IS
SELECT /*+ paralle(BASE_LIVE_SITES_1,5,1) */SITE_NO, CAS_SEGMENT
FROM BASE_LIVE_SITES_1
WHERE MAIN_SITE = 'Y';


TYPE SITE_TAB IS TABLE OF BASE_LIVE_SITES_1.SITE_NO%TYPE;
V_SITE_NO SITE_TAB;
TYPE CAS_TAB IS TABLE OF BASE_LIVE_SITES_1.CAS_SEGMENT%TYPE;
V_CAS CAS_TAB;
LN_UPDATED_RECORDS NUMBER := 0;
rows_affected varchar2(40);
BEGIN
OPEN BMS_CUR;
LOOP
FETCH BMS_CUR BULK COLLECT
INTO V_SITE_NO, V_CAS LIMIT 10000;

FORALL I IN 1 .. V_SITE_NO.COUNT

UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = V_CAS(I)
WHERE A.SITE_NO = V_SITE_NO(I);

--COMMIT;
LN_UPDATED_RECORDS := LN_UPDATED_RECORDS + V_SITE_NO.COUNT;


EXIT WHEN BMS_CUR%NOTFOUND;
END LOOP;
rows_affected:= to_char(sql%rowcount);

if sql%rowcount>=0 then
DBMS_OUTPUT.PUT_LINE('TOTAL' || LN_UPDATED_RECORDS || '' ||''||
'ROWS UPDATED');
DBMS_OUTPUT.PUT_LINE('TOTAL' || rows_affected || '' ||''||
'ROWS UPDATED');
end if;

COMMIT;
END PR_BMS_UPDATE;


SQL> exec PR_BMS_UPDATE;

TOTAL 961793 ROWS UPDATED
TOTAL 1793 ROWS UPDATED

PL/SQL procedure successfully completed.

Elapsed: 00:00:42.36

2nd Program

here i create a temp table of the same query as of the cursor in the 1st program
create table base_live_temp
as
SELECT /*+ paralle(BASE_LIVE_SITES_1,5,1) */SITE_NO, CAS_SEGMENT
FROM BASE_LIVE_SITES_1
WHERE MAIN_SITE = 'Y';



CREATE OR REPLACE PROCEDURE PR_BMS_UPDATE_TEMP AS
rows_affected varchar2(40);
BEGIN

UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = (select /*+ paralle(b,5,1) */
b.CAS_SEGMENT
from base_live_temp b
WHERE A.SITE_NO = b.site_no);


rows_affected:= to_char(sql%rowcount);

if sql%rowcount >= 0 then
DBMS_OUTPUT.PUT_LINE('TOTAL' ||''|| rows_affected || '' || '' ||
'ROWS UPDATED');
end if;
commit;

END PR_BMS_UPDATE_TEMP;

Elapsed: 00:00:44.47
SQL> exec PR_BMS_UPDATE_TEMP;
TOTAL 961789 ROWS UPDATED


I Apologise to all.Many times i am unable to figure out how to format,By just previewing it and sometimes in sql formatter (as
did in the earlier case),I don't get the desired result.

I have put my code in the [code] tags,If this doesn't work then,don't bother to answer to the questions.

Thanks and Regards
Re: Update Showing different results?? [message #313844 is a reply to message #313802] Mon, 14 April 2008 20:45 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I didn't get you?
About the paralled hint,I have altered the session to enable parallel operation,for faster processing

alter session enable parallel dml;
Re: Update Showing different results?? [message #313845 is a reply to message #313798] Mon, 14 April 2008 20:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I give up.
What problem are you trying to solve?


What is gained by wrapping this simple SQL statement inside of a PL/SQL procedure?
UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = (select /*+ paralle(b,5,1) */
b.CAS_SEGMENT
from base_live_temp b
WHERE A.SITE_NO = b.site_no);
Re: Update Showing different results?? [message #313873 is a reply to message #313798] Mon, 14 April 2008 23:59 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> Please suggest,How can this be different??

The easiest explanation would be, the table content changes in time, so number of updated rows is different as the query execution time differs.

> LN_UPDATED_RECORDS := LN_UPDATED_RECORDS + V_SITE_NO.COUNT;

In the first example, you count rows taken from BASE_LIVE_SITES_1

> rows_affected:= to_char(sql%rowcount);

In the second example, you count rows updated in BASE_MAIN_SITES_1

What are the table structures (only relevant columns with primary key/unique constraints)?
Does each row in BASE_LIVE_SITES_1 have corresponding row in BASE_MAIN_SITES_1?
How many rows does following query return?
SELECT *
FROM   Base_Main_Sites_1 a
WHERE  NOT EXISTS (SELECT 1
                   FROM   Base_Live_Sites_1 b
                   WHERE  a.Site_No = b.Site_No)

If you read the sticky about formatting, you should continue to its end. Using CODE tags (it would be good, if both code blocks would be divided and the description between them would be in normal style) is just first step. Typing nearly everything in uppercase without indenting does not make the code readable. What about using SQL Formatter?

> About the paralled hint,I have altered the session to enable parallel operation,for faster processing

SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Comments
Alphabetical Listing of Hints

Do you see PARALLE or PARALLED hint there?
Re: Update Showing different results?? [message #313903 is a reply to message #313844] Tue, 15 April 2008 02:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
O.K to explain more in detail in what I have said before.
This update will only update the records in base_main_sites_1 only where it could find a match.
First Update
UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = V_CAS(I)
WHERE A.SITE_NO = V_SITE_NO(I);

In this case you update all the records in base_main_sites_1 with a value returned from subquery if it could find a match. If no match found then it will be updated to null.
UPDATE /*+ paralle(A,5,1) */ BASE_MAIN_SITES_1 A
SET A.CAS_SEGMENT = (select /*+ paralle(b,5,1) */
b.CAS_SEGMENT
from base_live_temp b
WHERE A.SITE_NO = b.site_no);


This is what I am trying to highlight that these two updates are different and they are not the same.

Regards

Raj
Re: Update Showing different results?? [message #313922 is a reply to message #313798] Tue, 15 April 2008 03:25 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
@Raj: Good eyes. Beyond counting read vs. updated rows, both queries update in fact different rows.

Just a foreignism: this approach is verbatim described as comparing apples with pears in my country (oranges do not grow here Smile )
Previous Topic: ORA-06512 EXECUTE IMMEDIATE (merged 2 threads)
Next Topic: RAW datatype to sysdate (merged)
Goto Forum:
  


Current Time: Fri Dec 02 16:53:55 CST 2016

Total time taken to generate the page: 0.20288 seconds