Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with SQL constraint
hasta_l3_at_hotmail.com wrote:
> On 16 fév, 17:24, DA Morgan <damor..._at_psoug.org> wrote: >> hasta..._at_hotmail.com wrote:
>>>> dean wrote: >>>>> On Feb 15, 11:41 am, DA Morgan <damor..._at_psoug.org> wrote: >>>>>> Arto Viitanen wrote: >>>>>>> dean wrote: >>>>>>>> Hello all, >>>>>>>> A table T has 2 fields, one (L) holding letters 'Y' and 'N', and one >>>>>>>> (X) holding numbers. Is there a (non trigger)constraintsuch that for >>>>>>>> L='N' (and only this letter) the numbers must be unique? Records >>>>>>>> where L='Y' do not have to be unique. >>>>>>>> (I need to join another table to the L='N' group of records, and the >>>>>>>> join must be key-preserved). >>>> So, essentially, you want to NOT have unique data but convince Oracle, >>>> by some hocus-pocus magic trick, that the data is unique. >>>> As I presume you intend to put this into production there is no more >>>> helpavailable from me. > YOYO. There is one and only one solution ... >>>> correctly model your business requirement.
>> You store all hits received even when they are dups. >> Then, in your report, or using a materialized view, you formulate an >> accurate report based on that data. >> >> All hits received from all sensors are equally valid. It is the job >> of reporting to turn it into information. > > That's indeed the best solution, Daniel. > > However, I stressed that traffic is huge. You have may > store 2 or 300 times more information than what the > requirements ask for. > > When the history to be kept is huge by itself, > then it makes (at least some) sense to accumulate > the data at the acquisition level.
Perhaps. But lets take another look at the situation. Presuming there is a front-end ... what is the longest amount of time that is counted for a vehicle to enter and depart the zone? How about 24 hours? What if it drives in on Monday and departs three weeks later? Is that one event?
If it is a reasonably finite time ... cache the data real-time in something like TimesTen and then only write the final result to a results table.
Personally I'd keep all of the data ... because with little room for doubt ... someone next week or next year will ask for something based on what was thrown away.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Feb 16 2007 - 12:07:54 CST