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

Bud

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?

 

 

Bud

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.