One of our client running a Charity School and want to manage the Data which becoming huge.
They want to manage the size of their table which contains daily activities related to each student, they are asking to break tables year-wise and I have been instructed to manage it Partition wise.
I have not yet used Oracle Partition feature and want help for how to's. i searched and have following to alter existing table to a Partitioned table:
-- note: below is just to show how the actual table has been created (excluded Partition By Range claue) now want to ALTER
ALTER TABLE student_activities (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 999999999999 INCREMENT BY 1 NOT NULL ENABLE,
student_id NUMBER, -- Foreign Key
teacher_id NUMBER, -- Foreign Key
activity_id NUMBER, -- Foreign Key
activity_date DATE DEFAULT TO_DATE(SYSDATE),
is_completed CHAR(1) CHECK(is_completed IN('Y', 'N')),
activity_status CHAR(1) CHECK(activity_status IN('1', '0')),
admin_id number -- Foreign Key
)
PARTITION BY RANGE (activity_date)
( PARTITION activity_yr_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy'))
, PARTITION activity_yr_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','dd-MON-yyyy'))
, PARTITION activity_yr_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','dd-MON-yyyy'))
, PARTITION activity_yr_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','dd-MON-yyyy'))
, PARTITION activity_yr_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023','dd-MON-yyyy'))
, PARTITION activity_yr_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','dd-MON-yyyy'))
);
is it ok?
Please advice for a better approach to alter existing table and effectively use it to perform DML operations and reporting tasks with some example or a Link. and please for creating index(es) too.
regards
EDIT: to include XE after 21c in subject of the topic