asked on

partitioning an existing table

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'))
);

guinnie

ASKER

Ok . I noticed type, so I replaced sales_year with school_year. I will resubmit new code in a few minutes.

Sujith

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?

awking00

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.

guinnie

ASKER

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')
);

awking00

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.

guinnie

ASKER

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.

awking00

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.

guinnie

ASKER

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 ?

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial