Re: Three pieces of SQL, which one is best?

PhilHibbs wrote:
> -- Get the EMP_NO value from the latest period of work
> -- I think these are all ok, but I'm unsure about the third
> -- You can assume that there are no duplicate dates
>
> -- Old fashioned method
> SELECT pow.EMP_NO
> INTO l_emp_no
> FROM xxdm034_Employee_POW pow
> WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
> AND pow.START_DATE = ( SELECT MIN(START_DATE)
> FROM xxdm034_Employee_POW powd
> WHERE powd.N_I_NUMBER = pow.N_I_NUMBER
> AND powd.PAYROLL_TYPE IN ( 'Employee' ) );
>
> -- Descending ROWNUM method
> SELECT powd.EMP_NO
> INTO l_emp_no
> FROM ( SELECT pow.EMP_NO
> FROM xxdm034_Employee_POW pow
> WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
> AND pow.PAYROLL_TYPE IN ( 'Employee' )
> ORDER BY pow.START_DATE DESCENDING ) powd
> WHERE ROWNUM = 1;
>
> -- Fancy rank method, not sure about this, might have to
> -- be a sub-select like the ROWNUM version?
> SELECT pow.EMP_NO
> , RANK() OVER ( PARTITION BY pow.N_I_NUMBER
> ORDER BY pow.START_DATE DESCENDING ) rank
> INTO l_emp_no
> , l_rank
> FROM xxdm034_Employee_POW pow
> WHERE pow.N_I_NUMBER = emp.N_I_NUMBER
> AND pow.PAYROLL_TYPE IN ( 'Employee' )
> AND rank = 1;
>
> Opinions, both on correctness, performance and readability?
>
> Phil Hibbs.

Phil, to determine which query method is best from a performance point of view explain the queries and look at the plans then time test the queries.

Consider the plan, run time, and nature of the data now verse how it will look in the future. That is, try to determine if the query returning the best run time now will also probably return the best run time in the future considering expected growth.

If the code is cleanly formatted and commented properly there should be no reason for one version to be considered better than another from a maintenance coding point of view.

I cannot remember if Oracle added analytic queries to the standard edition but if not then that might be a consideration if you are developing an application on EE that may have to run on the standard edition.

HTH -- Mark D Powell -- Received on Fri Aug 25 2006 - 11:56:07 CDT