I am intypical situation , there is table of 3 TB and the table is not partitation yet.
After looking at data in table, I came to conclussion partition on date is best fit.
but here is the problem:-
CREATE TABLE ORDERS
PRIMARY INDEX PIE(ENO)
CREAT_TS IS NOT DEFINED IN PRIMARY INDEX, upto my knowledge unless we have that field in PI , I can not create the partation.
I create a temp table
CREATE TABLE ORDERS
PRIMARY INDEX PIE(ORNUM,CREAT_TS)
PARTITION BY RANGE_N(CAST(CREAT_TS AS DATE) BETWEEN '2005-01-01' AND '2013-02-10' EACH INTERVAL '1' MONTH)
TRYING TO insert that data into temp table , but it is failing due to spool, so i decided to move year wise data like -CREAT_TS <= '2011-09-23 13:35:57.83' .
can some help , can i partition main table directly .. i tried with alter statments but it is not working ..
Please consider your primary index again.
Partitioning columns do not have to be columns in the primary index. If they aren't, then the primary index cannot be unique.
1) If you will be joining your order table with other table based on ornum then let the PI be ornum and let it be nupi, and partition it by creat_ts.
2) If the temp table and order table has the same PI then data of both tables will be on hte same AMP and it will not spool out.
I don't think there is any way to partiton the table without creating a new temp table.
Limitations with ALTER TABLE:
Could you please explain on your comment. As if in any case we have to have the table empty then there is no use of these workarounds. Please confirm the steps.
I have created the table with a Non unique primary index and then i inserted one row. Afterwards i defined one secondary index(USI) then the alter command to change it to Unique primary index. It gives me error that the table has some data
Thank you Dieter! SImple and elegant :)
mmongia08 what is to explain, just create a UNIQUE secondary index on the same column(s) as the PI