On Sep 30, 6:51 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> 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;
Even with the missing GROUP BY clause added, if that was the idea, and
assuming the ORDER BY version is part of some cursor loop construction
in which you insert rows individually and discard duplicates (the most
inefficient approach possible btw), they still won't give the same
results because the MIN(member_key) and MIN(member_system_key) values
could come from different source rows.
Received on Sun Sep 30 2007 - 03:52:35 CDT
