Re: Interesting question for Oracle Gurus (partitioned tables)

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Thu, 24 Sep 1998 12:44:36 GMT
Message-ID: <360a37ed.3546339@newshost.us.oracle.com>

On Thu, 24 Sep 1998 13:28:53 +0200, jmorales <jmorales_at_dycsa.es> wrote:

>Hi:
>
>I'm reading all i find about partitioned tables and i can't find out the
>solution.
>
>The problem is:
>
>I have to create a partitioned table by a date field, but i want to make
>one partition for each day i the month (only 31 partitions forever).
>Well, i can't find the way to extract the day of a date field in the
>creation script for the table.
>
>I have to use only a date field to do the partition. I cannot use a new
>field for the day of the month.
>

When creating a parition, you are only allowed to put in a column list. But this could be easily solved by adding another column to your table which will contain the day of the month value. You can compute the value of this day from the date via a trigger, and then all you need to do is create a trigger which will compute the day.

SQL> CREATE TABLE foo (id NUMBER, thedate DATE, theday NUMBER) 2 PARTITION BY RANGE (theday)
3 (PARTITION part1 VALUES LESS THAN (2) TABLESPACE tsa, 4 PARTITION part2 VALUES LESS THAN (3) TABLESPACE tsb);

Table created.

SQL> CREATE OR REPLACE TRIGGER foo_biu
  2 BEFORE INSERT OR UPDATE OF thedate ON foo   3 FOR EACH ROW
  4 BEGIN
  5 :new.theday := TO_NUMBER( TO_CHAR( :new.thedate, 'DD'));   6 END;
  7 /

Trigger created.

SQL> INSERT INTO foo (id, thedate) VALUES (1000, '01-JAN-1998');

1 row created.

SQL> INSERT INTO foo (id, thedate) VALUES (2000, '02-JAN-1998');

1 row created.

SQL> INSERT INTO foo (id, thedate) VALUES (3000, '02-FEB-1998');

1 row created.

SQL> COMMIT; Commit complete.

SQL> SELECT ROWID, id FROM foo;

ROWID                      ID

------------------ ----------

AAAAuHAALAAAAADAAA       1000

AAAAuIAAMAAAAADAAA       2000

AAAAuIAAMAAAAADAAB       3000

>Thank's a lot for the answer, please if were possible to my e-mail
>account
>jmorales_at_dycsa.es
>
>Bye.
>;-)
>

Thanks!

Joel

Joel R. Kallman Oracle Government, Education, & Health

Columbus, OH                             http://govt.us.oracle.com

jkallman@us.oracle.com                   http://www.oracle.com

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Thu Sep 24 1998 - 07:44:36 CDT