In these days, I'm on HL7 project for my company. In this project, most of processes are created within Oracle Stored Procedure. Most of Oracle stored procedure, we need to retrieve the last record from table. At that time, we sometimes use "MAX" built-in function of Oracle. Using "MAX" function in Oracle is very simple and cannot write complicated query to get last record from table like query after query method with RomNUM. But the one I haven't noticed is the performance of retrieving data.

That's why I've wrote two query statements and test in PLSQL developer.

Using MAX function

view plain print about
1SELECT MAX(PATIENT_ID) FROM NH_PATIENT
2
3<!--- executing time --->
40.047 seconds

Using query and query Using MAX function

view plain print about
1SELECT PATIENT_ID FROM
2 (SELECT PATIENT_ID FROM NH_PATIENT
3 ORDER BY PATIENT_ID DESC) CLONE_USER
4WHERE ROWNUM = 1
5
6<!--- executing time --->
70.031 seconds

So, we should avoid using "MAX" function if we really don't need to use.

This entry was posted on June 8, 2011 at 12:06 AM and has received 1090 views. There are currently 0 comments. Print this entry.