hint no_expand
lnnvl在执行计划中,这是函数
1,no_expand对于where条件中出现in列表或出现or的查询,不会考虑or扩展
2,一般情况下,优化器会使用or扩展,假如不使用or扩展代价更低,会使用or扩展
lnnvl在执行计划中,这是函数
1,no_expand对于where条件中出现in列表或出现or的查询,不会考虑or扩展
2,一般情况下,优化器会使用or扩展,假如不使用or扩展代价更低,会使用or扩展
SQL> explain plan for select emp.* from emp,dept where emp.mgr=3 or dept.deptno=
30;
30;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 613092161
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
-
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 630 | 5 (0)| 00:00:01
|
|
| 1 | CONCATENATION | | | | |
|
|
| 2 | NESTED LOOPS | | 12 | 504 | 2 (0)| 00:00:01
|
|
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01
|
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL | EMP | 12 | 468 | 2 (0)| 00:00:01
|
| 5 | MERGE JOIN CARTESIAN| | 3 | 126 | 3 (0)| 00:00:01
|
|
|* 6 | TABLE ACCESS FULL | EMP | 1 | 39 | 2 (0)| 00:00:01
|
|
| 7 | BUFFER SORT | | 7 | 21 | 1 (0)| 00:00:01
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
|
|* 8 | INDEX FULL SCAN | PK_DEPT | 7 | 21 | 1 (0)| 00:00:01
|
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
3 - access("DEPT"."DEPTNO"=30)
6 - filter("EMP"."MGR"=3)
8 - filter(LNNVL("DEPT"."DEPTNO"=30))
已选择22行。
SQL> explain plan for select /*+ no_expand() */ emp.* from emp,dept where emp.mg
r=3 or dept.deptno=30;
r=3 or dept.deptno=30;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan hash value: 3941739098
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
-
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 630 | 6 (0)| 00:00:01
|
|
| 1 | NESTED LOOPS | | 15 | 630 | 6 (0)| 00:00:01
|
|
| 2 | TABLE ACCESS FULL | EMP | 12 | 468 | 2 (0)| 00:00:01
|
|
|* 3 | INDEX FAST FULL SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01
|
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."MGR"=3 OR "DEPT"."DEPTNO"=30)
已选择15行。
no_fact
1,用于星形转换中,此提示指示查询表不能用于事实表
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-751380/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-751380/