How to create a query, which produce this explain plan? (oracle sql)

oracle, oracle-sqldeveloper, sql-execution-plan

Solution

Creating a precise explain plan is difficult and depends on the query, version, parameters, and undocumented hints.

In this case the main hint is probably the undocumented `USE_HASH_AGGREGATION`, but it must also be used in combination with a `DISTINCT` or `GROUP BY`. But it also depends on which column is used - if the query did a distinct on only a primary key then it would not aggregate because the optimizer knows there's no need.

Since I'm using 12c I had to disable `_optimizer_batch_table_access_by_rowid`, but this isn't necessary for earlier versions.

The undocumented `format => '+outline'` feature is helpful to create exact plans. If you're not using 12c it's hard to guarantee that it will work the same way. This SQL Fiddle works in 11gR2, but it's difficult to know if the hints are working or if it's just luck that the plan is the same.

Query

explain plan for
select
    /*+
        BEGIN_OUTLINE_DATA
        USE_HASH_AGGREGATION(@"SEL$1")
        INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
        OUTLINE_LEAF(@"SEL$1")
        ALL_ROWS
        OPT_PARAM('_optimizer_batch_table_access_by_rowid' 'false')
        DB_VERSION('12.1.0.1')
        OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
        IGNORE_OPTIM_EMBEDDED_HINTS
        END_OUTLINE_DATA
    */
    distinct first_name
from HR.employees emp 
where MANAGER_ID <150 and 
    DEPARTMENT_ID <50;

Plan

select * from table(dbms_xplan.display(format => 'basic +predicate +outline'));

Plan hash value: 2074795195

----------------------------------------------------------
| Id  | Operation                    | Name              |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |
|   1 |  HASH UNIQUE                 |                   |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |
----------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_optimizer_batch_table_access_by_rowid' 'false')
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - filter("MANAGER_ID"<150)
   3 - access("DEPARTMENT_ID"<50)

Problem

The database is the example Oracle HR database: http://elsasoft.com/samples/oracle/Oracle.XE.HR/default.htm The explain plan: ``` ---------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | HASH UNIQUE | | |* 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | |* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | ---------------------------------------------------------- Predictate Information (identified by operation id): ---------------------------------------------------- 2 - filter("MANAGER_ID" < 150) 3 - access("DEPARTMENT_ID" < 50) ``` I tried this query, but it produced a very different result: ``` select /*+ use_hash(emp) */* from HR.employees emp where MANAGER_ID <150 and DEPARTMENT_ID <50; ``` I've built the where statement from `filter`, and `access`. And the `use_hash` from `HASH_UNIQUE`. But the result is still very different, and I have no idea how to solve it

Original source