Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: most preferable SQL
On Feb 8, 8:52 am, Amritha.Da..._at_gmail.com wrote:
> Which is most preferable between the below SQLs? In terms of consuming
> resources or fast execution.
>
> 1. Update Table1 set Reason = 'SX#' where
> (Sex <> 'M' or
> Sex <> 'F' or
> trim(Sex) <> '') and
> Key = inKey And
> Sub_Key = inSubKey and
> Reason is NULL;
>
> 2. Update Table1 set Reason = 'SX#' where
> (Sex not in ( 'M', 'F','') and
> Key = inKey And
> Sub_Key = inSubKey and
> Reason is NULL;
>
> I am calling this SQL in the stored procedure. If there is another
> better way of writing SQL for the above query, please let me know.
>
> Thanks in advance.
>
> Amrith
Those two statements are not equivalent.
ORA92> select 1 from dual where 'A' not in ('','F');
no rows selected
ORA92> select 1 from dual where ('A' <> 'F' or 'A' <> '');
1
1
Be careful when dealing with nulls.
And once you sort out the null issue in your queries,
try testing them yourself to find the answer.
Anurag Received on Thu Feb 08 2007 - 08:24:31 CST