Re: SELECT statement efficiency question

On Apr 7, 11:52 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> 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';

In general, anything that you can do strictly in sql ( without using plsql ) is probably going to be much more efficient and faster. This should be tested and benchmarked in specific cases of course.

It's always good when you submit a question like this to include details about what version of oracle.

While the optimizer has evolved and version by version may make better choices when evaluating a NOT IN phrase at times especially when given such a small range of things you are checking for alternate coding should be considered or at least evaluated ( and ( a.mbr_first_nm <> 'DATA' OR a.mbr_first_nm <> 'CORRECTION' ) etc ) but IMHO something just seems very strange about the design in the first place. Received on Sun Apr 08 2007 - 07:38:28 CDT