Solved

partitioning an existing table

Posted on 2006-11-28
9
1,409 Views
Last Modified: 2008-01-09
I have an existing table I would like to partition by date. I work in an educational system so I am thinking of partition
by school year (range) and then create another partition by school code (list).

My questions are:
1)  does the partition need to have all the columns of the table it is partitioned from
2)  to load the table - do I use the 'insert' statement

Below is the create partition sql. When I run, I get an ERROR at line 44:
ORA-14036: partition bound value too large for column.
Please advise.

CREATE TABLE student_enrolments_range
(
SCHOOL_CODE                                VARCHAR2(8) NOT NULL,
 SCHOOL_YEAR                               VARCHAR2(8) NOT NULL,
 PERSON_ID                                  VARCHAR2(13) NOT NULL,
 TRANSACTION_TYPE_IND                       NUMBER(1) NOT NULL,
 TRANSACTION_DATE_TIME                      DATE NOT NULL,
 SCHOOL_YEAR_TRACK                                  VARCHAR2(8),
 EFFECTIVE_DATE                             DATE NOT NULL,
 STUDENT_NO                                 VARCHAR2(9) NOT NULL,
 REGISTER_CODE                                      VARCHAR2(8),
 GRADE                                              VARCHAR2(2),
 PREVIOUS_LANGUAGE_OF_INSTRUCT                      VARCHAR2(16),
 ENTRY_PREVIOUS_SCHOOL_BSID                         VARCHAR2(13),
 ENTRY_TYPE_NAME                                    VARCHAR2(16),
 ENTRY_PROVINCE_STATE_CODE                          VARCHAR2(2),
 ENTRY_COUNTRY_NAME                                 VARCHAR2(16),
 DEMIT_REASON_NAME                                  VARCHAR2(16),
 DEMIT_NEXT_SCHOOL_BSID                             VARCHAR2(13),
 DEMIT_POST_SECONDARY_ID                            VARCHAR2(13),
 DEMIT_PROVINCE_STATE_CODE                          VARCHAR2(2),
 DEMIT_COUNTRY_NAME                                 VARCHAR2(16),
 FUNDING_SOURCE_TYPE                                VARCHAR2(8),
 FUNDING_PAYER_TYPE                                 VARCHAR2(1),
 FUNDING_ORGANIZATION_ID                            VARCHAR2(13),
 FUNDING_PERSON_ID                                  VARCHAR2(13),
 FUNDING_RESIDENT_FLAG                              VARCHAR2(1),
 FUNDING_STATUS_INDICATOR                           VARCHAR2(8),
 REMARK                                             VARCHAR2(240),
 ACTIVE_FLAG                                VARCHAR2(1) NOT NULL,
 TRANSACTION_CREATOR_UID                    VARCHAR2(20) NOT NULL,
 CANCELLATION_DATE_TIME                             DATE,
 CANCELLATION_UID                                   VARCHAR2(20),
 AVERAGE_DAILY_MOI                                  NUMBER(3),
 LAST_UPDATE_FUNCTION                               VARCHAR2(40),
 LAST_UPDATE_DATE_TIME                      DATE NOT NULL,
 LAST_UPDATE_UID                            VARCHAR2(20),
 SCHOOL_BSID                                VARCHAR2(13) NOT NULL,
 DEMIT_NEXT_SCHOOL_CODE                             VARCHAR2(8),
 REPLICATE_DOMAINS                                  VARCHAR2(45),
 HOME_SCHOOL_CODE                                   VARCHAR2(8),
 SHARED_FLAG                                        VARCHAR2(1))
