asked on

Oracle partition by range

Hi Team ,

I need to create a table partition which can store the employees who have joined in a given month and year
Can I achieve it using range partition
Iam trying the below command.
Create table emp_part1
(
employee_id number,
hire_date date
)
PARTITION BY RANGE(hire_date)

(
PARTITION pos_data_p2 VALUES LESS THAN (TO_date(HIRE_DATE', 'MM-YYYY')),
PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE(HIRE_DATE, 'MM-YYYY'))
);

Actually i want to create partitions like below

MONTH-YEAR
JAN-2007
FEB-2007

create partitions like above and store the records , but iam getting an error

arnold

I am unsure what it is you are doing. Commonly you would have a table that has over the years grown with many records and you would to speed up processing, partition it based on a criteria as you mention, you can partition data based on hire date.

See REF on partitioning tables https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm

Your partitioning directive should actually set the range explicitly that the partition will contain, you do not seem to set the date
pos_data_p2 is less than 'JAN-2007' instead of the column name in your example HIRE_DATE

You might want to create a loop, that will go through years, months, to create the partitions on a monthly basis.

you also seem to have an extra quote in the first HIRE_DATE...

Without knowing the data, it is not clear to be the benefit you will see since it is unlikely that you have huge tables of people churn hired/leave/terminated

HainKurt

how many billions of records do you have in this table?

i suggest, just create a function index

create index ix_hiredate

CREATE INDEX ix_hiredate ON emp (to_char(hire_date, 'YYYYMM'));

Open in new window

and you should be ok, just use same function when querying your data to be able to use the index...

Geert G

you typically wouldn't setup partitioning for an employees table

there is only 7.5 billion people, and i'm sure not all of them work at your company
especially the kids and elderly/retired people

you might want to setup partitioning on the daily activity of all those employees
but that's not gonna be all that much either

you have chosen a very odd case for partitioning ...
unless this is for learning ?

awking00

What is the data type of your hiredate field?

slightwv (䄆 Netminder)

>>What is the data type of your hiredate field?

pssssttttt...... The create table statement was provided? ;)

Swaminathan K

ASKER

its date field. Sorry for the late reply

Swaminathan K

ASKER

here iam just providing the sample table as employees, on real scenario its a different table sales_order table. kindly consider employees table and do the needful.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

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

Start Free Trial

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

slightwv (䄆 Netminder)

>>You might find it convenient to make your partition names a little more meaningful

For most all other partitioning types, I agree. Interval partitioning will automatically create new partitions as the data dictates. You have to remember to pre-create ALL possible partitions or rename them after the system generates them.

Swaminathan K

ASKER

thanks a lot. u guys are awesome