Solved

partitioning an existing table

Posted on 2006-11-28
9
1,300 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
  • 4
  • 4
9 Comments
 

Author Comment

by:guinnie
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
 

Author Comment

by:guinnie
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Accepted Solution

by:
awking00 earned 125 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now