Today I have to retrieve data from the largest table of my project in Oracle. Approximately, this table has more than 1 million records and need to retrieve data carefully when we need. Now what I need to do is to retrieve negative quantity from this table.

There are two ways to retrieve negative quantity from the table. One is systemically and second is simple way.

view plain print about
1<cfquery name="CheckOne" datasource="myDSN">
2    select * from nh_inv_trx_tmp
3    where transaction_date < '7-Jan-2008'
4    and abs(quantity) <> quantity
5</cfquery>

view plain print about
1<cfquery name="CheckTwo" datasource="myDSN">
2    select * from nh_inv_trx_tmp
3    where transaction_date < '7-Jan-2008'
4    and quantity < 0
5</cfquery>

Got the following result when I put above those query in the same page and run it.

  • CheckOne execution time = 172ms
  • CheckTwo execution time = 219ms

      • It shows me that using built-in function in Oracle can reduce database server loading and led server to have good performance.

      This entry was posted on January 6, 2011 at 8:47 PM and has received 1299 views. There are currently 0 comments. Print this entry.