Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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';
Good advice given so far. I would caution against creating too many indexes, as this will likely negatively impact performance of other parts of the system while offering minimal assistance to this SQL statement. Histograms could be important on the columns MBR_PERSON.DEACTIVATE, MBR_SYSTEM.DEACTIVATE, MBR_PERSON.MBR_LAST_NM, and MBR_PERSON.MBR_FIRST_NM. The histograms will give Oracle's cost based optimizer a better picture of the contents of the columns, rather than assuming an even spread of the data values between the min and max for the column.
Keep in mind that Oracle may transform your SQL statement into another equivalent form, and may use constraints and transitive closure to generate additional predicates (think entries in the WHERE) clause for the SQL statement. You may want to perform timing with alternate SQL syntax:
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.DEACTIVATE = B.DEACTIVATE
My guess is that the above SQL statement will perform a full tablescan on both tables. The full tablescans may be the most efficient way to retrieve the rows. However, that will depend greatly on the composition of the data in the two tables. Oracle may select to perform a hash join between the two tables, so a large HASH_AREA_SIZE may help.
As has been mentioned in this thread, avoid using PL/SQL for something that can be done efficiently in pure SQL. Context switches (and I suppose on-the-fly compiling of the PL/SQL code if not using Native code compilation) are expensive on some operating system platforms, and may greatly decrease performance.
The best way to test the performance of the various methods requires access to the Oracle database, unless of course you want to dig through 10046 traces at level 8 or 12.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Sun Apr 08 2007 - 08:37:37 CDT