How do I create a query that generates this explain plan? (oracle sql)
The database is an example Oracle HR database: http://elsasoft.com/samples/oracle/Oracle.XE.HR/default.htm
Explanation 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 completely different result:
select /*+ use_hash(emp) */*
from HR.employees emp
where MANAGER_ID <150 and
DEPARTMENT_ID <50;
I built a where clause from filter
and access
. And use_hash
from HASH_UNIQUE
. But the result is still very different and I have no idea how to solve it.
source to share
Creating an accurate explain plan is difficult and depends on query, version, options, and undocumented advice.
In this case, the main hint is probably undocumented USE_HASH_AGGREGATION
, but it should also be used in conjunction with DISTINCT
or GROUP BY
. But it also depends on which column is used - if the query made a separate one only on the primary key, then it won't aggregate, because the optimizer doesn't know what it doesn't need.
Since I am using 12c I had to disable it _optimizer_batch_table_access_by_rowid
, but this is not necessary for earlier versions.
An undocumented feature format => '+outline'
is useful for making accurate plans. If you are not using 12c, it is difficult to guarantee that it will work the same way. This SQL script works in 11gR2, but it's hard to tell if the hints are working or 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)
source to share
Try to collect stats on the table first, if the plan above makes sense then you will get it
exec dbms_stats.gather_table_stats('HR','EMP', cascade=>true);
If you still don't get this plan, then the oracle thinks there is a better plan (and he is usually right). To make this plan try
select /*+ USE_INDEX(eMP,EMP_DEPARTMENT_IX ) */
from HR.employees emp
where MANAGER_ID <150 and DEPARTMENT_ID <50
source to share