[20180503]视图提示使用索引.txt

简介: [20180503]视图提示使用索引.txt --//昨天优化sql语句,想提示某个视图里面的表使用索引,有点忘记ZALBB以前讲过的提示写法,看了以前链接, --//自己在写一个例子便于记忆.

[20180503]视图提示使用索引.txt

--//昨天优化sql语句,想提示某个视图里面的表使用索引,有点忘记ZALBB以前讲过的提示写法,看了以前链接,
--//自己在写一个例子便于记忆.

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1 as select * from all_objects;
create table t2 as select * from all_objects;
create index i_t1_object_id on t1(object_id);
create index i_t2_object_id on t2(object_id);
create view v_t12 as select * from t1 union all select * from t2;

--//分析表略.

2.编辑测试脚本:
$ cat e1.txt
set term off
select * from v_t12 where object_id<5000;
set term on
@ &r/dpc '' ''

--//执行计划如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  dpmhgbybp6bhc, child number 0
-------------------------------------
select * from  v_t12 where object_id<50000
Plan hash value: 2302642357
Plan hash value: 2302642357
------------------------------------------------------------------------------
| Id  | Operation           | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |        |       |   675 (100)|          |
|   1 |  VIEW               | V_T12 |  93342 |    10M|   675   (1)| 00:00:09 |
|   2 |   UNION-ALL         |       |        |       |            |          |
|*  3 |    TABLE ACCESS FULL| T1    |  46671 |  4466K|   338   (1)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL| T2    |  46671 |  4466K|   338   (1)| 00:00:05 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1 / V_T12@SEL$1
   2 - SET$1
   3 - SEL$2 / T1@SEL$2
   4 - SEL$3 / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("OBJECT_ID"<50000)
   4 - filter("OBJECT_ID"<50000)

--//不考虑执行效率,如何避免全表扫描.

3.使用提示:
--//修改如下:
$ cat e1.txt
set term off
select/*+ index(v_t12.t1 i_t1_object_id) index(v_t12.t2 i_t2_object_id) */ * from  v_t12 where object_id<50000;
set term on
@ &r/dpc '' ''
--//注意写法:视图名.表名.

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6bum48jat8jga, child number 0
-------------------------------------
select/*+ index(v_t12.t1 i_t1_object_id) index(v_t12.t2 i_t2_object_id)
*/ * from  v_t12 where object_id<50000
Plan hash value: 17053456
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |   675 (100)|          |
|   1 |  VIEW                         | V_T12          |  93342 |    10M|   675   (1)| 00:00:09 |
|   2 |   UNION-ALL                   |                |        |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1             |  46671 |  4466K|   802   (1)| 00:00:10 |
|*  4 |     INDEX RANGE SCAN          | I_T1_OBJECT_ID |  46671 |       |   105   (0)| 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2             |  46671 |  4466K|   802   (1)| 00:00:10 |
|*  6 |     INDEX RANGE SCAN          | I_T2_OBJECT_ID |  46671 |       |   105   (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1 / V_T12@SEL$1
   2 - SET$1
   3 - SEL$2 / T1@SEL$2
   4 - SEL$2 / T1@SEL$2
   5 - SEL$3 / T2@SEL$3
   6 - SEL$3 / T2@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("OBJECT_ID"<50000)
   6 - access("OBJECT_ID"<50000)

--//经常忘记这样的方法,做一个记录.

4.实际上可以通过前面的outline加入提示:
select * from v_t12 where object_id<5000;
@ &r/dpc '' ''

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "V_T12"@"SEL$1")
      FULL(@"SEL$3" "T2"@"SEL$3")
      FULL(@"SEL$2" "T1"@"SEL$2")
      END_OUTLINE_DATA
  */

--//修改如下,2种方式都可以使用索引.
$ cat e1.txt
set term off
select /*+ INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("T2"."OBJECT_ID")) INDEX_RS_ASC(@"SEL$2" "T1"@"SEL$2" ("T1"."OBJECT_ID")) */ * from  v_t12 where object_id<50000;
--select /*+ index(@"SEL$3" "T2"@"SEL$3") index(@"SEL$2" "T1"@"SEL$2") */ * from  v_t12 where object_id<50000;
set term on
@ &r/dpc '' outline

