| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Rolling partitions
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
> > > automation of this? I envision a number of
> > > parameters being passed in. For
> > > example, time period & table ... Or is there an
> > > alternative that I need to
> > > consider? (other than manual)
> > >
> > > Thanks
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Tracy Rahmlow
> > > INET: tracy.rahmlow_at_aexp.com
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> > >
> >
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: jack_silvey_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 Wed Aug 07 2002 - 11:22:17 CDT
 |
 |