[20120527]视图与hint.txt

这段时间一直在看<,发现yangtingkun的一篇关于视图与hint的文章.
oracle的hint可以强制制定sql的执行计划.如果查询对象是视图,使用hint就很麻烦.
自己重复测试了一下,实际上dbms_xplan.display_cursor的advanced或者outline参数,很容易知道如何写这个提示.

例子如下:

1.建立测试例子:

$cat /home/oracle11g/sqllaji/dpc.sql

select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost'));

create table t1 (id number,name varchar2(30),type varchar2(30));

create table t2 (id number,name varchar2(30),type varchar2(30));

create index ind_t1_name on t1(name);

create index ind_t2_name on t2(name);

insert into t1 select rownum ,object_name,object_type from dba_objects where object_type='TABLE';

insert into t2 select rownum ,object_name,object_type from dba_objects where object_type='TABLE';

commit;

create view v_t as select * from t1 union all select * from t2;

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

      OwnName        => user

     ,TabName        => 'T1'

    ,Estimate_Percent  => NULL

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1 '

    ,Degree            => 4

    ,Cascade           => TRUE

    ,No_Invalidate     => TRUE);

END;

/

BEGIN

  SYS.DBMS_STATS.GATHER_TABLE_STATS (

      OwnName        => user

     ,TabName        => 'T2'

    ,Estimate_Percent  => NULL

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1 '

    ,Degree            => 4

    ,Cascade           => TRUE

    ,No_Invalidate     => TRUE);

END;

/

2.测试:

select * from v_t where name like '%';

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  cpsys1d0g6br0, child number 0

-------------------------------------

select * from v_t where name like '%'

Plan hash value: 680321928

----------------------------------------------------------

| Id  | Operation           | Name | E-Rows | Cost (%CPU)|

----------------------------------------------------------

|   0 | SELECT STATEMENT    |      |        |    12 (100)|

|   1 |  VIEW               | V_T  |   5892 |    12   (0)|

|   2 |   UNION-ALL         |      |        |            |

|*  3 |    TABLE ACCESS FULL| T1   |   2946 |     7   (0)|

|*  4 |    TABLE ACCESS FULL| T2   |   2946 |     7   (0)|

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("NAME" LIKE '%' AND "NAME" IS NOT NULL AND "NAME" IS NOT NULL))

   4 - filter(("NAME" LIKE '%' AND "NAME" IS NOT NULL AND "NAME" IS NOT NULL))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

30 rows selected.

select /*+ full(v_t.t1) index(v_t.t2)*/ * from v_t where name like '%';

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  2ywch6fqag072, child number 0

-------------------------------------

select /*+ full(v_t.t1) index(v_t.t2)*/ * from v_t where name like '%'

Plan hash value: 577028219

---------------------------------------------------------------------------

| Id  | Operation                     | Name        | E-Rows | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |             |        |    12 (100)|

|   1 |  VIEW                         | V_T         |   5892 |    12   (0)|

|   2 |   UNION-ALL                   |             |        |            |

|*  3 |    TABLE ACCESS FULL          | T1          |   2946 |     7   (0)|

|   4 |    TABLE ACCESS BY INDEX ROWID| T2          |   2946 |   715   (0)|

|*  5 |     INDEX FULL SCAN           | IND_T2_NAME |   2946 |    19   (0)|

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("NAME" LIKE '%' AND "NAME" IS NOT NULL AND "NAME" IS NOT NULL))

   5 - filter(("NAME" LIKE '%' AND "NAME" IS NOT NULL AND "NAME" IS NOT NULL))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

31 rows selected.

SQL> @dpc

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------

SQL_ID  0h9xjqus6hhjr, child number 0

-------------------------------------

select /*+ index(v_t.t1) index(v_t.t2)*/ * from v_t where name like '%'

Plan hash value: 3429602345

---------------------------------------------------------------------------

| Id  | Operation                     | Name        | E-Rows | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |             |        |    12 (100)|

|   1 |  VIEW                         | V_T         |   5892 |    12   (0)|

|   2 |   UNION-ALL                   |             |        |            |

|   3 |    TABLE ACCESS BY INDEX ROWID| T1          |   2946 |   715   (0)|

|*  4 |     INDEX FULL SCAN           | IND_T1_NAME |   2946 |    19   (0)|

|   5 |    TABLE ACCESS BY INDEX ROWID| T2          |   2946 |   715   (0)|

|*  6 |     INDEX FULL SCAN           | IND_T2_NAME |   2946 |    19   (0)|

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter(("NAME" LIKE '%' AND "NAME" IS NOT NULL AND "NAME" IS NOT NULL))

   6 - filter(("NAME" LIKE '%' AND "NAME" IS NOT NULL AND "NAME" IS NOT NULL))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

32 rows selected.

--在视图中使用hint,就是显示=>视图名.表名.

3.如果不使用视图呢?

select * from (select * from t1 union all select * from t2) a where name like '%';

SQL> @dpc

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------

SQL_ID  cv2bzpbf742ps, child number 0

-------------------------------------

select * from (select * from t1 union all select * from t2) a where name like '%'

Plan hash value: 1505077622

