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.

Asked by sydney
(167 rep)
Jun 29, 2021, 08:08 PM
Last activity: Jun 29, 2021, 11:20 PM
Last activity: Jun 29, 2021, 11:20 PM