oracle hint_no_expand_no_fact

wisdomone1發表於2012-12-20
Oracle
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章