- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Wait, how do you do foreign keys? Or doesn't consistency matter either?
Filed under: Whelp, seems I picked the wrong day to stop using flat files.
Admin
I'm sure there's a table for that!
Admin
You outsource them to Bangalore.
Admin
One image just wasn't enough
Admin
Dis...
No. No. Too easy.
Admin
Okay, let's put on my stupid cap. If we have table A and table B where table B has a column C which references A_ID in table A, have a script running every few seconds that checks table A and writes the contents of the A_ID column to a text file. In table B, create a script that runs every few seconds (or on adding a row to B, if possible) that checks for all rows and their C value, and if that C does not exist inside the A_ID text file, delete it from the table. If you are querying B and want to get some column out of A that you would normally join, have your client get the C out of B first and then you query A where A_ID = <whatever your C value is>. Or something.
Oh, that was probably a rhetorical question.
Admin
Sometimes I get sooo tired.
Admin
I still don't get what the DBA thought the advantage of this approach was?
Admin
Admin
Does an Attached Table violate the "Contains" part? Now that could get REAL interesting.
Admin
I guess this is an example of Generation ORM, where joining tables to execute complex queries is soooooo 1990s.
Admin
Maybe they didn't know it better ,,, once upon a time ... as unicorns still galloped through the woods :wink:
Admin
Duplicate ALL THE DATA!
Admin
Maybe the tables were stored on floppy disks in those days. There simply was not enough room for all the tables. But they did have 8 floppy drives...
Admin
Admin
As long as they used wooden tables ...
Admin
You can attach each of the databases that you need for your query to your session and so build up a view that only contains the tables you need and no extra fat.
And I am able to say this with a straight face. :expressionless: See? Anyone want to play poker?
Admin
They are obviously not using Oracle databases. The SYS schema alone has over 1,000 tables. Not to mention that an 11gR2 database should have at least 1 GB of RAM. Could the actually be using DBase?!?!?!?! I guess people really don't absolutely need transactions...
http://www.dbase.com/Knowledgebase/dbulletin/bu03_b.htm
Admin
This would be even funnier if they were using MySQL, which already puts each table into a separate file.
Admin
Only if you use myism tables
Admin
Definitely not! The tables get in the way of dancing.
Admin
Good point.
OK, the dining hall then :smile:
Admin
The DBA didn't go far enough. Surely an even better design would be a database that contains one table, which holds one column and one row of data.
Admin
That sounds like a job for XML!
Admin
Admin
I'm guessing the "DBA" was really just a crappy Sys Admin.
Admin
Unicorns could be so useful now. At least it would definitely help lampshading some of the things I see...
Admin
Hmm. I think "database" is the wrong word then for a container of tables. We should be calling it a "dining hall".
Admin
That's... special.
Admin
I'm trying to think of what kind of esoteric RAID level is somehow FS aware and wouldn't allow you to have files larger than its stripe size. Because that's what it would take to keep you from striping an individual file.
Perhaps a word other than "stripe" was intended.
Admin
I think “stripe” is perfect.
http://www.nenature.com/Images/StripedSkunkLAT.jpg
Admin
I produce a report annually for an external organisation, basically a historical view of stuff spanning ten years or so. There's some data which is fixed and some which varies from month to month, so internally we store this in two tables. (Yes, in the same database.)
But for the report, I have to send them each month's joined data as a separate table. Each table has to be in a separate database. And it has to be in Access .mdb format (the only time I ever have to touch Access).
I don't like doing that report.
Oddly, though I've documented the process and trained a couple of other people on it, nobody seems to be too keen on taking it over.
It does help that, several years ago, I automated most of the grunt work in terms of doing the joins and creating the individual databases. Size limitations in Access are still a massive headache for this process, one that grows worse annually as the amount of data increases.
Admin
TIL you can write cross schema/database JOINs.
No, a RDBMS does this. Not all databases work the same way.
Admin
Ugh this what i expect from a Junior DBA who thinks they know everything...
Admin
I think you mean a script kiddie who tries to be a Junior DBA Trainee ;-) and thinks they know everything :smile:
Admin
FTFY
* Possibly acquiring the title through performing various "favours" for the poss promoting them ** ** Then again it is just possible that they are in the position simply through a HUGE hiring error and the boss is too scared of their image withing the company to admit it yet.
Admin
You can use VBA to create new .mdb files and transfer the tables and data. Or since you mentioned automating most of the grunt work, is that what you're already doing? Pretty much the whole process sounds like it could be automated, except for any user input that might be required.
Is the Access size limitation something that you hit with just the data from 1 month? If so, :wtf:
Admin
My data-producing process loads the data into Oracle tables, but also produces flat-file outputs that I can feed to Access. (IIRC the flat files came first, and I added the Oracle tables later for ease of use.)
If it weren't for the size limitation, I could just load in all the data and have the macro run over the entire data set, spitting out a nice long stream of one-month databases.Instead, I load in the fixed part of the data (and make a bunch of copies of the database at this stage). The variable parts are split up into a bunch of files - initially these were 1-year files, so I'd take a copy of the database with the fixed data present, load in one year's worth of data, and let the macro spit out the monthly databases for that year, repeat until done.
An important note here is that I'm creating the table with a join in the database, then copying it out into a new database. This means that I can only create a limited number of tables before I run out of space in the host database. Even if I delete these tables after they've been copied out, the space is not reclaimed unless I do a (very lengthy) Compact and Repair on the database; it's more efficient just to grab a new copy of the database-with-fixed-data and load another chunk of the variable data into it.
The size of the joined tables increases in later years (business growing -> more data), so the data generating process currently spits out 1-year chunks of the variable data for the earlier years, then switches to 6-month chunks for the later ones. But as time passes, the size of the fixed data also increases; these effects combine to the extent that in the last run even some of the 6-month chunks couldn't be fully processed in one go (and some years where I'd previously been able to do one-year runs had to be split up into 6-month runs).
It doesn't look like I can generate the Access files directly from either Oracle or Informatica, unfortunately, but I should be able to re-engineer it so that I don't load the data tables into Access at all, but merely have them as links to the Oracle tables. Then my macro can just be given a date range and create all the month tables in that range. We'd still have to break it into pieces to avoid going over the 2GB limit, but that'd give us more space to work in; it'd just be the result tables taking it up (mind you when the result tables are in the 250-300MB kind of range, it doesn't take many). And it would save a lot of tedious "load data from flat file" steps. Ultimately, though, Access is still :WTF:.
Admin
Funny enough, I'm actually going to be (coerced? It's an assignment so...) doing this in class in a few weeks. [image]
I'm probably going to ask the Prof. if I can use my own instead...