PARTITION BY RANGE(school_year)
(partition school_year_19921993 VALUES LESS THAN(TO_DATE('06/30/1993','MM/DD/YYYY')),
PARTITION sales_year_19931994 VALUES LESS THAN(TO_DATE('06/30/1994','MM/DD/YYYY')),
PARTITION sales_year_19941995 VALUES LESS THAN(TO_DATE('06/30/1995','MM/DD/YYYY')),
PARTITION sales_year_19951996 VALUES LESS THAN(TO_DATE('06/30/1996','MM/DD/YYYY')),
PARTITION sales_year_19961997 VALUES LESS THAN(TO_DATE('06/30/1997','MM/DD/YYYY')),
PARTITION sales_year_19971998 VALUES LESS THAN(TO_DATE('06/30/1998','MM/DD/YYYY')),
PARTITION sales_year_19981999 VALUES LESS THAN(TO_DATE('06/30/1999','MM/DD/YYYY')),
PARTITION sales_year_19992000 VALUES LESS THAN(TO_DATE('06/30/2000','MM/DD/YYYY')),
PARTITION sales_year_20002001 VALUES LESS THAN(TO_DATE('06/30/2001','MM/DD/YYYY')),
PARTITION sales_year_20012002 VALUES LESS THAN(TO_DATE('06/30/2002','MM/DD/YYYY')),
PARTITION sales_year_20022003 VALUES LESS THAN(TO_DATE('06/30/2003','MM/DD/YYYY')),
PARTITION sales_year_20032004 VALUES LESS THAN(TO_DATE('06/30/2004','MM/DD/YYYY')),
PARTITION sales_year_20042005 VALUES LESS THAN(TO_DATE('06/30/2005','MM/DD/YYYY')),
PARTITION sales_year_20052006 VALUES LESS THAN(TO_DATE('06/30/2006','MM/DD/YYYY')),
PARTITION sales_year_20062007 VALUES LESS THAN(TO_DATE('06/30/2007','MM/DD/YYYY'))
);
0
Comment
Question by:guinnie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 

Author Comment

by:guinnie
ID: 18028500
Ok . I noticed type, so I replaced sales_year with school_year. I will resubmit new code in a few minutes.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18028571
well,
here SCHOOL_YEAR  is VARCHAR2(8)
and in the partition clause you are putting "VALUES LESS THAN(TO_DATE('06/30/1993','MM/DD/YYYY')),"

It requires the school_year column to be of type "DATE"

What is the format in which you are storing values in school_year column?
0
 
LVL 32

Expert Comment

by:awking00
ID: 18028736
With school_year being varchar2(8), you are going to have problems if the format of these eight characters is MMDDYYYY. For example 06301994 is less than 11301993, which is obviously not what you want. You either need to make the school_year attribute a true date datatype or format the characters as YYYYMMDD, which avoids the issue I just discussed. With a change to date datatype, your partitioning should work as written. With formatting the eight character field as YYYYMMDD, the partitioning would simply take the form "VALUES LESS THAN (19930630), for example.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:guinnie
ID: 18028782
Yes, you are correct and this is the new sql. Our school_year column is not defined as a date (strangely enough) but the reason is the format is like this: 20052006, 20062007 etc.

So now I have created the partition correctly. How do I get data into it ?
Also, I am assuming each partition will consume data space ??

CREATE TABLE student_enrolments_range
(
SCHOOL_CODE                                VARCHAR2(8) NOT NULL,
 SCHOOL_YEAR                               VARCHAR2(8) NOT NULL,
 PERSON_ID                                  VARCHAR2(13) NOT NULL,
 TRANSACTION_TYPE_IND                       NUMBER(1) NOT NULL,
 TRANSACTION_DATE_TIME                      DATE NOT NULL,
 SCHOOL_YEAR_TRACK                                  VARCHAR2(8),
 EFFECTIVE_DATE                             DATE NOT NULL,
 STUDENT_NO                                 VARCHAR2(9) NOT NULL,
 REGISTER_CODE                                      VARCHAR2(8),
 GRADE                                              VARCHAR2(2),
 PREVIOUS_LANGUAGE_OF_INSTRUCT                      VARCHAR2(16),
 ENTRY_PREVIOUS_SCHOOL_BSID                         VARCHAR2(13),
 ENTRY_TYPE_NAME                                    VARCHAR2(16),
 ENTRY_PROVINCE_STATE_CODE                          VARCHAR2(2),
 ENTRY_COUNTRY_NAME                                 VARCHAR2(16),
 DEMIT_REASON_NAME                                  VARCHAR2(16),
 DEMIT_NEXT_SCHOOL_BSID                             VARCHAR2(13),
 DEMIT_POST_SECONDARY_ID                            VARCHAR2(13),
 DEMIT_PROVINCE_STATE_CODE                          VARCHAR2(2),
 DEMIT_COUNTRY_NAME                                 VARCHAR2(16),
 FUNDING_SOURCE_TYPE                                VARCHAR2(8),
 FUNDING_PAYER_TYPE                                 VARCHAR2(1),
 FUNDING_ORGANIZATION_ID                            VARCHAR2(13),
 FUNDING_PERSON_ID                                  VARCHAR2(13),
 FUNDING_RESIDENT_FLAG                              VARCHAR2(1),
 FUNDING_STATUS_INDICATOR                           VARCHAR2(8),
 REMARK                                             VARCHAR2(240),
 ACTIVE_FLAG                                VARCHAR2(1) NOT NULL,
 TRANSACTION_CREATOR_UID                    VARCHAR2(20) NOT NULL,
 CANCELLATION_DATE_TIME                             DATE,
 CANCELLATION_UID                                   VARCHAR2(20),
 AVERAGE_DAILY_MOI                                  NUMBER(3),
 LAST_UPDATE_FUNCTION                               VARCHAR2(40),
 LAST_UPDATE_DATE_TIME                      DATE NOT NULL,
 LAST_UPDATE_UID                            VARCHAR2(20),
 SCHOOL_BSID                                VARCHAR2(13) NOT NULL,
 DEMIT_NEXT_SCHOOL_CODE                             VARCHAR2(8),
 REPLICATE_DOMAINS                                  VARCHAR2(45),
 HOME_SCHOOL_CODE                                   VARCHAR2(8),
 SHARED_FLAG                                        VARCHAR2(1))
