FAQ
My personal preference is with solution 2 - moving the current information to JOB. The scheduler can quietly insert into JOB_HISTORY when it is done with a job, and update the current line (do it through triggers if you like). Solution 3 violates the beloved KISS principle ...
Moreover, when you want to do some maintenance operation over the history table (purge, archival, whatever) you are going to interfere with the process which presumably polls the table.

My EUR 0.02

SF
----- ------- Original Message ------- -----
From: "Yechiel Adar"
To: Multiple recipients of list ORACLE-L

Sent: Thu, 13 Nov 2003 03:14:25

I usually use a switch in the scheduled job tables
to indicate active
status.
After the job run jus NULL or put another value in
it.
If you put bitmap index on this the search will be
a snap.

There is no reason to hold this pointer in the jobs
table.

Yechiel Adar
Mehish
----- Original Message -----
To: "Multiple recipients of list ORACLE-L"

Sent: Thursday, November 13, 2003 2:49 AM

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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: sfaroult_at_oriolecorp.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).

Search Discussions

  • Jacques Kilchoer at Nov 13, 2003 at 8:34 pm
    Merci beaucoup Monsieur. I agree with your point in the second paragraph.
    -----Original Message-----
    Stephane Faroult

    My personal preference is with solution 2 - moving the
    current information to JOB. The scheduler can quietly insert
    into JOB_HISTORY when it is done with a job, and update the
    current line (do it through triggers if you like). Solution 3
    violates the beloved KISS principle ...
    Moreover, when you want to do some maintenance operation over
    the history table (purge, archival, whatever) you are going
    to interfere with the process which presumably polls the table.

    My EUR 0.02
    --
    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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 13, '03 at 4:19p
activeNov 13, '03 at 8:34p
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase