Re: Load and Query question
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: shreeni@xxxxxxxxxxxxx
- Date: Thu, 27 Apr 2006 11:44:58 -0400
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
