Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Which SELECT is less/more efficient?

Which SELECT is less/more efficient?

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 30 Sep 2007 05:51:25 GMT
Message-ID: <xPGLi.9566$JD.2936@newssvr21.news.prodigy.net>


Oracle 10.2.0.3.0; Windows 2003 Server

These selects are against a table with over 3 million rows. SRV_DATE_FROM/ PERSON_ID/ SERVICE_TYPE_CODE and DATA_SOURCE comprise a primary key on a table the rows from these selects will be loaded into.

Is ordering the returned rows so the primary key with the lowest value MEMBER_KEY and MEMBER_SYSTEM_KEY rows get inserted first any less/more efficient than using a MIN function on the two columns and not ordering the rows?

Any duplicates that would be attempted to be inserted into the table would be trapped and written to an error table regardless of which select is used.

SELECT #1

      SELECT srv_date_from,
             person_id,
             service_type_code,
             data_source,
             member_key,
             member_system_key
        FROM t_aa_tb
       ORDER BY srv_date_from,
                person_id,
                service_type_code,
                data_source,
                member_key,
                member_system_key;

SELECT #2

      SELECT srv_date_from,
             person_id,
             service_type_code,
             data_source,
             MIN(member_key),
             MIN(member_system_key)
        FROM t_aa_tb;
Received on Sun Sep 30 2007 - 00:51:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US