Find unmatched rows based on multiple columns.

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Find unmatched rows based on multiple columns.

Postby Anand » Sun Apr 14, 2013 1:28 pm

Hi Tim,

I am having trouble in finalizing the query which suits to my requirement:

My requirement is to join 2 tables based on P.K "A_NUM" and then find the unmatched rows based on 2 columns "SPARE_1" & "STATUS".

Table Structure alongwith data are as follows:-
Code: Select all
CREATE TABLE SW_TST1(A_NUM VARCHAR2(20) NOT NULL,spare_1 NUMBER(2),status VARCHAR2(1) NOT NULL);
CREATE TABLE SW_TST2(A_NUM VARCHAR2(20) NOT NULL,spare_1 NUMBER(2),status VARCHAR2(1) NOT NULL);


Code: Select all
insert into sw_tst1 (A_NUM, SPARE_1, STATUS)
values ('94322265742', 0, 'A');               --Unmatched Rows
insert into sw_tst1 (A_NUM, SPARE_1, STATUS)
values ('94112855533', 11, 'I');                  --Unmatched Rows
insert into sw_tst1 (A_NUM, SPARE_1, STATUS)
values ('9411285', 15, 'A');


Code: Select all
insert into sw_tst2 (A_NUM, SPARE_1, STATUS)
values ('94322265742', 1, 'I');                     --Unmatched Rows
insert into sw_tst2 (A_NUM, SPARE_1, STATUS)
values ('94112855533', 11, 'A');                  --Unmatched Rows
insert into sw_tst2 (A_NUM, SPARE_1, STATUS)
values ('9411285', 15, 'A');


Query 1: Concatenating both columns of each tables first and then using the non-equality operator to find the unmatched rows.
------------
Code: Select all
SELECT *
   FROM sw_tst1
     INNER JOIN sw_tst2 ON sw_tst1.a_num = sw_tst2.a_num
    WHERE (NVL(sw_tst1.spare_1, '-1') || NVL(sw_tst1.status, '-1')) <> (NVL(sw_tst2.spare_1, '-1') || NVL(sw_tst2.status, '-1'));


Query 2 : Using non-equality operator for each column one by one,joining through "OR" operator.
------------
Code: Select all
SELECT *
   FROM sw_tst1
     INNER JOIN sw_tst2 ON sw_tst1.a_num = sw_tst2.a_num
    WHERE (NVL(sw_tst1.spare_1, '-1') <> NVL(sw_tst2.spare_1, '-1') OR
               NVL(sw_tst1.status, '-1') <> NVL(sw_tst2.status, '-1'));


I want to include the "Query 2" in my program unit but the logic written inside my program unit forces me to use the "Query 1" instead of "Query 2" ,
what I want to ask you is that "IS THERE ANY ISSUE IS USING THE "QUERY 1" IN PLACE OF "QUERY 2" FROM PERFORMANCE OR TECHNICAL STANDPOINTS"?

Please let me know if there is any.

P.S: Unmatched columns can increase up to 5.

Thanks in advance.

With Regards,
Anand
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Find unmatched rows based on multiple columns.

Postby Tim... » Sun Apr 14, 2013 2:03 pm

Hi.

I don't understand when you say, "logic written inside my program unit forces me to..." This doesn't make sense to me. Use the query that gets you the result you want.

It looks like both of these queries do the same thing. What does look odd to me is you have a number variable, that you are assigning a string value to. It would make more sense to do "NVL(sw_tst1.status, -1)", since using the string forced Oracle to do an implicit datatype conversion from string to number, only to convert it back again when you do the concatenate. You are just making more work for yourself.

The other thing to consider, is the value -1 possible in a normal situation? If so, then using NVL to -1 is dangerous.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Find unmatched rows based on multiple columns.

Postby Anand » Sun Apr 14, 2013 4:49 pm

Hi Tim,

First of all , thanks for your prompt reply.
Yes your considerations looks fine to me:-
1) I will assign the numeric -1 in place of string -1.
2)No, the columns can't contains -1 as a value,only positive values is possible in these columns.

Just to ask one more question, Which query i.e. Query1 and Query2 according to you is good from performance standpoint?

Thanks & Regards,
Anand Kumar Ojha
Anand
Member
 
Posts: 22
Joined: Thu Jun 07, 2012 2:59 pm

Re: Find unmatched rows based on multiple columns.

Postby Tim... » Sun Apr 14, 2013 5:16 pm

Hi.

Well, it's not always possible to tell performance just by looking at a query, but I can see that the second query has the potential for short-circuit evaluation. Oracle knows that:

TRUE OR FALSE = TRUE

So if the first part of the boolean expression equates to TRUE, Oracle won't bother to the evaluate the second half of the expression. For a query that is quite but, if the first equation mostly equates to true, this could represent a significant reduction in work that needs to be done. So put the expression that is most likely to equate to TRUE on the left hand side of the OR.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 5 guests

cron