Hi all,
Consider a table MY_TABLE that has the following values:
column1 column2 column3
------- ------- -------
123 Citizen John
456 Citizen john
789 citizen John
001 Kent Clark
002 Lang Loise
The first 3 records above actually refer to the same
person but has been entered in the database 3 times
with 3 different values in column1. Records 5 and 6
are unique records and are alright.
What I would like to do is create a view that displays
only the records that I have same values in column1 and
column3. The view, for example, should display the
following:
column1 column2 column3
------- ------- -------
123 Citizen John
456 Citizen john
789 citizen John
I can think of 2 formulations which are:
- create view MY_VIEW
as select distinct *
from MY_TABLE A, MY_TABLE B
where upper(ltrim(rtrim(A.column1)))=upper(ltrim(rtrim(B.column1))) and
upper(ltrim(rtrim(A.column2)))=upper(ltrim(rtrim(B.column2))) and
A.rowid != B.rowid;
- create view MY_VIEW
as select *
from MY_TABLE A
where EXISTS (select '*' from MY_TABLE B
where upper(ltrim(rtrim(A.column1)))=upper(ltrim(rtrim(B.column1))) and
upper(ltrim(rtrim(A.column2)))=upper(ltrim(rtrim(B.column2))) and
A.rowid != B.rowid);
My question is, which of the 2 formulations is more efficient?
