如何创建生成此解释计划的查询?(甲骨文SQL)

Question

数据库是Oracle HR数据库的示例： http://elsasoft.com/samples/oracle/Oracle.XE.HR/default.htm

解释计划：

----------------------------------------------------------
| 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)

我尝试了这个查询，但它产生了一个非常不同的结果：

select /*+ use_hash(emp) */* 
  from HR.employees emp 
 where MANAGER_ID <150 and 
       DEPARTMENT_ID <50;

我已经从 filter 构建了 where 语句，access .还有来自HASH_UNIQUE use_hash.但是结果还是很不一样的，我不知道怎么解决

Answer 1

创建精确的解释计划很困难，并且取决于查询、版本、参数和未记录的提示。

在这种情况下，主要提示可能是未记录的USE_HASH_AGGREGATION，但它也必须与DISTINCT或GROUP BY结合使用。 但它也取决于使用哪一列 - 如果查询仅对主键执行了不同的操作，则它不会聚合，因为优化程序知道没有必要。

由于我使用的是 12c，我不得不禁用_optimizer_batch_table_access_by_rowid，但这对于早期版本不是必需的。

未记录的format => '+outline'功能有助于创建确切的计划。 如果您不使用 12c，则很难保证它会以相同的方式工作。 这个SQL小提琴在11gR2中工作，但很难知道提示是否有效，或者计划是否相同只是运气。

查询

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;

计划

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)

Answer 2

首先尝试在桌子上收集统计数据，如果上面的计划有意义，那么你就会得到它

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

如果你仍然没有得到这个计划，那么oracle认为有一个更好的计划（他通常是对的）。要强制使用此计划，请尝试

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