UPDATE 06/01/25: It does indeed appear that once created, the index is automatically rebuilt on writing to the GDB with truncate existing.
Steps to recreate:
- Add SQL statement to create attribute index after write
CREATE INDEX <name_INDEX> ON <table_name> (<attribute_name>)- Run the writer either to create new GDB or update existing GDB
- Remove SQL statement
- Continue to run writer as normal going forwards
-----------------------------
Hi all,
This is driving me crazy trying to figure out. I have a GDB writer that builds an attribute index after write.
- CREATE INDEX <name_INDEX> ON <table_name> (<attribute_name>)
Unbeknownst to me, each time I updated the data, an additional index was being created instead of overwritten which led to a bloated GDB and blown out write times. Note: The GDB table handling is set to truncate before write.
I first went to using the below statement, but I was unsuccessful.
- ALTER INDEX <name_INDEX> ON <table_name> REBUILD
The next attempts were focussed on dropping the index before write and creating it anew afterwards.
My problem is that no matter which format I use, FME errors with ‘An invalid SQL statement was used’. I believe the issue could be with the INDEX parameter because it isn’t highlighted by the syntax highlighter and leaving it out provides a useful error: 'An expected Field was not found or could not be retrieved properly.
- ALTER TABLE <table_name> DROP <name_INDEX>.
Here are the statement formats I’ve tried:
- DROP INDEX <name_INDEX> ON <table_name>
- DROP INDEX <name_INDEX> ON <table_name>(<attribute_name>)
- DROP INDEX <name_INDEX> ON <table_name>.<attribute_name>
- DROP INDEX <table_name>.<name_INDEX>
- DROP INDEX <table_name>(<name_INDEX)
- ALTER <table_name> DROP INDEX <name_INDEX>
- including the above variations of ( ) and dot notation on the table name
- I’ve also inserted IF EXISTS before and after the DROP INDEX command to no effect.
Does anyone know how I can achieve this? Any help is much appreciated.