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;
Michel Cadot - 30 Sep 2007 07:24 GMT
| Oracle 10.2.0.3.0; Windows 2003 Server
|
[quoted text clipped - 35 lines]
| MIN(member_system_key)
| FROM t_aa_tb;
I didn't read the whole post when I saw:
1/ The 2 queries won't give the same result set
2/ The second one is not valid
Regards
Michel Cadot
William Robertson - 30 Sep 2007 09:52 GMT
> Oracle 10.2.0.3.0; Windows 2003 Server
>
[quoted text clipped - 35 lines]
> 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.