{"id":73193,"date":"2014-07-09T11:42:47","date_gmt":"2014-07-09T11:42:47","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/execution-plans-part-7-query-blocks-and-inline-views\/"},"modified":"2021-07-14T13:07:31","modified_gmt":"2021-07-14T13:07:31","slug":"execution-plans-part-7-query-blocks-and-inline-views","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/execution-plans-part-7-query-blocks-and-inline-views\/","title":{"rendered":"Execution Plans Part 7: Query Blocks and Inline Views"},"content":{"rendered":"

When we examine an execution plan, we\u2019re usually trying to work out the mechanical steps that Oracle took to produce a result set. When we do this, it\u2019s important to remember that the text that Oracle optimized wasn’t necessarily the same as the statement we originally wrote. Oracle may have transformed our statement before passing it through the optimization engine. Sometimes it\u2019s obvious that some significant transformations have taken place because we see some unexpected “VIEW” operations appearing in the plan, but sometimes we need to take note of the “Query Block”<\/em>\u00a0details to notice that the statement we started with isn’t the statement that the optimizer ended with.<\/p>\n

Query Blocks.<\/h2>\n

The “unit of optimization”<\/em>\u00a0is the Query Block<\/strong>, and Oracle made it very easy from 10g onward to identify query blocks in execution plans. Every time you see a select, insert, update, delete, or merge keyword in a statement you\u2019re looking at the start of a query block where you can introduce the qb_name<\/i><\/b> (query block name) hint, for example:<\/p>\n

select\u00a0 \/*+ qb_name(main) *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 outer.*\r\nfrom\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp outer\r\nwhere\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 outer.sal > (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0 \/*+ qb_name(avg_subq) *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 avg(inner.sal)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp inner\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 inner.dept_no = outer.dept_no\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n;<\/pre>\n
As you can see I have named the main body of my query “main<\/strong>“, and the correlated subquery “avg_subq<\/strong>“.\u00a0In the absence of explicitly stated names, Oracle would have generated the names sel$1<\/strong> and sel$2<\/strong> (with del$1<\/strong>, ins$1<\/strong>, mrg$1<\/strong> etc. for the other types of SQL commands). Here\u2019s the execution plan I get for this query, with no other hints from a call to dbms_xplan.display()<\/strong><\/em> with the format options of \u2018+alias +outline\u2019<\/i><\/b>.\u00a0 (I should point out that the EMP<\/strong>\u00a0table in this example is one I generated with 6 departments and 20,000 rows):<\/p>\n
----------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0 | Rows\u00a0 | Bytes | Cost\u00a0 |\r\n----------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1000 | 98000 |\u00a0\u00a0 120 |\r\n|*\u00a0 1 |\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1000 | 98000 |\u00a0\u00a0 120 |\r\n|\u00a0\u00a0 2 |\u00a0\u00a0 VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | VW_SQ_1 |\u00a0\u00a0\u00a0\u00a0 6 |\u00a0\u00a0 156 |\u00a0\u00a0\u00a0 84 |\r\n|\u00a0\u00a0 3 |\u00a0\u00a0\u00a0 HASH GROUP BY\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 6 |\u00a0\u00a0\u00a0 48 |\u00a0\u00a0\u00a0 84 |\r\n|\u00a0\u00a0 4 |\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS FULL| EMP\u00a0\u00a0\u00a0\u00a0 | 20000 |\u00a0\u00a0 156K|\u00a0\u00a0\u00a0 35 |\r\n|\u00a0\u00a0 5 |\u00a0\u00a0 TABLE ACCESS FULL\u00a0 | EMP\u00a0\u00a0\u00a0\u00a0 | 20000 |\u00a0 1406K|\u00a0\u00a0\u00a0 35 |\r\n----------------------------------------------------------------\r\n\r\nQuery Block Name \/ Object Alias (identified by operation id):\r\n-------------------------------------------------------------\r\n\u00a0\u00a0 1 - SEL$C7CDAD1E\r\n\u00a0\u00a0 2 - SEL$11FCF3E2 \/ VW_SQ_1@SEL$EF633D71\r\n\u00a0\u00a0 3 - SEL$11FCF3E2\r\n\u00a0\u00a0 4 - SEL$11FCF3E2 \/ INNER@AVG_SUBQ\r\n\u00a0\u00a0 5 - SEL$C7CDAD1E \/ OUTER@MAIN\r\n\r\nOutline Data\r\n-------------\r\n\u00a0 \/*+\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 BEGIN_OUTLINE_DATA\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 USE_HASH_AGGREGATION(@\"SEL$11FCF3E2\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FULL(@\"SEL$11FCF3E2\" \"INNER\"@\"AVG_SUBQ\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 USE_HASH(@\"SEL$C7CDAD1E\" \"OUTER\"@\"MAIN\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LEADING(@\"SEL$C7CDAD1E\" \"VW_SQ_1\"@\"SEL$EF633D71\" \"OUTER\"@\"MAIN\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 FULL(@\"SEL$C7CDAD1E\" \"OUTER\"@\"MAIN\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 NO_ACCESS(@\"SEL$C7CDAD1E\" \"VW_SQ_1\"@\"SEL$EF633D71\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OUTLINE(@\"MAIN\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OUTLINE(@\"SEL$EF633D71\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OUTLINE(@\"AVG_SUBQ\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 UNNEST(@\"AVG_SUBQ\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OUTLINE_LEAF(@\"SEL$C7CDAD1E\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OUTLINE_LEAF(@\"SEL$11FCF3E2\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ALL_ROWS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OPT_PARAM('_optimizer_cost_model' 'io')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DB_VERSION('11.2.0.4')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 IGNORE_OPTIM_EMBEDDED_HINTS\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 END_OUTLINE_DATA\r\n  *\/<\/pre>\n
There are several points we can pick out from this simple example. First, of course, line 1 shows us a hash join between lines 2 and 5; but line 2 is a VIEW<\/strong> operator and the view name isn’t one that I referenced (or aliased) and, in fact, it doesn’t even exist in my database. This is an example of Oracle generating an internal view during query transformation and then optimizing with a non-mergeable view. In effect the optimizer has rewritten the query into the following form:<\/p>\n
select\u00a0 \/*+ qb_name(main) *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 outer.*\r\nfrom\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0 \/*+ qb_name(avg_subq) *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0inner.dept_no, avg(inner.sal) avg_sal\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp inner\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 group by\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 inner.dept_no\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 vw_sq_1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 emp\u00a0\u00a0\u00a0\u00a0 outer\r\nwhere\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 outer.sal \u00a0\u00a0\u00a0\u00a0> vw_sq_1.avg_sal\r\nand\u00a0\u00a0\u00a0\u00a0 outer.dept_no = vw_sq_1.dept_no\r\n;<\/pre>\n
This rewrite produced exactly the same plan as the original with the slight difference that the name that I’ve explicitly given the inline view didn’t appear in the execution plan (and, if you try this in earlier versions of Oracle, you may find that the costs don\u2019t quite match the original).<\/p>\n
The optimizer has decided that it\u2019s not going to merge the inline view (whether generated or explicitly stated) into the main query to produce a single join, so it has optimized it separately. Apart from the clue we get from seeing the VIEW<\/strong> operator we get clues from two other places \u2013 first the “Query Block Name \/ Object Alias<\/strong>“<\/em>\u00a0section of the report shows us that\u00a0 line 2 (and its descendants, lines 3 and 4) are part of a query block called SEL$11FCF3E2<\/strong>; then we see that the “Outline Data<\/strong>“<\/em>\u00a0section of the report tells us that query block SEL$11FCF3E2<\/strong> is an “outline_leaf”<\/em>, in other words it is a “final” query block that has actually been subject to independent optimization.<\/p>\n
Another detail we can pick from the plan \u2013 with the optional extra sections \u2013 is the identity of the two different appearance of the EMP<\/strong> table. Lines 4 and 5 both identify EMP<\/strong> as the table scanned; but which EMP<\/strong> comes from which part of the query? \u00a0The “Query Block Name \/ Object Alias<\/strong>“<\/em>\u00a0section tells us that EMP<\/strong> from line 4 was aliased “inner<\/strong>”\u00a0in query block “avg_subq<\/strong>“, while the EMP<\/strong> from line 5 was aliased “outer<\/strong>”\u00a0in query block “main<\/strong>“. We might have guessed that very quickly in this example, but it gets harder when you\u2019re looking at Oracle Financials and statements with multiple references to the FND_CODE_COMBINATIONS<\/strong> table!<\/p>\n
A question that we could ask about the incomprehensible query block names that Oracle generates is: “are they deterministic?”<\/em>\u00a0\u2013 is it possible for the same query to give you the same plan while generating different query block names on different versions of Oracle (or different days of the week). The answer is (or should be) no; when Oracle generates a query block name (after supplying the initial defaults of sel$1, sel$2 etc.) it applies a hashing function to the query block names that have gone INTO a transformation to generate the name that it will use for the block that comes OUT of the transformation.<\/p>\n
Multiple Transformations<\/h2>\n
I\u2019m going to push this query just a little further by hinting it into a path that might have appeared if the numbers had been a little different. Having unnested the subquery into the inline aggregate view the optimizer might have decided that it was sensible to use “complex view merging”<\/em> to join the two copies of EMP<\/strong> before aggregating. I can emulate this by adding the \/*+ merge *\/<\/strong> hint to the code.\u00a0 I could do this by adding the hint into the subquery itself to get:<\/p>\n
select \/*+ qb_name(avg_subq) merge *\/<\/pre>\n
or I could add it to the hints in the main query itself but targeting the right query block to get:<\/p>\n
select \/*+ qb_name(main) merge(@avg_subq)*\/<\/pre>\n
Note, especially, the “@” symbol that I’ve used to direct the hint to a particular query block. Here\u2019s the plan:<\/p>\n
---------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Rows\u00a0 | Bytes |TempSpc| Cost\u00a0 |\r\n---------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 3333K|\u00a0\u00a0 254M|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 953K|\r\n|*\u00a0 1 |\u00a0 FILTER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0 2 |\u00a0\u00a0 HASH GROUP BY\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 3333K|\u00a0\u00a0 254M|\u00a0 6127M|\u00a0\u00a0 953K|\r\n|*\u00a0 3 |\u00a0\u00a0\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 66M|\u00a0 5086M|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 6749 |\r\n|\u00a0\u00a0 4 |\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS FULL| EMP\u00a0 | 20000 |\u00a0\u00a0 156K|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 35 |\r\n|\u00a0\u00a0 5 |\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS FULL| EMP\u00a0 | 20000 |\u00a0 1406K|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 35 |\r\n---------------------------------------------------------------------\r\n\r\nQuery Block Name \/ Object Alias (identified by operation id):\r\n-------------------------------------------------------------\r\n\u00a0\u00a0 1 - SEL$A36D7A23\r\n\u00a0\u00a0 4 - SEL$A36D7A23 \/ INNER@AVG_SUBQ\r\n\u00a0\u00a0 5 - SEL$A36D7A23 \/ OUTER@MAIN\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\u00a0\u00a0 1 - filter(\"OUTER\".\"SAL\">AVG(\"INNER\".\"SAL\"))\r\n\u00a0\u00a0 3 - access(\"INNER\".\"DEPT_NO\"=\"OUTER\".\"DEPT_NO\")<\/pre>\n
It\u2019s interesting to note that the expected cardinality of the result set has changed because of the change in plan. This shouldn\u2019t happen and it\u2019s one of the weaknesses of the optimizer that it can happen \u2013 in principle, different paths through the optimizer ought to produce the same final cardinality estimates (even though the cost may change).<\/p>\n
The little points I wanted to pick out here are that (a) the final query block name has changed, (b) there is no VIEW<\/strong> operator, the optimizer has collapsed the entire query down to a single query block and optimized just that and (c) thanks to the use of query block names we can still see where our two EMP<\/strong> tables originally came from.<\/p>\n
ANSI headache<\/h2>\n
I can’t end without mentioning an annoying feature of ANSI SQL in Oracle. Putting it crudely, the optimizer doesn’t like ANSI and (apart from a couple of special cases) transforms ANSI into an equivalent Oracle format before optimizing it. This makes it much harder to use query block names. Consider, for example, the following very simple query and execution plan (excluding predicates):<\/p>\n
select\r\n\u00a0\u00a0\u00a0\u00a0 \/*+ qb_name(main) *\/\r\n\u00a0\u00a0\u00a0\u00a0 *\r\nfrom\r\n\u00a0\u00a0\u00a0\u00a0 t1\r\njoin\r\n\u00a0\u00a0\u00a0\u00a0 t2\r\non\u00a0\u00a0 t2.t2_n1 = t1.t1_n2\r\njoin\r\n\u00a0\u00a0\u00a0\u00a0 t3\r\non\u00a0\u00a0 t3.t3_n1 = t2.t2_n2\r\njoin\r\n\u00a0\u00a0\u00a0\u00a0 t4\r\non\u00a0\u00a0 t4.t4_n1 = t3.t3_n2\r\n;\r\n\r\n-----------------------------------------------------------------------------\r\n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Rows\u00a0 | Bytes | Cost (%CPU)| Time\u00a0\u00a0\u00a0\u00a0 |\r\n-----------------------------------------------------------------------------\r\n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 192K|\u00a0\u00a0 140M|\u00a0\u00a0\u00a0 56\u00a0 (15)| 00:00:01 |\r\n|*\u00a0 1 |\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 192K|\u00a0\u00a0 140M|\u00a0\u00a0\u00a0 56\u00a0 (15)| 00:00:01 |\r\n|\u00a0\u00a0 2 |\u00a0\u00a0 TABLE ACCESS FULL\u00a0 | T4\u00a0\u00a0 |\u00a0 3000 |\u00a0\u00a0 562K|\u00a0\u00a0\u00a0 12\u00a0\u00a0 (0)| 00:00:01 |\r\n|*\u00a0 3 |\u00a0\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 | 48000 |\u00a0\u00a0\u00a0 26M|\u00a0\u00a0\u00a0 39\u00a0\u00a0 (8)| 00:00:01 |\r\n|\u00a0\u00a0 4 |\u00a0\u00a0\u00a0 TABLE ACCESS FULL | T3\u00a0\u00a0 |\u00a0 3000 |\u00a0\u00a0 562K|\u00a0\u00a0\u00a0 12\u00a0\u00a0 (0)| 00:00:01 |\r\n|*\u00a0 5 |\u00a0\u00a0\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 | 12000 |\u00a0 4488K|\u00a0\u00a0\u00a0 25\u00a0\u00a0 (4)| 00:00:01 |\r\n|\u00a0\u00a0 6 |\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS FULL| T1\u00a0\u00a0 |\u00a0 3000 |\u00a0\u00a0 562K|\u00a0\u00a0\u00a0 12\u00a0\u00a0 (0)| 00:00:01 |\r\n|\u00a0\u00a0 7 |\u00a0\u00a0\u00a0\u00a0 TABLE ACCESS FULL| T2\u00a0\u00a0 |\u00a0 3000 |\u00a0\u00a0 559K|\u00a0\u00a0\u00a0 12\u00a0\u00a0 (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------<\/pre>\n
There’s nothing unexpected about the plan \u2013 it seems a perfectly reasonable way to join the 4 tables; but an anomaly appears when you look at the “Query block \/ Object Alias<\/strong>“<\/em>\u00a0section of the plan. We clearly have a single query block in the original text, and all 4 tables are in that query block; and a quick check of syntax confirms that the qb_name()<\/strong><\/em> hint is properly specified \u2013 but here’s how Oracle sees it:<\/p>\n
Query Block Name \/ Object Alias (identified by operation id):\r\n-------------------------------------------------------------\r\n\u00a0\u00a0 1 - SEL$43767242\r\n\u00a0\u00a0 2 - SEL$43767242 \/ T4@SEL$3\r\n\u00a0\u00a0 4 - SEL$43767242 \/ T3@SEL$2\r\n\u00a0\u00a0 6 - SEL$43767242 \/ T1@SEL$1\r\n\u00a0\u00a0 7 - SEL$43767242 \/ T2@SEL$1<\/pre>\n
We don\u2019t have a query block called “main”, we have 3 separate query blocks given the default names of sel$1<\/strong>, sel$2<\/strong> and sel$3<\/strong>, and we don\u2019t have a four-table join, we have three separate two-table joins. In fact we would be able to see in the outline section that the query block name “main<\/strong>”\u00a0has been used but has been merged. In effect, Oracle read the ANSI and transformed it into the following query:<\/p>\n
select\u00a0\u00a0\u00a0\u00a0 \/*+ qb_name(main) *\/\r\n\u00a0\u00a0\u00a0\u00a0 *\r\nfrom (\r\n\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0 \/*+ qb_name(sel$3) *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0 from\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select\u00a0\u00a0\u00a0 \/*+ qb_name(sel$2) *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/*+ qb_name(sel$1) *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where t2.t2_n1 = t1.t1_n2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) v1,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 t3\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where t3.t3_n1 = v1.t2_n2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\u00a0\u00a0\u00a0 v2,\r\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0 t4\r\n\u00a0\u00a0\u00a0\u00a0 where t4.t4_n1 = v2.t3_n2\r\n\u00a0\u00a0\u00a0\u00a0 )\r\n;<\/pre>\n
In complex cases, this type of preliminary rewrite can make it much harder to work out how to inject the hints you need when you’re trying to force a particular execution plan for a query.<\/p>\n
Summary notes<\/h2>\n
It\u2019s a good idea to use the qb_name<\/strong><\/em> hint throughout your queries to give explicit names to the query blocks that make up your queries.\u00a0When a query is transformed internally by the optimizer this naming makes it much easier to associate the positions of tables in the transformed plan with their positions in the original text \u2013 this can be particularly helpful if the same table is used several times in a single query. Unfortunately, Oracle’s treatment of even the simplest ANSI SQL statements confuses the issue by generating a lot of query block names that you can\u2019t affect.<\/p>\n
If you see the VIEW<\/strong> operator in an execution plan, this identifies a separately optimized query block in your (possibly transformed) SQL \u2013 sometimes this will also mean that the result set of that piece of SQL will be completely built in memory before the next steps of the query plan take place but this is not necessarily the case. You will probably be able to associate a VIEW<\/strong> operator in the plan with an OUTLINE_LEAF()<\/strong> hint in the outline section of the output.<\/p>\n
The outline section of the dbms_xplan<\/strong><\/em> output will show some OUTLINE()<\/strong> hints \u2013 which correspond to initial or intermediate query blocks – and some (or possibly only one) OUTLINE_LEAF()<\/strong> hints \u2013 which correspond to the final query block(s) that were individually optimized.<\/p>\n","protected":false},"excerpt":{"rendered":"
When we examine an execution plan, we\u2019re usually trying to work out the mechanical steps that Oracle took to produce a result set. When we do this, it\u2019s important to remember that the text that Oracle optimized wasn’t necessarily the same as the statement we originally wrote. Oracle may have transformed our statement before passing it through the optimization engin…<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48353,48354,4783,48481,4150],"coauthors":[],"class_list":["post-73193","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-ansi","tag-ansi-sql","tag-execution-plans","tag-query-blocks","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73193","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73193"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73193\/revisions"}],"predecessor-version":[{"id":91680,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73193\/revisions\/91680"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73193"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}