Re: Load and Query question

On 04/27/2006 11:24:41 AM, Shreeni wrote:
> Hi List,
>
> I have a semantic question in a DWH environment. At the current place I
> work, the tables have been de-normalized to such an extent that they may not
> get qualified as 2NF even. Sure there are PK but they are there more to
> provide a security implementation rather than follow the relational
> priciples. Out of 200+ tables there is not one dimension or a fact or even
> one referential key. Each table has about 130+ columns out of which equal
> quantities are codes and descriptions.
>
> The argument in favor the current design is that joins are expensive.
> However from a DBA's perspective would you rather have a relational model
> which would help really large load volumes and get the data out there faster
> or go with a totally denormalized structure and don't care about when the
> data is delivered ?
>
> On an average day, the load takes more than 10+ hours and volumes are
> presently at 8mil+ rows everyday. The env is Solaris 9, Oracle 10.2.0.1.0
>
> Thanks
>
> Shreeni
>

Shreeni, the English word "constraint" can be translated roughly as "limitation" or "ban". Constraints are here to prevent non-conforming data from making its way into the database. Constraints are, primarily, logical entities which help with keeping your data logical. If somebody enters date of birth as 4/1/2525, then you have to deal with either a prankster or doctor Who. Both cases deserve special handling so you're well advised to prevent them from entering your database. That is the primary purpose and the prime directive of database constraints. In data warehouse situation, you don't have to prevent data from entering, data is already checked and trusted, you are extracting data from your OLTP database, transforming it along the way and loading it into the DW. That process is called ETL. In the DW you don't need constraints, but you do need physical entities which speed up retrieval and joins. Those physical entities are called "indexes" or "indices", depending on your garden variety of English spelling. You must index your DW properly. Indexing schemes are widely described in various books. The best one I can think of is called "The Data Warehouse Toolkit", by Ralph Kimball and introduced to me by my dear friend Tim Gorman, also a member of this list. I can wholeheartedly recommend this book as well as Tim's own work: "Essential Oracle8i Data Warehousing". I'm afraid that he had less success with teaching me how to bowl.

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l