topic Re: Do FGDB database views have attribute indexes? (no indexes in underlying table) in Geodatabase Questions

Do FGDB database views have attribute indexes?

Bud — Fri, 26 Jan 2024 19:21:13 GMT

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.

Re: Do FGDB database views have attribute indexes? (no indexes in underlying table)

Bud — Thu, 25 Jan 2024 18:12:25 GMT

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

Re: Do FGDB database views have attribute indexes?

Bud — Fri, 26 Jan 2024 19:05:36 GMT

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