Re: Which is the more efficient SQL formulation?

From: SANJAY PURI <skp_at_ix.netcom.com>
Date: 18 Oct 1994 13:38:43 GMT
Message-ID: <380j53$n7k_at_ixnews1.ix.netcom.com>


In <37vbo3$4f0_at_grivel.une.edu.au> mpaulo_at_metz.une.edu.au (Maria Paulo) writes:

>
>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:
>
> 1) 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;
>
> 2) 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?
>
>I appreciate any help.
>
>Maria (mpaulo_at_mossman.une.edu.au)
>

I think the second option is the good one.

VK Nair Received on Tue Oct 18 1994 - 14:38:43 CET

Original text of this message