----------------------------------------------------------

| Id  | Operation           | Name | E-Rows | Cost (%CPU)|

----------------------------------------------------------

|   0 | SELECT STATEMENT    |      |        |    12 (100)|

|   1 |  VIEW               |      |   5892 |    12   (0)|

|   2 |   UNION-ALL         |      |        |            |

|*  3 |    TABLE ACCESS FULL| T1   |   2946 |     7   (0)|

|*  4 |    TABLE ACCESS FULL| T2   |   2946 |     7   (0)|

----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("T1"."NAME" LIKE '%' AND "T1"."NAME" IS NOT NULL AND "T1"."NAME" IS NOT NULL))

   4 - filter(("T2"."NAME" LIKE '%' AND "T2"."NAME" IS NOT NULL AND "T2"."NAME" IS NOT NULL))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

31 rows selected.

SQL> select /*+ index(a.t1) */* from (select * from t1 union all select * from t2) a where name like '%';

SQL> @dpc

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------

SQL_ID  392u75m96djfc, child number 0

-------------------------------------

select /*+ index(a.t1) */* from (select * from t1 union all select * from t2) a where name like '%'

Plan hash value: 2194796698

---------------------------------------------------------------------------

| Id  | Operation                     | Name        | E-Rows | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |             |        |    12 (100)|

|   1 |  VIEW                         |             |   5892 |    12   (0)|

|   2 |   UNION-ALL                   |             |        |            |

|   3 |    TABLE ACCESS BY INDEX ROWID| T1          |   2946 |   715   (0)|

|*  4 |     INDEX FULL SCAN           | IND_T1_NAME |   2946 |    19   (0)|

|*  5 |    TABLE ACCESS FULL          | T2          |   2946 |     7   (0)|

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter(("T1"."NAME" LIKE '%' AND "T1"."NAME" IS NOT NULL AND "T1"."NAME" IS NOT NULL))

   5 - filter(("T2"."NAME" LIKE '%' AND "T2"."NAME" IS NOT NULL AND "T2"."NAME" IS NOT NULL))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

32 rows selected.

--一样,使用别名.表名.

4.如果没有别名呢?

SQL> select  * from (select * from t1 union all select * from t2)  where name like '%';

--在显示执行计划中加入outline或者advanced.

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS cost outline'));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------

SQL_ID  59mctfxwrck44, child number 0

-------------------------------------

select * from (select * from t1 union all select * from t2)  where name

like '%'

Plan hash value: 1505077622

----------------------------------------------------------

| Id  | Operation           | Name | E-Rows | Cost (%CPU)|

----------------------------------------------------------

|   0 | SELECT STATEMENT    |      |        |    12 (100)|

|   1 |  VIEW               |      |   5892 |    12   (0)|

|   2 |   UNION-ALL         |      |        |            |

|*  3 |    TABLE ACCESS FULL| T1   |   2946 |     7   (0)|

|*  4 |    TABLE ACCESS FULL| T2   |   2946 |     7   (0)|

----------------------------------------------------------

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')

      DB_VERSION('11.2.0.1')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$2")

      OUTLINE_LEAF(@"SEL$3")

      OUTLINE_LEAF(@"SET$1")

      OUTLINE_LEAF(@"SEL$1")

      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")

      FULL(@"SEL$3" "T2"@"SEL$3")

      FULL(@"SEL$2" "T1"@"SEL$2")

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("T1"."NAME" LIKE '%' AND "T1"."NAME" IS NOT NULL AND

              "T1"."NAME" IS NOT NULL))

   4 - filter(("T2"."NAME" LIKE '%' AND "T2"."NAME" IS NOT NULL AND

              "T2"."NAME" IS NOT NULL))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

50 rows selected.

--从这里   NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")

--这样很容易猜到别名是"from$_subquery$_001",注意要使用引号.

SQL> select /*+ index("from$_subquery$_001".t2) */ * from (select * from t1 union all select * from t2)  where name like '%';

SQL> @dpc

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------

SQL_ID  c8rt0k8zxwfgu, child number 0

-------------------------------------

select /*+ index("from$_subquery$_001".t2) */ * from (select * from t1

union all select * from t2)  where name like '%'

Plan hash value: 1923654983

---------------------------------------------------------------------------

| Id  | Operation                     | Name        | E-Rows | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |             |        |    12 (100)|

|   1 |  VIEW                         |             |   5892 |    12   (0)|

|   2 |   UNION-ALL                   |             |        |            |

|*  3 |    TABLE ACCESS FULL          | T1          |   2946 |     7   (0)|

|   4 |    TABLE ACCESS BY INDEX ROWID| T2          |   2946 |   715   (0)|

|*  5 |     INDEX FULL SCAN           | IND_T2_NAME |   2946 |    19   (0)|

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("T1"."NAME" LIKE '%' AND "T1"."NAME" IS NOT NULL AND

              "T1"."NAME" IS NOT NULL))

   5 - filter(("T2"."NAME" LIKE '%' AND "T2"."NAME" IS NOT NULL AND

              "T2"."NAME" IS NOT NULL))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

32 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-731218/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-731218/