CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopersql-execution-plan
Iter Ator
Iter Ator

Reputation: 9269

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

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

Upvotes: 3

Views: 909

Answers (2)

Jon Heller
Jon Heller

Reputation: 36798

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)

Upvotes: 5

haki
haki

Reputation: 9759

First try to gather stats on the table, if the plan above makes sense then you'll get it 

exec dbms_stats.gather_table_stats('HR','EMP', cascade=>true);

If you still dont get this plan then oracle thinks there is a better plan (and he's usually right). To force this plan try

select /*+ USE_INDEX(eMP,EMP_DEPARTMENT_IX ) */ 
from HR.employees emp 
where MANAGER_ID <150 and DEPARTMENT_ID <50

Upvotes: 2

Related Questions