SELECT statement efficiency question

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sat, 7 Apr 2007 22:52:08 -0500
Message-ID: <iNYRh.1799$w41.967@newssvr19.news.prodigy.net>

I have the following two select statements in some code I've been looking at. I don't have
access to a computer with Oracle on it currently so I can't determine this myself.

Both selects do the same thing. The function in the second version simply checks the
condition what is in the first and send back a 'Y' or 'N' whereas the first has the
check in the where clause itself.

My question is this: is any one of the two selects more efficient (or less efficient)
than the other?

SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id

   AND a.deactivate = 0
   AND b.deactivate = 0
   AND (

             a.mbr_last_nm NOT IN ( 'DATA','CORRECTION' )
         AND a.mbr_first_nm NOT IN ( 'DATA','CORRECTION' )
        );


SELECT a.mbr_dob,
       a.mbr_first_nm,
       a.mbr_gender,
       b.mbr_key,
       a.mbr_last_nm,
       b.mbr_sys_key,
       b.mbr_updated_dt,
       a.mbr_x_fst_nm,
       a.mbr_x_lst_nm,
       a.person_id,
       a.z_person_id
  FROM mbr_person a,
       mbr_system b
 WHERE a.person_id = b.person_id

   AND a.deactivate = 0
   AND b.deactivate = 0
   AND not_data_correction( a.mbr_last_nm, a.mbr_first_nm ) = 'Y'; Received on Sat Apr 07 2007 - 22:52:08 CDT