Plan hash value: 17053456

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |   675 (100)|          |
|   1 |  VIEW                         | V_T12          |  93342 |    10M|   675   (1)| 00:00:09 |
|   2 |   UNION-ALL                   |                |        |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1             |  46671 |  4466K|   802   (1)| 00:00:10 |
|*  4 |     INDEX RANGE SCAN          | I_T1_OBJECT_ID |  46671 |       |   105   (0)| 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2             |  46671 |  4466K|   802   (1)| 00:00:10 |
|*  6 |     INDEX RANGE SCAN          | I_T2_OBJECT_ID |  46671 |       |   105   (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1 / V_T12@SEL$1
   2 - SET$1
   3 - SEL$2 / T1@SEL$2
   4 - SEL$2 / T1@SEL$2
   5 - SEL$3 / T2@SEL$3
   6 - SEL$3 / T2@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "V_T12"@"SEL$1")
      INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("T2"."OBJECT_ID"))
      INDEX_RS_ASC(@"SEL$2" "T1"@"SEL$2" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("OBJECT_ID"<50000)
   6 - access("OBJECT_ID"<50000)

5.使用qb_name:

SCOTT@book> create or replace view v_t12 as select /*+ qb_name(t1) */ * from t1 union all select /*+ qb_name(t2) */* from t2;
View created.

$ cat e1.txt
set term off
select /*+ index(@"T2" "T2"@"T2") index(@"T1" "T1"@"T1") */ * from  v_t12 where object_id<50000;
set term on
@ &r/dpc '' outline

Plan hash value: 17053456

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |   675 (100)|          |
|   1 |  VIEW                         | V_T12          |  93342 |    10M|   675   (1)| 00:00:09 |
|   2 |   UNION-ALL                   |                |        |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1             |  46671 |  4466K|   802   (1)| 00:00:10 |
|*  4 |     INDEX RANGE SCAN          | I_T1_OBJECT_ID |  46671 |       |   105   (0)| 00:00:02 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2             |  46671 |  4466K|   802   (1)| 00:00:10 |
|*  6 |     INDEX RANGE SCAN          | I_T2_OBJECT_ID |  46671 |       |   105   (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1 / V_T12@SEL$1
   2 - SET$1
   3 - T1    / T1@T1
   4 - T1    / T1@T1
   5 - T2    / T2@T2
   6 - T2    / T2@T2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"T1")
      OUTLINE_LEAF(@"T2")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"T1")
      OUTLINE(@"T2")
      NO_ACCESS(@"SEL$1" "V_T12"@"SEL$1")
      INDEX_RS_ASC(@"T2" "T2"@"T2" ("T2"."OBJECT_ID"))
      INDEX_RS_ASC(@"T1" "T1"@"T1" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("OBJECT_ID"<50000)
   6 - access("OBJECT_ID"<50000)

目录
相关文章
|
设计模式 数据库
Access2016删除主键列(图文操作)错误提示:若要删除主键,请在“设计”视图中打开该表并删除主键字段
Access2016删除主键列(图文操作)错误提示:若要删除主键,请在“设计”视图中打开该表并删除主键字段
Access2016删除主键列(图文操作)错误提示:若要删除主键,请在“设计”视图中打开该表并删除主键字段
|
Oracle 关系型数据库 索引
[20180408]那些函数索引适合字段的查询.txt
[20180408]那些函数索引适合字段的查询.txt --//一般不主张建立函数索引,往往是开发的无知,使用trunc等函数,实际上一些函数也可以用于字段的查询. --//以前零碎的写过一些,放假看了https://blog.
1069 0
|
索引 关系型数据库 Oracle
[20171202]关于函数索引的状态.txt
[20171202]关于函数索引的状态.txt --//我曾经在一篇贴子提到索引可以disable吗?链接: --//http://blog.itpub.net/267265/viewspace-2123537/ --//实际上仅仅函数索引能disable,为什么呢?实际上自己以前并不搞清楚实际上这个跟oracle使用函数的特殊性有关.
1212 0
|
SQL 监控 测试技术
[20171113]修改表结构删除列相关问题4.txt
[20171113]修改表结构删除列相关问题4.txt --//连续写了3篇修改表结构删除列的相关问题,链接如下: http://blog.itpub.net/267265/viewspace-2147158/ http://blog.
965 0
|
SQL Oracle 关系型数据库
[20171113]修改表结构删除列相关问题.txt
[20171113]修改表结构删除列相关问题.txt --//维护表结构删除字段一般都是先 ALTER TABLE SET UNUSED (); --//然后等空闲时候删除列.
832 0
|
Oracle 关系型数据库
[20171113]修改表结构删除列相关问题3.txt
[20171113]修改表结构删除列相关问题3.txt --//维护表结构删除字段一般都是先 ALTER TABLE SET UNUSED (); --//然后等空闲时候删除列.
748 0
|
Oracle 关系型数据库 数据库管理
[20171113]修改表结构删除列相关问题2.txt
[20171113]修改表结构删除列相关问题2.txt --//测试看看修改表结构删除列产生的redo向量,对这些操作细节不了解,分析redo看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING             ...
1028 0
|
Oracle 关系型数据库
[20170502]11G查询隐含参数视图.txt
[20170502]11G查询隐含参数视图GV$SYSTEM_PARAMETER3.txt --//oracle 存在许多隐含参数,一直以为oracle没有提供正常的视图查询该内容,实际上oracle 11G已经提供这方面的功能,只不过oracle并不公开.
989 0