-
Change Partiton Scheme
I'm using Oracle 11.2.0.3.0 and I need to change the partioning scheme on a table from a date field to a timestamp field.
The sql for our old partition scheme is:
Code:
partition by range ("e_date") interval (numtodsinterval(7, 'DAY'))
(partition "t_01" values less than (to_date(' 2001-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
The sql for the new partiton scheme is:
Code:
partition range ("l_date") interval (numtodsinterval(7, 'DAY'))
(partition "t_01" values less than (timestamp ' 2001-01-07 00:00:00'))
Is this OK? In testing it seems to work but after reading the docs I'm still a bit confused on partitioning. Does anyone have any suggestions?
Thanks,
CG
-
Originally Posted by cyclegeek
. . . E t c . . .
Code:
partition range ("l_date") interval (numtodsinterval(7, 'DAY'))
(partition "t_01" values less than (timestamp ' 2001-01-07 00:00:00'))
Is this OK? In testing it seems to work but after reading the docs I'm still a bit confused on partitioning. Does anyone have any suggestions?
This: '2001-01-07 00:00:00' is not a full timestamp format but rather a date format, why change?
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
As LkBrown mentioned, the part that does the partitioning is "interval (numtodsinterval(7, 'DAY'))" and that is creating a new partition every 7 days. You can also create a new partition every day, which isn't as bad as it sounds, given that you will have on average 3,653 partitions created over 10 years. If you have a large table and you routinely query for the last day, it can help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|