Skip to main content
Question

How to format DROP INDEX on GDB in Writer?

  • November 22, 2024
  • 3 replies
  • 35 views

whichwayisnorth
Contributor
Forum|alt.badge.img+2

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.

3 replies

whichwayisnorth
Contributor
Forum|alt.badge.img+2

Maybe I’ve misunderstood attribute indices on FGDBs. Can anyone comment on whether I even need to rebuild the index when I truncate and repopulate the dataset, or do I only need to create the index once and each time I repopulate the FGDB, the index will be automatically rebuilt? Thanks.


nielsgerrits
VIP
Forum|alt.badge.img+52

I have no sample, but I would try to do this using a PythonCaller and arcpy. With the AI assistant in the PythonCaller it should be fairly easy to generate code for something like that.


whichwayisnorth
Contributor
Forum|alt.badge.img+2

Thanks, I’ll give that a go if a user reports the speed of searching on the indexed attribute has worsened since last update.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings