oracle hint_no_expand_no_fact

hint no_expand
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;

已解释。

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;

已解释。

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/