Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g78G7SE26976 for ; Thu, 8 Aug 2002 12:07:28 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id JAA18724; Thu, 8 Aug 2002 09:07:25 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 004AF2E2; Thu, 08 Aug 2002 08:43:32 -0800 Message-ID: Date: Thu, 08 Aug 2002 08:43:32 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Jack Silvey Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Jack Silvey Subject: Re: Rolling partitions Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-MIME-Autoconverted: from 8bit to quoted-printable by newsfeed.cts.com id JAA18724 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by naude.co.za id g78G7SE26976 interesting ... what was the partition key? Phylum? Genus? --- paquette stephane 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 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@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 > > > > > Sent by: root@fatcity.com > > > > 08/06/2002 08:43 AM > > > > Please respond to ORACLE-L > > > > > > > > > > > > To: Multiple recipients of list > > > ORACLE-L > > > > > > > > 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 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@aexp.com > > > > > > > > > > Fat City Network Services -- (858) > 538-5051 > > > > > FAX: > > > > > (858) 538-5051 > > > > > San Diego, California -- Public > > Internet > > > > > access / Mailing Lists > > > > > > > > > > > > > > > === message truncated === __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: jack_silvey@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@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).