Re: data modelling question - job vs. job history table
It all depends on the frequency of your scheduler.. If it wakes up every 10 seconds the blocks are already in memory so updating the bitmap will not involve that much overhead. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: data modelling question - job vs. job history table
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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Re: data modelling question - job vs. job history table
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 [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: data modelling question - job vs. job history table
Jacques, Why make it sooo complicated? I like the third table - Job_History that shows what job ran and when. Much clearer now, and in a year from now when you (or someone else) goes and reviews what you did. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 12, 2003 7:49 PM To: Multiple recipients of list ORACLE-L 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: data modelling question - job vs. job history table
Jacques, What you are doing is not exactly data modeling: you are designing a database. When data modeling, the database to be used is irrelevant. There are no columns or primary keys, but there are attributes, and one or more unique identifiers. From a DM perspective, I would be first inclined to think of JOB as a super type, with 3 subtypes of SCHEDULED, UNSCHEDULED and COMPLETED, each with their unique attributes. I'm not too sure about the UNSCHEDULED though, it sounds as if that just really containts the base attributes for the job, though this 5 minute email hardly constitutes a modeling session. :) The attributes in common wuld be just the job_no as far as I can see. UNSCHEDULED should probably be called something like CONFIG. CONFIG would contain the interval, earliest start date, and maybe a latest_start_date, past which the job would not run, along with whatever else is needed to describe the actual process that is to run. SCHEDULED would contain the start_date and time, as well as anything else that is needed at runtime. This makes me reconsider the value CONFIG subtype. It could very well be that these attributes should be in the supertype JOB. What would drive that would be whether you want changes made to a jobs configuration to affect scheduled jobs or not. If so, they go in the supertype, if not, then into the CONFIG subtype they go. This DM would leave you free to design the database with 1-4 tables to implement the JOB supertype and subtypes. I like the idea of keeping only jobs that are scheduled in one of the tables, and deleting them upon completion. HTH Jared Jacques Kilchoer [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/12/2003 04:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: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: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051
RE: data modelling question - job vs. job history table
-Original Message- Mercadante, Thomas F Why make it sooo complicated? I like the third table - Job_History that shows what job ran and when. Much clearer now, and in a year from now when you (or someone else) goes and reviews what you did. Because I can? Because when I make something complicated people think I'm smart? :) My first idea was to have a job_history table, but when I was looking at my tables I noticed that the job_schedule table and the job_history table had pretty much identical columns, and I said to myself why have two tables when all the columns are the same? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: data modelling question - job vs. job history table
-Original Message- [EMAIL PROTECTED] What you are doing is not exactly data modeling: you are designing a database. I stand corrected. I am actually looking at a denormalized set of tables we have here (there's a table with 52 columns: OBSERVATION1, OBSERVATION2, ... OBSERVATION52) and playing aroung with a different design. But perhaps I need to step back and look at it from the data model point of view as you suggest. (Don't tell me I have to go in my modelling tool Your suggestions sounded good to me. I didn't think of a CONFIG subtype. I have a tendency to go straight to the physical model anyway. I guess I should look at the Conceptual Data Model option in my design tool. :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Re: data modelling question - job vs. job history table
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: data modelling question - job vs. job history table
-Original Message- Yechiel Adar 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. Except would you put a bitmap index on a column in a table that gets updated frequently? There is no reason to hold this pointer in the jobs table. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
