Design Question

[THoey]

I have a developer that designed a "tall and skinny" table. Each record has a date, a tag id, and a count. That was fine until we started having almost 4000 tag id's per day. Now, when they are starting to pull weekly or monthly reports using Business Objects, and due to a limitation of BO, these queries are taking 35-45 minutes to roll up the data and performing multiple full table scans.

Now, their solution to this is to flip the table and make it "short and wide". Each record would be for one day, with a field for each tag id (4000 fields per record). I am really concerned of doing it this way. It just sounds really difficult to manage, especially since new tag id's (fields) would be added fairly often.

Just looking for any ideas about how you would design something like this?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[SantaMufasa]

Terry,

The problem is that "short and wide" (aka "short and morbidly obese" IMHO) in your case (and most others) absolutely, positively disobeys Codd's "First Normal Form" (no repeating attributes).

Before badly degrading what sounds like a decent design, I propose that you first examine all tuning alternatives first. The most important tuning issue that seems to be significant by its absence from your description is "indexing". If you have a good indexing strategy, I'm presuming that (depending upon your queries and especially your WHERE clauses) your full table scans may go away.

Also, with that many rows adding to your "tall/skinny" table, re-gathering statistics is vital to fast (CBO) access.

Have you a highly experienced Oracle-Tuning expert available to you? If you haven't, I can recommend to you one of the top Oracle-tuning authors in the industry (who is available for consults at very reasonable hourly rates) out of California. (If that is sounds viable to you, make contact via my signature, below.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Terry,

since you posed this as a design question, I assume that to some degree you can control and influence the design of the table.

All of Mufasa's comments make sense, wring the last drop of 'oomph' out of Oracle before allowing such a "morbidly obese" disaster into your database.

However, I believe that you should back-up a stage and consider the bigger issues. Basically someone is saying to you that because B.O. is unacceptably slow you should change a proven, known-good RDBMS design. The notion is risible and dangerous. If B.O. is total pants, don't do a total pants solution. Tails wagging dogs, and carts preceded by horses spring to mind.

Your original query (if on this occasion I have read it and understood correctly Mufasa ) stated that "queries are taking 35-45 minutes to roll up the data". Why not establish the user's reporting requirements and create a view or views, using roll up and cube in Oracle, and point the report at the view. Because it can use indexes (as per Mufasa's post) Oracle will move like greased lightning c.f. BO. Then change B.O. to look at the view(s). If BO is slow, then let BO change, not your RDBMS!

Highly de-normalised info is stored in a weird and wonderful thing called a data mart - maybe your BO punters have heard of this?

This may sound like purist twaddle Terry, but in my experience, forcing out the real issue, although painful, is less painful than living with the consequences of not doing so. Herewith, I descend from my 'requirements capture and design' soap box.

Regards

Tharg

 

[dbtoo2001]

I am also under the impression that BO can handle analytics. Perhaps you could have the universe creator try some new objects that use analytic functions?

 

[dbtoo2001]

Did you ask BOB?

http://www.forumtopics.com/busobj/index.php

 

[THoey]

Just a note to thank those that have replied to my question. I have forwarded the thread to my work email and will be discussing your suggestions with our development team.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[sem]

Dave,
this design (IMHO ugly, we call it "sausage") doesn't violate Codds 1-st normal form, but in fact builds another, obviously far less productive, dbms on top of Oracle. In most cases it's caused by the lack of knowledge about the area. Unfortunately the authors of such systems successfully launch them, get money and move to another "fast, successfull and flexible" project :-(

Terry,
you may consider mixed design, as in most systems there's a set of key (quasi-mandatory or used for searching) parameters, they are good candidates to become fields in wide table, and auxiliary ones. Periodically you may move (or copy) most popular parameters from the sausage to the table. I'd also suggest to store some meta-data, describing a whole set of parameters and their location to rebuild queries on-fly. In general you may need several related tables to make your data normalized. Of course, this is a deep refactoring, but only that way your "black hole" may become a system.




Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Each record would be for one day, with a field for each tag id (4000 fields per record)...new tag id's (fields) would be added fairly often.

Sem, I would be willing to bet your paycheck that a 4000-field record, nearly by definition, must be breaking "First Normal Form". If Terry posted the field descriptions, I just know we would see repeating groups. (Could you list 4000 field names without repeating classes of data?)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

AFAIK the 1-st normal form (not BCNF!) is just an atomicity requirement: no compound elements allowed. Now trying to find some authoritative (both for you and me) source to prove that and get a paycheck

Regards, Dima

http://seminihin.narod.ru