PARTITION BY RANGE(school_year)
(
PARTITION school_year_19992000 VALUES less than ('19992000'),
PARTITION school_year_20002001 VALUES less than ('20002001'),
PARTITION school_year_20012002 VALUES less than ('20012002'),
PARTITION school_year_20022003 VALUES less than ('20022003'),
PARTITION school_year_20032004 VALUES less than ('20032004'),
PARTITION school_year_20042005 VALUES less than ('20042005'),
PARTITION school_year_20052006 VALUES less than ('20052006'),
PARTITION school_year_20062007 VALUES less than ('20062007')
);
0
 
LVL 32

Expert Comment

by:awking00
ID: 18029509
Will you actually have values that are less than ...?
I would think that perhaps school_year might always be a four-character beginning year followed by a four_character ending year. If so, the values would always be part of a list and you should probably use a list partition.
CREATE TABLE
...
PARTITION BY LIST (school_year)
(PARTITION 19992000 VALUES ('19992000')
,PARTITION 20002001 VALUES ('20002001')
,PARTITION 20012002 VALUES ('20012002')
,PARTITION 20022003 VALUES ('20022003')
,PARTITION 20032004 VALUES ('20032004')
,PARTITION 20042005 VALUES ('20042005')
,PARTITION 20052006 VALUES ('20052006')
,PARTITION 20062007 VALUES ('20062007'));

Also, the data will automatically be stored in the partition where the value designated for that partition equals the data value for the school_year attribute.
0
 

Author Comment

by:guinnie
ID: 18029913
Awking:

   Ok ! I recreated using your suggestion to define as a list using values clause. However, when I do a select to see
data there aren't any rows returned. I guess I do not understand how it knows to partition on the specific table I need it to use. i.e. student_enrolments table. It is not specified in sql - so how does it know to use that table ???

SQL> SELECT * FROM student_enrolments_range PARTITION (school_year_19992000);

no rows selected

SQL> SELECT * FROM student_enrolments_range PARTITION (school_year_20002001);

no rows selected


