ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120527]视图与hint.txt

[20120527]视图与hint.txt

原创 Linux操作系统 作者:lfree 时间:2012-05-28 07:36:09 0 删除 编辑


这段时间一直在看<,发现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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2285
  • 访问量
    6025247