Sample Header Ad - 728x90

Oracle Execution Plan - Outline Question

-1 votes
1 answer
248 views
I'm spending the last few days trying to learn some tips/tricks on tuning an Oracle query being run in Crystal Report. Obviously it has some performance issues. I suspect there are some deficiencies on both ends. The database is what I want to focus on. So I ran the query and isolated the SQL ID. I ran this to produce the execution plan with "+outline".
SELECT ROWNUM, T.* FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => 'fg3afzk411qw2',
CURSOR_CHILD_NO => 0,
FORMAT => 'ALLSTATS LAST +cost +bytes +outline')) T;
I replaced the actual table names with names like TABLE_A, TABLE_B and this is the LEADING row from the execution plan. >LEADING(@"SEL$6B71E5ED" "TABLE_A"@"SEL$14" "TABLE_B"@"SEL$16" "TABLE_C"@"SEL$1" "TABLE_D"@"SEL$21") Here are my questions: - Does it mean TABLE_A contain the least amount of rows? - Can the above join order be interpreted as something like this?
TABLE_A LEFT JOIN TABLE_B -> resultset TABLE_AB
     TABLE_AB LEFT JOIN TABLE_C -> resultset TABLE_ABC
     TABLE_ABC LEFT JOIN TABLE_D
There are more tables in the actual execution plan. I simplified just to get my questions posted. Here's the actual execution plan with table names altered. TABLE_D appears 4x and it's the same table because it's being aliased 4x by the query. Same with TABLE_E. enter image description here
Asked by sydney (167 rep)
Jun 29, 2021, 08:08 PM
Last activity: Jun 29, 2021, 11:20 PM