Re: Avoiding "ORA-04091". Design Altenratives?

> > Carlo Sirna wrote:

> >> create table mytab
> >> (
> >> startdate date not null,
> >> enddate date not null
> >> )

> >> I don't want this table to contain overlapping intervals: it must
be
> >> impossible to insert a record that overlaps with an already
existing
> >> one or to update an existing record in order to make it overlap
over
> >> onto another one.

> > The basic mutating table avoidance is:
> >
> > before-statement-trigger:
> > clear a PL/SQL table
> >
> > after-row:
> > add rowid for this row to PL/SQL table
> >
> > after-statement-trigger:
> > use each rowid and do the work you would have liked to have done
> > within the row level trigger
> >
> > Easy enough - but if you have concurrent users modifying this
table, you
> > will need to use something like dbms_lock or a sentinel table to
control
> > access to the table whilst you do your work.
> >
> > Connor

> And, I'll say it, since Connor implied it, but didn't spell it out:
> I hate this design, it screams of serialization problems. That is,
> the "dbms_lock or a sentinel table" that Connor mentions is likely
> to really hurt you in terms of scalability. If you expect to
> support a significant number of concurrent users, now is the time
> to reconsider your design, if possible.

This is not my case: there are not more than 10 people that do have write access to this table and it is nearly impossible that two people do write conflicting data. in my case I have three main causes for corrupted data:

1. during customer's company startup orrible data comes from their old application. The old application they use, usually had absolutely no control on these errors.
2. corrupted data is generated by bugs in the procedure of exporting and importing from the above application
3. when using the program, those 10 users try to corrupt the data like they were used to do with the old application. And they find always a way to do it: for example, instead of using the front-end stored procedures (that already do the required checks), they access directly the data by loading the table inside Microsoft Access via ODBC....

during normal operation this table is not subjected to more than 200 updates/inserts a day, but It contains data collected over 10-15 years.

Anyway I would be interested in knowing which could be a good alternative to this structure... This is not exactly what this table does but you can think of it like this one:

Table mytab(

   customercode varchar2(10)
   vehiclecode varchar2(10)
   datestart date
   dateend date
)

what this table does is to keep track of the fact that customer X used vehicle Y in the period of time ranging from datestart to dateend.

What kind of data design could assure me that it is impossible to have the same person to drive 2 cars in the same day or the same car to be driven by 2 persons in the same day? (this is exactly the kind of problem i am trying to solve)

Thanks Received on Tue Nov 04 2003 - 09:49:36 CST