Way back when this was all being put together, somehow the developers won out over the powers-that-be. I'm a contract maintenance programmer, but I really have a desire to clean this mess up.) (Which wouldn't necessarily be a bad thing IMO.
Summarize in r code#
Any change to constraints will likely required massive code changes. Our database "layer" has about six different ways (all abhorrent IMO) to get data in/out of the database.
Summarize in r plus#
We have somewhere between 144K and 440K SLOC (depending on reckoning) plus about 25K lines in configuration/static data. Really learnt the value of a good DBA at that stage, helpfully I've got them at my current place too. This was without any downtime to the application too, and no code changes whatsoever. In my prior life working for a credit card company the Oracle DBA who organised the OLTP-like database (for the sales contact records that were generated by inbound and outbound calls to the call centre) did a fantastic job of automagically partitioning that core Contact table by the DateTime field on it.
Summarize in r archive#
It is entirely possible that your current data is actually valid, and if you add the integrity constraints now your archive process will proceed soooo much smoother, even if you do decide to use an explicit archive table, since it will become clear what data will become orphaned if you do, before you do it. Honestly, I'd fix that before I did any sort of archiving. As far as your data integrity is concerned they are nothing more than a *convention*. It lulls you into a false sense of security. I know you say you know, but simply don't call them foreign keys. While we do have foreign keys, they are not formalized in the database only in the program. Sadly, the real DBA was appointed after the previous one departed for greener pastures and knows only what he's learned and what the out-going DBA decided to tell him about our current setup. I'm not a DBA nor do I know Oracle well enough to know what its best practices are. I think we need to, at a minimum, add an index for the date field then tweak the queries/joins such that they include a maximum happened-long-ago cutoff. perhaps that archive table is where fired_date < sysdate - 30 * n-months stuff goes to rest and the remaining stuff stays in the 'main' partition. How easy maintaining such a thing is depends on the tool, and often on the skills and organisation of your DBA(s) if any, so Caveat Emptor. This may end up being huge overkill compared to just copying into a big old archive table, but it does mean you don't have to break FK relationships to do so, which I think is definitely a win. If your RDBMS supports table partitioning (the canonical partition in an append only table being some function of the date) then that imght work for your needs.Īny queries wishing to be fast in general without relying on PK looks ups need to specify their date range of interest, you can do a view that will only pull in the most recent few partitions that you expect all OLTP type activity to reference but the performance of this again, depends on the RDBMS. Have any of you been in this situation? What did you do? Did you regret it? Did you end up doing it in a better way? We'd have to create a secondary table to maintain the summary and now any queries against these statistics will need to come from two separate tables, complicating things even further. In the meantime, however, after giving it some further thought I began to wonder if this was the right idea.
Also, this table is apparently being used for more that just simple counts of how many times a given rule fires and when. (I'm personally looking at stealing the ideas/mechanism that RRDTool does with its archives.) We're still sussing out what the user's requirements truly are as they don't know for sure how granularly to summarize the information. General consensus is that we find an algorithm that will summarize the older data and trim the fat out of the table to make it more performant.
(Yes, there's no limit to the number of records queried, so we get the whole table.) Select r.rule_id, r.rule_type, max(ra.date_fired), count(ra.rule_number)
0 kommentar(er)
