a index will help (in reverse order)
CREATE INDEX IDX_MAXRECV_DATE ON T
(RECV_DATE)
REVERSE
COMPUTE STATISTICS;
Main Topics
Browse All Topics
1. I have a procedure "process_orders" that is runs monthly by a user off a web page.
It takes 6 minutes on the web.
Instead of keeping user waiting, I want to print a page to tell USER that a job has been scheduled to run and he will receive a confirm email upon completion
Do i just create a small procedure that print the message and schedule "process_orders" and have "process_orders" send the email at the end of execution. any code sample.
another small index question
2. I have a big table (1 million records) that has RECV_DATE date/time stamp.
There is a job that runs every minute computing the MAX
SELECT MAX(RECV_DATE) from table where RECV_DATE < SYSDATE
HAVING MAX(RECV_DATE) < SYSDATE - 300/86400
would an index on RECV_DATE help in this case? The table is storing 5000 records per day too.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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
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>>
sam15: Yes, you'd need a new procedure (I've given an outline below). Schwertner is correct, you don't need an extra procedure. However, I do think it's cleaner to have a separate procedure (since emailing on job completion is part of your app, and might not been done everytime you want to run "process_orders". A comprimise is to pass a parameter into "process_orders" (see example).
schwertner: The question has "Oracle 9.x" zone, which is why i suggested DBMS_JOB. Otherwise, if Oracle 10.x I agree, DBMS_SCHEDULE is a better solution (and recommended by Oracle) to handle this.
>>sam15: i assume I still need a NEW_PROCEDURE anyway to simply print a page that
I missed out the COMMIT, since I pressumed this is a MOD_PLSQL application, each HTTP request that touches the database results in an implicit COMMIT.
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
I believe that all parts of the asker's question have now been answered. I think that the points should be split equally between the following posts:
schwertner (http:#24771019), andytw (http:#24774327), andytw (http:#24784549)
Business Accounts
Answer for Membership
by: Geert_GruwezPosted on 2009-07-02 at 16:28:48ID: 24768793
first:
why not use this select (1 less step)
SELECT MAX(RECV_DATE)
from T
where RECV_DATE < SYSDATE - 300/86400;