[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.


				


				
时间： 2024-07-30 10:50:28

		
		


		


	

	
	

		


		
[20120527]视图与hint.txt的相关文章


								

		

			

                [20150629]物化视图刷新atomic_refresh.txt
			

		

		

									

				[20150629]物化视图刷新atomic_refresh.txt --11G物化视图刷新有1个参数atomic_refresh. --如果为false,采用的方式是truncate,再使用/*+ append */ 提示insert.这样redo最少,但是刷新期间无法访问. --如果为true,采用的方式是delete,再insert.这样产生许多redo与undo.这样在刷新期间访问没问题,最多有点慢. --自己做一个测试: 1.建立测试环境: SCOTT@test> @ver1 PORT			

		

	

				

		

			

                [20150610]使用物化视图同步数据.txt
			

		

		

									

				[20150610]使用物化视图同步数据.txt --昨天听别人的一个需求要同步一个表的数据,要求使用golden gate有点小题大作.实际上物化事务就可以了,自己以前做过一些测试,也 --许没做记录,这次做一个记录. 1.建立测试环境: --源数据库10g  10.2.0.4.0  IP=192.168.100.89 --同步表T. create table t ( id number CONSTRAINTS pk_t primary key , name varchar2(20)); in			

		

	

				

		

			

                [20120307]查看v$session视图的定义.txt
			

		

		

									

				[20120307]查看v$session视图的定义.txt 今天查看v$session的原始定义,查询GV$FIXED_VIEW_DEFINITION SQL> column VIEW_DEFINITION format a100 SQL> select * from GV$FIXED_VIEW_DEFINITION where view_name='GV$SESSION';    INST_ID VIEW_NAME                      VIEW_DEFINITION			

		

	

				

		

			

                [20131027]11G的内部视图X$DBGALERTEXT.txt
			

		

		

									

				[20131027]11G的内部视图X$DBGALERTEXT.txt 链接:http://www.askmaclean.com/archives/11g%E6%96%B0%E7%89%B9%E6%80%A7xdbgalertext%E4%B8%80%E4%B8%AA%E5%BE%88%E9%85%B7%E7%9A%84%E5%86%85%E9%83%A8%E8%A7%86%E5%9B%BE.html 以前我们看alert*.log文件,一般直接进入目录,直接查看.当然也可以通过外部表来访问al			

		

	

				

		

			

                [20170502]11G查询隐含参数视图.txt
			

		

		

									

				[20170502]11G查询隐含参数视图GV$SYSTEM_PARAMETER3.txt --//oracle 存在许多隐含参数,一直以为oracle没有提供正常的视图查询该内容,实际上oracle 11G已经提供这方面的功能,只不过oracle并不公开. --//自己也是偶然发现: 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER -------------------------			

		

	

				

		

			

                ORACLE 部分HINT
			

		

		

									

				本文章为学习笔记,为了方便查阅,收录于此 大部分为以前学习基于CBO的ORACLE优化一书(崔华著),加上自己的实验 如果记录有误请指出 1.gather_plan_statistics HINT 用于记录SQL执行时的额外信息,如果实际执行次数,执行时间,物理逻辑读等.     select /*+  gather_plan_statistics */ count(*) from pp;      后执行如下的任何一句可以查看相应的信息     select * from table(dbms			

		

	

				

		

			

                AngularJS学习笔记--002--Angular JS路由插件ui.router源码解析
			

		

		

									

				路由(route),几乎所有的MVC(VM)框架都应该具有的特性,因为它是前端构建单页面应用(SPA)必不可少的组成部分. 那么,对于angular而言,它自然也有内置的路由模块:叫做ngRoute. 不过,大家很少用它,因为它的功能太有限,往往不能满足开发需求!! 于是,一个基于ngRoute开发的第三方路由模块,叫做ui.router,受到了大家的"追捧". ngRoute vs ui.router 首先,无论是使用哪种路由,作为框架额外的附加功能,它们都将以模块依赖的形式被引入,			

		

	

				

		

			

                Oracle结果集缓存（Result Cache）--服务器、客户端、函数缓存
			

		

		

									

				Oracle结果集缓存(Result Cache)--服务器.客户端.函数缓存 在11g中,Oracle提供了结果集缓存特性.该缓存是在共享内存中存储全部的结果集,如果一个查询SQL被执行,且它对应的结果集在缓存中,那么,该SQL的几乎全部开销都可以避免.这些开销包括,解析时间.逻辑读.物理读和任意的通常可能会遭遇的争用.但是,在实际的情况中,结果集缓存仅在少数的情况下是有效的,原因有如下几点: (1)有数据重叠的多个SQL会在缓存中保存冗余的数据. (2)对依赖对象的任何改变(包括对查询中引用			

		

	

				

		

			

                [20131001]ctas与视图user_tables，user_objects.txt
			

		

		

									

				[20131001]ctas与视图user_tables,user_objects.txt 链接:http://www.itpub.net/thread-1819520-1-1.htmlSQL> show user ;USER is "SCOTT"SQL> purge recyclebin; Recyclebin purged. SQL> select table_name from user_tables where table_name = 'NO_EXISTS'