Adding a local index to an already partitioned table

Posted by viswanath.sekar on 10-Jun-2014 08:26

Hi,

I wish to add a new local index to an already partitioned table. In the df i specify the below syntax...but it is not loading and is throwing out errors...index field nnumber is the key on which the table has been partitioned...

CREATE INDEX "TEST_EFFDATE" ON "FIELDHST"
PRIMARY
IS-LOCAL "TRUE"
INDEX-FIELD "NNUMBER" ASCENDING
INDEX-FIELD "EFFDATE" DESCENDING
INDEX-FIELD "TRANTIME" DESCENDING
FOR PARTITION fieldhstpartition-1-1 USING INDEX AREA "FH_HTP_IDX_01"
FOR PARTITION fieldhstpartition-1-2 USING INDEX AREA "FH_HTP_IDX_02"
FOR PARTITION fieldhstpartition-1-3 USING INDEX AREA "FH_HTP_IDX_03"
FOR PARTITION fieldhstpartition-1-4 USING INDEX AREA "FH_HTP_IDX_04"
FOR PARTITION fieldhstpartition-1 USING INDEX AREA "FH_DATA_04"

the error that is thrown is ..

**Error during ADD INDEX TEST_EFFDATE**

**Line 7 FOR PARTITION fieldhstpartition-1-1 USING INDEX AREA FH_HTP_IDX_01

Unknown INDEX keyword

Thanks,

Viswanath Sekar 

OpenEdge RDBMS - Forum
Posted by Håvard Danielsen on 10-Jun-2014 09:08

This is wrong syntax if this is a .df loaded with DataAdmin or OEM.    
 
FOR PARTITION is not a valid keyword in .df.  IS-LOCAL is the only extra keyword needed to make a local index. If a policy exists on the table the partitions will be created according to the policy default areas and  allocation rule.   
 
 
[collapse]
From: viswanath.sekar [mailto:bounce-viswanathsekar@community.progress.com]
Sent: Tuesday, June 10, 2014 9:27 AM
To: Beta.OE11.4@community.progress.com
Subject: [Beta - OE11.4] Adding a local index to an already partitioned table
 
Thread created by viswanath.sekar

Hi,

I wish to add a new local index to an already partitioned table. In the df i specify the below syntax...but it is not loading and is throwing out errors...index field nnumber is the key on which the table has been partitioned...

CREATE INDEX "TEST_EFFDATE" ON "FIELDHST"
PRIMARY
IS-LOCAL "TRUE"
INDEX-FIELD "NNUMBER" ASCENDING
INDEX-FIELD "EFFDATE" DESCENDING
INDEX-FIELD "TRANTIME" DESCENDING
FOR PARTITION fieldhstpartition-1-1 USING INDEX AREA "FH_HTP_IDX_01"
FOR PARTITION fieldhstpartition-1-2 USING INDEX AREA "FH_HTP_IDX_02"
FOR PARTITION fieldhstpartition-1-3 USING INDEX AREA "FH_HTP_IDX_03"
FOR PARTITION fieldhstpartition-1-4 USING INDEX AREA "FH_HTP_IDX_04"
FOR PARTITION fieldhstpartition-1 USING INDEX AREA "FH_DATA_04"

the error that is thrown is ..

**Error during ADD INDEX TEST_EFFDATE**

**Line 7 FOR PARTITION fieldhstpartition-1-1 USING INDEX AREA FH_HTP_IDX_01

Unknown INDEX keyword

Thanks,

Viswanath Sekar 

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by Fernando Souza on 10-Jun-2014 08:50

Hi

The issue is that FOR PARTITION is not supported syntax. Remove those lines and you should be all set. The client will automatically add the partitions for the index when the new index is added. The area for each index partition will be the default area defined for each partition in the partition policy details.

All Replies

Posted by Fernando Souza on 10-Jun-2014 08:50

Hi

The issue is that FOR PARTITION is not supported syntax. Remove those lines and you should be all set. The client will automatically add the partitions for the index when the new index is added. The area for each index partition will be the default area defined for each partition in the partition policy details.

Posted by Håvard Danielsen on 10-Jun-2014 09:08

This is wrong syntax if this is a .df loaded with DataAdmin or OEM.    
 
FOR PARTITION is not a valid keyword in .df.  IS-LOCAL is the only extra keyword needed to make a local index. If a policy exists on the table the partitions will be created according to the policy default areas and  allocation rule.   
 
 
[collapse]
From: viswanath.sekar [mailto:bounce-viswanathsekar@community.progress.com]
Sent: Tuesday, June 10, 2014 9:27 AM
To: Beta.OE11.4@community.progress.com
Subject: [Beta - OE11.4] Adding a local index to an already partitioned table
 
Thread created by viswanath.sekar

Hi,

I wish to add a new local index to an already partitioned table. In the df i specify the below syntax...but it is not loading and is throwing out errors...index field nnumber is the key on which the table has been partitioned...

CREATE INDEX "TEST_EFFDATE" ON "FIELDHST"
PRIMARY
IS-LOCAL "TRUE"
INDEX-FIELD "NNUMBER" ASCENDING
INDEX-FIELD "EFFDATE" DESCENDING
INDEX-FIELD "TRANTIME" DESCENDING
FOR PARTITION fieldhstpartition-1-1 USING INDEX AREA "FH_HTP_IDX_01"
FOR PARTITION fieldhstpartition-1-2 USING INDEX AREA "FH_HTP_IDX_02"
FOR PARTITION fieldhstpartition-1-3 USING INDEX AREA "FH_HTP_IDX_03"
FOR PARTITION fieldhstpartition-1-4 USING INDEX AREA "FH_HTP_IDX_04"
FOR PARTITION fieldhstpartition-1 USING INDEX AREA "FH_DATA_04"

the error that is thrown is ..

**Error during ADD INDEX TEST_EFFDATE**

**Line 7 FOR PARTITION fieldhstpartition-1-1 USING INDEX AREA FH_HTP_IDX_01

Unknown INDEX keyword

Thanks,

Viswanath Sekar 

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by viswanath.sekar on 10-Jun-2014 09:13

Thank you for all for your inputs. It is working now.

Regards

Viswanath Sekar

This thread is closed