Maintaining field indexes on dynamic read/write

Hi,

 

 

usually the upkeep of indexes is the responsability of the underlying database, not the client software. What output format are you using?

 

 

David

I totally agree with David, but if you are using a db format, you can specify an index at the writer feature type parameters (oracle spatial format > FME 2014 SP3)

Another thought: make sure that you don't drop the existing tables on the writer. Dropping tables will also drop all associated indexes.

 

 

David

Thanks for the replies.

 

 

@David R - While the underlying database technology should maintain the indexes, the intention (by creation) is something that's inherently part of the data itself. My output formats here are Esri Shapefile and MapInfo TAB so, with regards to your follow-up, these are freshly created rather than appended.

 

 

@ETLS-Italy - If I manage by tables one-by-one, that's not a problem as the writer has the ability to set a field to indexed. However, I'm try to this as a dynamic workspace so there's no explicit field control on the writer.

 

 

The only way I can see this working in a dynamic mode is if FME had the option for a writer to copy the indexed fields set-up of a reader.

I now see that my use of the word "maintaining" may have caused some confusion. I'm not talking about the technical implementation of indexes, just the existence of them. I simply want my output tables to have the same indexed fields as my input.

Since you're writing to file-based databases, you will have to manually specify your indexes on the writer. As far as I know, there is no (easy) way to transfer index defintions with FME from one dataset to another.

 

 

David

That's a shame. Writing indexed fields would, to me, seem like a basic requirement to write a complete/ready-to-use output. Losing this when using a dynamic reader-based workflow would seem to be something that could be overcome. The only FME option seems to be individual workflow paths for each layer (there are dozens so I'm not going to do that) so I can have better control of the writers. It seems odd to me that FME cannot be easily used to do a basic function like make geo-clipped samples of bulk TAB and SHP files.

For what it's worth, this has nothing to do with using a dynamic reader, it's just the way it is.

 

 

You could also consider contacting Safe support (https://safecommunity.force.com/knowledgeSubmitCase) with your issue and see if they have some other idea or have existing plans to implement this.

 

 

David

I'm not sure I understand why you say it's nothing to do with the dynamic reader. In a static mode data flow, the writers have index-setting controls. In dynamic mode, the index-settings controls are lost altogether. Have I missed something? I will follow your advice and see if Safe support have any ideas.

Hi Warren,

 

 

I feel your pain.  We're discussing here what we'd have to do to preserve this, and @KevinWiebe has figured out a workaround in the short term...but that only motivates us more to fix it right.  Please do file a case with our support so we can track it but we'll do our best in the meantime.

 

 

Enjoy FOSS4G! Wish I was there with you.

 

 

Dale

Hi, has this since been resolved in latter versions, I am having a similar issue writing to MapInfo. I can see the fme_data_type is fme_varchar(45),Indexed but when I try and use this in my resource file I get an error workspace fails with 2017-12-05 11:30:41| 4.7| 0.0|ERROR |Schema From Table Reader: 'fme_varchar(45),Indexed' is not a valid FME data type. Is there a solution to this?

 

 

This has been resolved when roundtripping from Mapinfo to Mapinfo dynamically. What hasn't quite been sorted is when we are working with different formats and you need to include ',Indexed' inside the schema. The current way to handle this is to manually add in the ',Indexed' text onto the schema at runtime and the writer should be able to handle it.

 

 

I've added a problem report for our developers to take a deeper look and see if we can make this use case work.

 

osalex wrote:

Hi, has this since been resolved in latter versions, I am having a similar issue writing to MapInfo. I can see the fme_data_type is fme_varchar(45),Indexed but when I try and use this in my resource file I get an error workspace fails with 2017-12-05 11:30:41| 4.7| 0.0|ERROR |Schema From Table Reader: 'fme_varchar(45),Indexed' is not a valid FME data type. Is there a solution to this?

 

 

This has been resolved when roundtripping from Mapinfo to Mapinfo dynamically. What hasn't quite been sorted is when we are working with different formats and you need to include ',Indexed' inside the schema. The current way to handle this is to manually add in the ',Indexed' text onto the schema at runtime and the writer should be able to handle it.

 

 

I've added a problem report for our developers to take a deeper look and see if we can make your use case work.