SQL> CREATE TABLE student_enrolments_range
  2  (
  3  SCHOOL_CODE                                VARCHAR2(8) NOT NULL,
  4   SCHOOL_YEAR                               VARCHAR2(8) NOT NULL,
  5   PERSON_ID                                  VARCHAR2(13) NOT NULL,
  6   TRANSACTION_TYPE_IND                       NUMBER(1) NOT NULL,
  7   TRANSACTION_DATE_TIME                      DATE NOT NULL,
  8   SCHOOL_YEAR_TRACK                                  VARCHAR2(8),
  9   EFFECTIVE_DATE                             DATE NOT NULL,
 10   STUDENT_NO                                 VARCHAR2(9) NOT NULL,
 11   REGISTER_CODE                                      VARCHAR2(8),
 12   GRADE                                              VARCHAR2(2),
 13   PREVIOUS_LANGUAGE_OF_INSTRUCT                      VARCHAR2(16),
 14   ENTRY_PREVIOUS_SCHOOL_BSID                         VARCHAR2(13),
 15   ENTRY_TYPE_NAME                                    VARCHAR2(16),
 16   ENTRY_PROVINCE_STATE_CODE                          VARCHAR2(2),
 17   ENTRY_COUNTRY_NAME                                 VARCHAR2(16),
 18   DEMIT_REASON_NAME                                  VARCHAR2(16),
 19   DEMIT_NEXT_SCHOOL_BSID                             VARCHAR2(13),
 20   DEMIT_POST_SECONDARY_ID                            VARCHAR2(13),
 21   DEMIT_PROVINCE_STATE_CODE                          VARCHAR2(2),
 22   DEMIT_COUNTRY_NAME                                 VARCHAR2(16),
 23   FUNDING_SOURCE_TYPE                                VARCHAR2(8),
 24   FUNDING_PAYER_TYPE                                 VARCHAR2(1),
 25   FUNDING_ORGANIZATION_ID                            VARCHAR2(13),
 26   FUNDING_PERSON_ID                                  VARCHAR2(13),
 27   FUNDING_RESIDENT_FLAG                              VARCHAR2(1),
 28   FUNDING_STATUS_INDICATOR                           VARCHAR2(8),
 29   REMARK                                             VARCHAR2(240),
 30   ACTIVE_FLAG                                VARCHAR2(1) NOT NULL,
 31   TRANSACTION_CREATOR_UID                    VARCHAR2(20) NOT NULL,
 32   CANCELLATION_DATE_TIME                             DATE,
 33   CANCELLATION_UID                                   VARCHAR2(20),
 34   AVERAGE_DAILY_MOI                                  NUMBER(3),
 35   LAST_UPDATE_FUNCTION                               VARCHAR2(40),
 36   LAST_UPDATE_DATE_TIME                      DATE NOT NULL,
 37   LAST_UPDATE_UID                            VARCHAR2(20),
 38   SCHOOL_BSID                                VARCHAR2(13) NOT NULL,
 39   DEMIT_NEXT_SCHOOL_CODE                             VARCHAR2(8),
 40   REPLICATE_DOMAINS                                  VARCHAR2(45),
 41   HOME_SCHOOL_CODE                                   VARCHAR2(8),
 42   SHARED_FLAG                                        VARCHAR2(1))
 43  PARTITION BY list(school_year)
 44  (
 45  PARTITION school_year_19992000 VALUES  ('19992000'),
 46  PARTITION school_year_20002001 VALUES  ('20002001'),
 47  PARTITION school_year_20012002 VALUES    ('20012002'),
 48  PARTITION school_year_20022003 VALUES    ('20022003'),
 49  PARTITION school_year_20032004 VALUES    ('20032004'),
 50  PARTITION school_year_20042005 VALUES    ('20042005'),
 51  PARTITION school_year_20052006 VALUES    ('20052006'),
 52  PARTITION school_year_20062007 VALUES    ('20062007')
 53  );

Table created.

   
0
 
LVL 32

Expert Comment

by:awking00
ID: 18030129
The table was created with just a table structure. It needs to be populated by some method such as using a load utility like sql*loader or through an insert select from another table. An example of the latter method would be

create table student_enrollments_range
(school_code
,school_year
....)
PARTITION BY LIST(school_year)
(PARTITION school_year_19992000 VALUES  ('19992000'),
 PARTITION school_year_20002001 VALUES  ('20002001'),
 PARTITION school_year_20012002 VALUES    ('20012002'),
 PARTITION school_year_20022003 VALUES    ('20022003'),
 PARTITION school_year_20032004 VALUES    ('20032004'),
 PARTITION school_year_20042005 VALUES    ('20042005'),
 PARTITION school_year_20052006 VALUES    ('20052006'),
 PARTITION school_year_20062007 VALUES    ('20062007')
 )
AS SELECT
 school_code
,school_year
,...
FROM student_enrollments;

The values for school_year in the source table, student_enrollments, will determine the partition in which partition they will be stored for the partitioned student_enrollments_range table.
0
 

Author Comment

by:guinnie
ID: 18030321
Great ! Two more questions:

1) Do partitions consume additional disk storage ?

2) I want to further partition using school_code. Would this be best done by adding hash partition or should I recreate using composite partition ?
0
 
LVL 32

Accepted Solution

by:
awking00 earned 125 total points
ID: 18030956
The difference in storage space between partitioned and not-partitioned tables is negligible assuming the partitions are created on a reasonably even distribution basis. To further partition requires subpartitioning and I would probably do that on a hash basis (which tends to promote even distribution). The number of subpartitions depends on a number of factors (e.g. block size, number and size of records, etc.) but is usually set to some exponential of 2.

PARTITION BY LIST(school_year)
SUBPARTITION BY HASH(school_code)
SUBPARTITIONS 16
(PARTITION school_year_19992000 VALUES  ('19992000'),
 PARTITION school_year_20002001 VALUES  ('20002001'),
 PARTITION school_year_20012002 VALUES    ('20012002'),
 PARTITION school_year_20022003 VALUES    ('20022003'),
 PARTITION school_year_20032004 VALUES    ('20032004'),
 PARTITION school_year_20042005 VALUES    ('20042005'),
 PARTITION school_year_20052006 VALUES    ('20052006'),
 PARTITION school_year_20062007 VALUES    ('20062007')
 )
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question