ArcGIS Pro 3.2.1; File Geodatabase
I have a table that doesn't have any attribute indexes (other than the default ObjectID index):
And I have a database view:
select * from species_records inner join (select t_species, max(t_date) as t_date from species_records group by t_species) l on species_records.t_species = l.t_species and species_records.t_date = l.t_date
The view's properties and the attribute table headers both indicate that the view has indexed fields, even though the underlying table doesn't have indexes.
Do FGDB database view fields really have attribute indexes?
I've heard of indexed views in SQL Server and materialized views in Oracle. But those mechanisms need to be set up manually. So I'm surprised to see FGDBs automatically index certain fields in a database view, especially when the underlying table isn't indexed.
Solved! Go to Solution.
Esri Case #03528658 - FGDB database view — Attribute table shows unindexed fields as having indexes
BUG-000164631: Attribute indexes generated on fields participating in an inner join for the creation of a file geodatabase database view
In the outer query, if I use a table alias instead of SELECT *, then the resulting view doesn't have indexes.
select s.* from species_records s inner join (select t_species, max(t_date) as t_date from species_records group by t_species) l on s.t_species = l.t_species and s.t_date = l.t_date
So that makes me wonder if this is a bug.
I've seen other strange behavior with SELECT * in FGDB database views too. And using a table alias has solved those issues.
See:
BUG-000164420: Database View created in file geodatabase with Join definition does not create the required duplicate fields
Esri Case #03528630 - ArcGIS Pro 3.2.1: FGDB database view has joined fields with duplicate names; joined fields should not be excluded from attribute table
Esri Case #03528658 - FGDB database view — Attribute table shows unindexed fields as having indexes
BUG-000164631: Attribute indexes generated on fields participating in an inner join for the creation of a file geodatabase database view