The fact tables were partitionned with hashing than
range.
The volumetry was "supposed" to be huge and there was
no data purging in the architecture.
The range partitionning was done on a protein batch
number or something like that.
- Jack Silvey <jack_silvey_at_yahoo.com> a écrit : >
interesting ... what was the partition key? Phylum?
> Genus?
>
>
>
> --- paquette stephane <stephane_paquette_at_yahoo.com>
> wrote:
> > We used the date as the key of the time dimension
> > but
> > we were using a time dimension to drive the
> queries.
> >
> >
> > At my last client, I was surprised to see no time
> > dimension in the datawarehouse but I was even more
> > surprised to that there were no date at all in the
> > fact tables of this datawarehouse.
> >
> > It was in a biotech company and the datawarehouse
> > was
> > developped by Oracle Corp.
> >
> > Time was having no meaning for the users
> > (biologists).
> >
> >
> > --- Jack Silvey <jack_silvey_at_yahoo.com> a écrit :
> >
> > Jared / all,
> > >
> > > Agree, date column in the fact is a bad idea.
> > >
> > > I have experience with a warehouse that had a
> fact
> > > table partitioned on a date column. This system
> > does
> > > not use a date dimension, and queries are
> directly
> > > constrained on the date column in the fact.
> > >
> > > You are right, in this system, if you use a date
> > > dimension, you do not get partition elimination.
> > You
> > > can get partition elimination if you use use
> > > partition
> > > key in the WHERE clause, but this setup has two
> > > major
> > > problems: 1) no true star execution, and 2) lack
> > of
> > > flexibility.
> > >
> > > No true star execution: In true star schema
> > > execution
> > > the query visits all the dimension tables first,
> > > makes
> > > a cartesian join of all of the relevant
> dimension
> > > records, and use this dataset to select records
> > from
> > > the fact table. This works since the fact table
> > > visit
> > > is usually the most expensive, and it is cheaper
> > to
> > > do
> > > a cartesian join of dimensions rather than use
> the
> > > fact table as part of the regular query.
> > >
> > > Since the date column is in the fact table, the
> > > enduser must directly constrain queries against
> > the
> > > fact. Therefore, this system does not use the
> STAR
> > > execution plan, since it must use the fact as
> part
> > > of
> > > the normal query and is not able to save it
> until
> > > the
> > > last step. This hampers query performance.
> > >
> > > Poor flexibility: This system is not flexible,
> > since
> > > you cannot store date information other than
> just
> > > the
> > > date. For instance, since there is no date
> > > dimension,
> > > queries can't just look in the dimension table
> and
> > > see
> > > which dates comprise fiscal third quarter. The
> > query
> > > issuer has to hardcode dates into the query,
> > instead
> > > of saying "where datetab.3qflag = 'Y'".
> > >
> > > Kimball talks about direct fact constraint on
> > dates
> > > with an air of disfavor in his book about data
> > > warehousing. I am sure that these two problems
> are
> > > not
> > > the only ones, they are just the first two that
> > leap
> > > to mind.
> > >
> > > Jack
> > >
> > >
> > >
> > > > --- Jared.Still_at_radisys.com wrote:
> > > > > This discussion raises an interesting
> > question.
> > > > >
> > > > > Do your fact tables have a date column in
> > them?
> > > > >
> > > > > If so, how do you correlate that with the
> > > > date/time dimension table?
> > > > >
> > > > > If you use a local date column to control
> the
> > > > partitioning rather
> > > > > than a PK from the date/time dimension, user
> > > > queries based on
> > > > > the date/time dimension won't be able to use
> > > > partition elimination.
> > > > >
> > > > > Personally, I see no need for a date column
> in
> > > the
> > > > fact table.
> > > > >
> > > > > Jared
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > paquette stephane
> > <stephane_paquette_at_yahoo.com>
> > > > > Sent by: root_at_fatcity.com
> > > > > 08/06/2002 08:43 AM
> > > > > Please respond to ORACLE-L
> > > > >
> > > > >
> > > > > To: Multiple recipients of list
> > > > ORACLE-L
> > > > > <ORACLE-L_at_fatcity.com>
> > > > > cc:
> > > > > Subject: Re: Rolling
> partitions
> > > > >
> > > > >
> > > > > I've done that in a datawarehouse system.
> > > > > The fact tables were partitionned by date.
> > > > > Some fact tables were keeping different
> number
> > > of
> > > > > months : 6o, 12, 15,...
> > > > > This was handle in the metadata tables and
> was
> > > > written
> > > > > with PL/SQL (Oracle 8). You can do a lot of
> > > things
> > > > > with PS/SQL.
> > > > > So the metadata was containg the table name,
> > the
> > > > > naming convention of the partition, the
> naming
> > > > > convention for the tablespace, disks name,
> the
> > > > number
> > > > > of partition per table, ...
> > > > > Data was moved into retention data tables
> then
> > > the
> > > > > partition were rolling : creation of new
> > > > tablespaces,
> > > > > creation of new partition, moving data to
> the
> > > > > retention tables, dropping partition,...
> > > > >
> > > > > HTH
> > > > >
> > > > > --- Tracy Rahmlow <tracy.rahmlow_at_aexp.com>
> a
> > > > écrit :
> > > > > > I am finally looking to implement
> > partitioning
> > > > and
> > > > > > have some issues with
> > > > > > rolling partitions by date. (Ie add
> partion
> > > > p0802
> > > > > > and drop p0801) Does
> > > > > > anybody have or know of a generic
> > > > procedure/process
> > > > > > that will allow the
>
=== message truncated ===
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
stephane_paquette_at_yahoo.com
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail :
http://fr.mail.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
INET: stephane_paquette_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 08 2002 - 16:27:16 CDT
