|
|
Update yields different results [message #445924 is a reply to message #445913] |
Thu, 04 March 2010 12:50   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Hi Michael,
Apologies for the earlier testcase. I made a school boy error. Now hopefully I managed to produce a meaningful testcase.
I am finding very difficult to understand the behaviour of the update statement.
Any reason behind it or am I missing something here. To be honest I am not able to find anything wrong with it.
Version is Oracle 10.2.0.4. Try running this script as a normal user who doesn't have dba privileges. Atleast that's why I have tested it on.
First update yields 0 updates
Second update yields 1 update
third update yields 1 update
To me all the three updates should yield the same result. Correct me if I am wrong
drop table test_tab_parent;
drop table test_tab_child;
create table test_Tab_parent
(
sno number,
cnt number,
constraint pk_test_tab_parent primary key(sno)
);
create table test_tab_child
(
sno number,
row_cnt number
);
insert into test_tab_parent
(
sno, cnt
)
select level, 1 from dual connect by level <= 10;
insert into test_tab_child
(
sno, row_cnt
)
select a.lev, a.cnt from
(
select level lev, 1 cnt from dual connect by level <= 10
) a,
(
select level from dual connect by level <= 10
) b;
commit;
Prompt Primary key Enabled
update test_tab_parent parent
set cnt = (select count(*) from test_tab_child where parent.sno = child.sno)
where
parent.cnt != (select count(*) from test_tab_child where parent.sno = child.sno)
and parent.sno = 1;
select * from test_tab_parent;
rollback;
alter table test_tab_parent drop constraint pk_test_Tab_parent;
Prompt Primary key Disabled
update test_tab_parent parent
set cnt = (select count(*) from test_tab_child where parent.sno = child.sno)
where
parent.cnt != (select count(*) from test_tab_child where parent.sno = child.sno)
and parent.sno = 1;
select * from test_tab_parent;
rollback;
Prompt Primary key Enabled with hardcoded values
alter table test_Tab_parent add constraint pk_Test_tab_parent primary key(sno);
update test_tab_parent parent
set cnt = (select count(*) from test_tab_child where parent.sno = child.sno)
where
parent.cnt != (select count(*) from test_tab_child where parent.sno = 1)
and parent.sno = 1;
Regards
Raj
|
|
|
Re: Update yields different results [message #445928 is a reply to message #445924] |
Thu, 04 March 2010 13:09   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I have not the same behaviour (10.2.0.4 too),
When there is the constraint I have 0 row updated and 1 when the constraint is not there. At least it is consistent in its error.
The execution plans explain the difference in behaviour.
With constraint:
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 78 | 4 (0)| 00:00:01 |
| 1 | UPDATE | TEST_TAB_PARENT | | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 78 | 4 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_TEST_TAB_PARENT | 1 | 39 | 0 (0)| 00:00:01 |
| 5 | VIEW | VW_SQ_1 | 10 | 390 | 3 (0)| 00:00:01 |
| 6 | SORT GROUP BY | | 10 | 260 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| TEST_TAB_CHILD | 10 | 260 | 3 (0)| 00:00:01 |
| 8 | SORT AGGREGATE | | 1 | 13 | | |
|* 9 | TABLE ACCESS FULL | TEST_TAB_CHILD | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("VW_COL_1" IS NULL AND "PARENT"."CNT"<>0 OR "PARENT"."CNT"<>"VW_COL_1")
4 - access("PARENT"."SNO"=1)
7 - filter("CHILD"."SNO"=1)
9 - filter("CHILD"."SNO"=:B1)
Without it:
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 78 | 7 (15)| 00:00:01 |
| 1 | UPDATE | TEST_TAB_PARENT | | | | |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN OUTER | | 1 | 78 | 7 (15)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEST_TAB_PARENT | 1 | 39 | 3 (0)| 00:00:01 |
| 5 | VIEW | VW_SQ_1 | 10 | 390 | 3 (0)| 00:00:01 |
| 6 | SORT GROUP BY | | 10 | 260 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| TEST_TAB_CHILD | 10 | 260 | 3 (0)| 00:00:01 |
| 8 | SORT AGGREGATE | | 1 | 13 | | |
|* 9 | TABLE ACCESS FULL | TEST_TAB_CHILD | 1 | 13 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("VW_COL_1" IS NULL AND "PARENT"."CNT"<>0 OR
"PARENT"."CNT"<>"VW_COL_1")
3 - access("PARENT"."SNO"="SNO"(+))
4 - filter("PARENT"."SNO"=1)
7 - filter("CHILD"."SNO"=1)
9 - filter("CHILD"."SNO"=:B1)
You retrieve the same result and execution with the constraint when you add the following hint:
You have a nice test case to open a SR and raise a new bug.
Tell us what is the answer of Oracle when you'll have one.
Regards
Michel
[Edit: fix typo]
[Updated on: Fri, 05 March 2010 01:49] Report message to a moderator
|
|
|
Re: Update yields different results [message #445997 is a reply to message #445928] |
Fri, 05 March 2010 01:42  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Hi Michael,
Thanks for testing the test case and updating the thread with your observation. I will be raising the SR today and will update the thread with further updates.
Regards
Raj
|
|
|