Hi All,
I'm trying to get a hold on interpreting the Explain Plan output, I've gone through the White Paper and have a couple of questions.
The below explain plan for the SQL given below.
SELECT prod_category, AVG(amount_sold) FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY prod_category;
White paper says "tabular representation is a top-down, left-to-right traversal of the execution tree. When you read a plan tree you should start from the bottom left and work across and then up. In the above example, begin by looking at the leaves of the tree. In this case, the leaves of the tree are implemented using a full table scans of the PRODUCTS and the SALES tables."
Could you please explain the highlighted words, it is really confusing for me, kindly bear with me? My understanding was it will build Hash table on the PRODUCTS table (Prod_ID column) and join with the SALES table on Prod_ID column. But, they have mentioned, start from the bottom left and work across and then up, does it mean the parser, will start with the SALES table, move up to Products, join them, finally group by.
In another example given below, for a similar SQL, it shows a different plan with different operation ordering, could you please explain this, especially where the parser will start and proceed, should I read the plan output lines from top to bottom or bottom to top? Also, I'm not sure, how it is referring the view "VW_GBC_5" and it has a nested group by in turn, as the source SQL doesn't use any views. Thank you so much!!
