Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / Oracle / Oracle Server / September 2007

Tip: Looking for answers? Try searching our database.

Which SELECT is less/more efficient?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Dereck L. Dietz - 30 Sep 2007 06:51 GMT
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2010 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.