orcl_job

a index will help (in reverse order)

CREATE INDEX IDX_MAXRECV_DATE ON T
(RECV_DATE)
REVERSE
COMPUTE STATISTICS;

this query would also work:
select recv_date
from t
where RECV_DATE < SYSDATE - 300/86400
  and rownum < 2
 order by 1 desc;

i think if add the having filter afterwards will raise a no_data_found exception and go there.
why do the index in reverse. is it faster to query? it is like index of a book. having it reverse will not make things easier to find though. is it.

RECV_DATE < SYSDATE
and
MAX(RECV_DATE) < SYSDATE - 300/86400
are not logical.

300/86400 = 0.00347

So only
MAX(RECV_DATE) < SYSDATE - 300/86400
will do the job

So the equavalent statement will be

SELECT MAX(RECV_DATE) from table  
HAVING MAX(RECV_DATE) < SYSDATE - 300/86400;

Because you have no groupings an index over the column RECV_DATE
will highly help to get the result walking only on the BTree without doing
sorting. Of course after creating the index run the ststistics over the table and the
index.

1.  To make the procedure run in the background, you can schedule it using DBMS_JOB.  Add the following code to schedule the job.  It will submit the job to run straight away (in the background).
Do you want "process_orders" to always e-mail?  If not, then I'd wrap that in another procedure, which:  calls "process_orders", then sends an e-mail.

2.  I agree with Schwertner.  A *normal* (B-tree) index on RECV_DATE will help here.  

>a index will help (in reverse order)
No, a reverse index isn't a good idea here, as it restricts the optimizer from using an index range scan.  

In a reverse index, oracle stores an index entry in reverse byte order.  This can help when there's heavy inserts into a table in sequential order, which can result in block contention.  Since in reversed indexes, the entries are reversed, inserts are spread across different blocks reducing contention.

sam15:  you can see the results for yourself (see my attached tests for a table with 1 million records) - look at the consistent gets, that's the important number, and you'll see that the normal index wins hands-down:
no index:            10976
reverse index:    2732
normal index:      77

DECLARE
  i BINARY_INTEGER;
BEGIN
  dbms_job.submit(job       => i,
                  what      => 'process_orders();',
                  next_date => sysdate,
                  interval  => NULL);
END;
/

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

Tha is excellent example.

What would be the difference in having "process_orders" send the email OR creating another procedure that calls process_orders and send the email? Which is cleaner design. i assume I still need a NEW_PROCEDURE anyway to simply print a page that
1) tells the user the job has been scheduled and he will receive an email when it completes on success or failure.
2) schedule the job using DBMS_JOB


WHat do you mean by this? This table does have a sequence (seqno) and is heavily used to store heartbeats sent by other systems every minute.
<<This can help when there's heavy inserts into a table in sequential order, which can result in block contention>>

the procedure can send emails also.
You do not need to use separate procedure to send emails.
But the procedure to schedule jobbs possibly has errors.
At least a COOMIT; should end the declaration.
Also DBMS_SCHEDULE is the bettre solution.

and re: REVERSE indexes ...
Here's an extract from the Oracle Database Performance Tuning Guide:

  "2.5.3.2.5 Reverse Key Indexes
  These indexes are designed to eliminate index hot spots on insert applications. These indexes are   excellent for insert performance, but they are limited in that they cannot be used for index range scans."

I would only recommend using this if you are having problems with normal indexes, and are experiencing index block contention due to hot leaf blocks.  See the following Oracle doc for more information on reverse indexes, what they are, and when to use them:

Oracle Database Concepts
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref998