Community

Attribute table join (and sort) between two EGDB tables: How are the join and sort performed? (DB vs. application)

950
1
06-25-2022 12:12 AM
Bud
by
Notable Contributor
‎06-25-2022 12:12 AM

Oracle 18c 10.7.1 EGDB; ArcGIS Pro 2.6.8:

In the attribute table, if I join between one table to another, how is the join performed? (DB vs. application)

  1. Does a single query get sent to the DB that returns ALL columns in a single resulset?
    • Select the rows from the first table, do an SQL JOIN to the second table, for each row - retrieve the first row from the second table that's matched, return the whole thing as a single resultset?
    • Or maybe a subquery is used (for each row in the first table) to get a row from the second table?
  2. Or does the application do the joining?
    • Select the rows from the first table. Select the rows from the second table. Use the application to match them up?
  3. Or something else?

Likewise, if I sort the columns in the attribute table, does the DB do the sorting, or does the application do the sorting?

 

 

0 Kudos
1 Reply
Bud
by
Notable Contributor
‎06-25-2022 12:23 AM
The reason I ask: I'm trying to figure out why sorting is so slow in this workaround:


Question: Sorting a field in the attribute table: Specify how nulls are handled (nulls first or last)

Workaround: 

1. Create a query layer with a NULL_FLAG field:

select
    objectid,
    last_edited_date,
    case 
        when last_edited_date is not null then 1 
        else 0 
    end as null_flag
    --Source: https://modern-sql.com/concept/null#order-by
from
    events

2. In the Attribute Table, join from the table to the query layer via the OBJECTID.

3. Do an advanced sort using the fields from the query layer:
 - NULL_FLAG descending.
 - LAST_EDITED_DATE descending.
 

Now, the latest dates are at the top and the nulls are at the bottom. Which is what I want.

Unfortunately, it's far too slow to use. It takes roughly a minute to do the sorting, whereas sorting was instantaneous without the workaround (and sorting was instantaneous when I tested the query in SQL Developer too). So, something about sorting on the query layer in the attribute table is going awry.

0 Kudos