Bending the rules using MERGE INTO

From: Jeffrey Hunter <jhunter_at_iDevelopment.info>
Date: 16 Jul 2003 12:22:00 -0700
Message-ID: <9dfc69b4.0307161122.31f3669e@posting.google.com>

Hello,

I have a situation where I would like to use the MERGE INTO statement to merge literal values into a single table based on a particular condition. (I don't want to use PL/SQL to solve this, rather a single SQL statement). The query below provides an example of what I am trying to accomplish. I am looking for comments or suggestions if I am bending the rules here or is there a better way to accomplish this. Thanks in advance!

SQL> create table test (a number primary key, b number);

SQL> merge into test

     using dual on (dual.dummy is not null and test.a = 1)
     when not matched then
       insert values (1,1)
     when matched then
       update set test.b = 2;

1 row merged.

Execution Plan

---

0 MERGE STATEMENT Optimizer=CHOOSE (Cost=5382720 Card=5470730368 Bytes=142238989568)

1 0 MERGE OF 'TEST'

2    1     VIEW
3    2       NESTED LOOPS (OUTER) (Cost=8176 Card=8168 Bytes=285880)
4    3         TABLE ACCESS (FULL) OF 'DUAL' (Cost=8 Card=8168
Bytes=16336)
5    3         VIEW
6    5           TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1
Card=1 Bytes=33)
7    6             INDEX (RANGE SCAN) OF 'SYS_C007729' (UNIQUE)

(Cost=1 Card=1)

SQL> select * from test;

         A B
---------- ----------

         1 1

SQL> merge into test

     using dual on (dual.dummy is not null and test.a = 1)
     when not matched then
       insert values (1,1)
     when matched then
       update set test.b = 2;

1 row merged.

Execution Plan

---

0 MERGE STATEMENT Optimizer=CHOOSE (Cost=5382720 Card=5470730368 Bytes=142238989568)
1 0 MERGE OF 'TEST'

2    1     VIEW
3    2       NESTED LOOPS (OUTER) (Cost=8176 Card=8168 Bytes=285880)
4    3         TABLE ACCESS (FULL) OF 'DUAL' (Cost=8 Card=8168
Bytes=16336)
5    3         VIEW
6    5           TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1
Card=1 Bytes=33)
7    6             INDEX (RANGE SCAN) OF 'SYS_C007729' (UNIQUE)

(Cost=1 Card=1)

SQL> select * from test;

         A B
---------- ----------

         1 2

Regards,
-- jeff

---

Jeffrey Hunter, OCP
Senior Database Administrator
Email : jhunter_at_iDevelopment.info
Web : www.iDevelopment.info

---

Received on Wed Jul 16 2003 - 14:22:00 CDT