Home » SQL & PL/SQL » SQL & PL/SQL » Test (Oracle 10g, Solaris 10)
Test [message #445909] Thu, 04 March 2010 11:55 Go to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Apologies... Ignore my post

Regards

Raj

[Updated on: Thu, 04 March 2010 12:09]

Report message to a moderator

Re: Test [message #445913 is a reply to message #445909] Thu, 04 March 2010 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the point? I have the same result in both cases.

Regards
Michel


Edit: Ah! OK.

[Updated on: Thu, 04 March 2010 12:19]

Report message to a moderator

Update yields different results [message #445924 is a reply to message #445913] Thu, 04 March 2010 12:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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:
/*+ full(parent) */ 


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 Go to previous message
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
Previous Topic: Sum of Character column
Next Topic: Newton-Raphson method with sql
Goto Forum:
  


Current Time: Tue Feb 11 10:25:57 CST 2025