Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Which SELECT is less/more efficient?
"Dereck L. Dietz" <dietzdl_at_ameritech.net> a écrit dans le message de news: xPGLi.9566$JD.2936_at_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;
|
|
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
Received on Sun Sep 30 2007 - 01:24:59 CDT