Readable join for execution plan in Oracle I want to understand what join operations were done on my database by different users for future analysis and improvements. but I have issues with understanding the execution plan.
Example:
Consider this query:
SELECT f.year, f.geoid, a.age_band, sum(f.num_people), sum(f.num_insured)
FROM fact f
  JOIN age_bands a ON f.age_code = a.age_code
WHERE f.gender_code = 1
GROUP BY f.year, f.geoid, a.age_band

When I try to see the execution plan with this command:
SELECT s.sql_id, p.*
FROM v$sql s CROSS JOIN LATERAL (SELECT * FROM TABLE (
  dbms_xplan.display_cursor (s.sql_id, s.child_number, 'ALLSTATS LAST')
)) p
WHERE s.sql_text LIKE '%f.gender_code = 1%';

I get different values for the Predicate Information values (for different child numbers)
for instance - for child number 0:
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(&quot;ITEM_1&quot;=&quot;A&quot;.&quot;AGE_CODE&quot;)
   6 - filter(&quot;F&quot;.&quot;GENDER_CODE&quot;=1)

for child number 1 (and 2):
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(&quot;F&quot;.&quot;AGE_CODE&quot;=&quot;A&quot;.&quot;AGE_CODE&quot;)
   4 - filter(&quot;F&quot;.&quot;GENDER_CODE&quot;=1)

My questions are

what is &quot;ITEM_1&quot; and why does it replace &quot;F&quot;.&quot;AGE_CODE&quot; ?

is there a better way to understand what joins were made in the DB?

is there a simple way to convert the alias in the Predicate Information to the full table name? (for instance convert &quot;A&quot; to &quot;AGE_BANDS&quot; in the above example)
That is because of query transformations like for example Join Factorization.

Simply put, with JF, queries like this:
select t1.c1, t2.c2
    from t1, t2, t3
    where t1.c1 = t2.c1 and t1.c1 &gt; 1 and t2.c2 = 2 and t2.c2 = t3.c2 
  union all
    select t1.c1, t2.c2
    from t1, t2, t4
    where t1.c1 = t2.c1 and t1.c1 &gt; 1 and t2.c3 = t4.c3;

Can be automatically rewritten by the optimizer to something like this:
select t1.c1, VW_JF_1.item_2
    from t1, (select t2.c1 item_1, t2.c2 item_2
                   from t2, t3
                   where t2.c2 = t3.c2 and t2.c2 = 2                 
                 union all
                   select t2.c1 item_1, t2.c2 item_2
                   from t2, t4 
                   where t2.c3 = t4.c3) VW_JF_1
    where t1.c1 = VW_JF_1.item_1 and t1.c1 &gt; 1;

You can see a step called VW_JF_... in your plan whan this happens.
There are other transformations as well that can cause this.

This is too generic and unclear what you want.

Not that I am aware of. You can however display an alias mapping with the format=&gt;'alias' option:


SQL&gt; explain plan for select count(*) from t1 a where a.object_id = 1;
Explained.
Default output:
SQL&gt; select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - access(&quot;A&quot;.&quot;OBJECT_ID&quot;=1)

14 rows selected.

Same with alias mapping:
SQL&gt; select * from table(dbms_xplan.display(format=&gt;'alias'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| I1   |     1 |     5 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

   1 - T1
   2 - T1 / A@T1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(&quot;A&quot;.&quot;OBJECT_ID&quot;=1)

20 rows selected.

SQL&gt;