Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> data modelling question - job vs. job history table
Warning - this is a little long.
Thank you to those who take the time to read it.
I have a data modelling question (the target database will be an Oracle database.) I am keeping track of scheduled jobs run by a job agent.
Table 1:
JOB with columns JOBNO (primary key), JOBNAME
Table 2:
JOB_SCHED with columns JOB_SCHED_ID (primary key), JOBNO (foreign key to JOB), JOB_INTERVAL, JOB_START_DATE, JOB_END_DATE, JOB_RESULT
Table JOB_SCHED can have:
completed jobs: JOB_START_DATE not null and JOB_END_DATE not null
scheduled jobs: JOB_START_DATE not null and JOB_END_DATE null
unscheduled jobs: JOB_START_DATE null and JOB_END_DATE null
The job can be scheduled to run only once: JOB_INTERVAL null
or scheduled to run periodically: JOB_INTERVAL not null
A user can save an unscheduled job and then schedule it later. As currently designed JOB_SCHED contains job history for past jobs.
My background scheduler often looks up jobs to see which jobs should run now. If JOB_SCHED contains the history of all jobs run then I will have to scan through many rows to find out those jobs which should run now.
I could do this in several ways:
Option 1: put completed jobs in a different table called JOB_HISTORY, and then JOBNO would be UNIQUE in JOB_SCHED, or I could combine the columns in JOB and JOB_SCHED
Option 2: select * from JOB a, JOB_SCHED b where a.JOBNO = b.JOBNO and b.JOB_START_DATE is not null and b.JOB_END_DATE is null
But I propose option 3:
Add to JOBNO a column called CURRENT_JOB_SCHED_ID (foreign key to JOB_SCHED)
This should make it faster to find the current schedule for the job.
The tables have reciprocal foreign key relationships:
JOB_SCHED.JOBNO foreign key references JOB.JOBNO -> FK_JOBNO
JOB.CURRENT_JOB_SCHED_ID foreign key references JOB_SCHED.JOB_SCHED_ID -> FK_JOB_SCHED
FK_JOBNO characteristics: ON DELETE CASCADE FK_JOB_SCHED characteristics: DEFERRABLE INITIALLY DEFERRED (you insert into JOB before you insert into JOB_SCHED)
On JOB I have a BEFORE INSERT TRIGGER that generates JOBNO and CURRENT_JOB_SCHED_ID based on a sequence On JOB_SCHED I have a BEFORE INSERT TRIGGER that generates JOB_SCHED_ID based on a sequence if JOB_SCHED_ID is null
To create a new job:
insert into JOB returning the new JOBNO and CURRENT_JOB_SCHED_ID set by trigger
-- the insert into JOB will succeed because the FK relationship to JOB_SCHED is a DEFERRABLE FK constraint
insert into JOB_SCHED using the schedule ID returned by the above insert
commit
When a periodic job has completed:
update JOB_SCHED set JOB_END_DATE = SYSDATE
insert into JOB_SCHED returning the new JOB_SCHED_ID set by trigger, START_DATE = previous START_DATE + INTERVAL
update JOB set CURRENT_JOB_SCHED_ID to the schedule ID returned by the above insert
commit
When a "run-once" job has completed:
update JOB_SCHED set JOB_END_DATE = SYSDATE
update JOB set CURRENT_JOB_SCHED_ID to null
commit
Is there any reason why option 3 should be avoided?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Nov 12 2003 - 18:49:25 CST