Path ID Negative 1 and 0 in Execution Engine Profiles
Hello, I've been exploring query profiling, and ran into two Path ID's that I don't see in our Query Explain Plan, 0 and -1.
For 0, I see nodes sending data to a single node, I assume this is where data is sent to the query originator?
However, Path ID -1 is more confusing. For some basic queries, I see 'Root'. Does that account for transfer to the client?
In other queries, for example, in the one below where I force a Merge Join (doing this for understanding, not for production queries), the only sort operation I see occurs in PathId -1. However, I'd expect it to occur on a path id associated with the join. I also see filter operations, which I'd expect to see associated with reading from disk.
My best guess at this moment is that -1 is used for paths that aren't expressed as individual paths, but are 'implied paths', ie the sort operations, filter operation, and root. Is that correct, or does it indicate something else?
SELECT /* + SYNTACTIC_JOIN*/ dim.account_name , COUNT(*) FROM fact JOIN /*+JTYPE(FM)*/ dim ON fact.account_id = da.account_id WHERE fact.date > '2022-08-01' GROUP BY dim.account_name;
Summary of the -1 Path ID in Excel
@mferrall Thank you for sharing this information. Sort and StorageUnion don't appear in path ID = -1. We will fix it in the future release.