AI 助理
备案控制台
开发者社区 数据库 文章 正文

[20150901]提示USE_CONCAT.txt

2015-09-01 823
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20150901]提示USE_CONCAT.txt --最近一直在使用这个提示USE_CONCAT ，开发真的不要这样写代码，应该分开写,这样的sql技巧真的不能乱用！分开写对应的sql语句优 --化选择索引的建立更方便一些。

[20150901]提示USE_CONCAT.txt

--最近一直在使用这个提示USE_CONCAT ，开发真的不要这样写代码，应该分开写,这样的sql技巧真的不能乱用！分开写对应的sql语句优
--化选择索引的建立更方便一些。
--因为程序大量使用非绑定变量，没有办法我只能通过参数cursor_sharing=force来控制这种行为。结果导致这些问题，参考链接：
--http://blog.itpub.net/267265/viewspace-1771727/
--在这个学习中，遇到一些问题，做一些总结,并且参看杨大师的链接：
--http://blog.itpub.net/4227/viewspace-68623/

1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table t as select rownum id, a.* from dba_objects a;
Table created.

SCOTT@test> exec dbms_stats.gather_table_stats(user, 'T')
PL/SQL procedure successfully completed.

2.测试:
SCOTT@test> select * from t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1';
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  asuuxtb4s0n0k, child number 0
-------------------------------------
select * from t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1'
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |   325 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   4034 |   401K|   325   (1)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("OWNER"='SYS1' OR "OBJECT_TYPE"='PACKAGE1'))

3.使用提示/*+ use_concat */ 看看：
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5augfd6vn2uzd, child number 0
-------------------------------------
select /*+ use_concat */ * from t where owner = 'SYS1' or OBJECT_TYPE ='PACKAGE1'
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |   325 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   4034 |   401K|   325   (1)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("OWNER"='SYS1' OR "OBJECT_TYPE"='PACKAGE1'))

--可以发现提示无效。

4.建立一个索引：
SCOTT@test> create index i_t_owner on t (owner);
Index created.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5augfd6vn2uzd, child number 0
-------------------------------------
select /*+ use_concat */ * from t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1'
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |   325 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |   4034 |   401K|   325   (1)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("OWNER"='SYS1' OR "OBJECT_TYPE"='PACKAGE1'))

SCOTT@test> create index i_t_object_type on t (object_type);
Index created.

SCOTT@test> select /*+ use_concat */ * from t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1';
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5augfd6vn2uzd, child number 0
-------------------------------------
select /*+ use_concat */ * from t where owner = 'SYS1' or OBJECT_TYPE =
'PACKAGE1'
Plan hash value: 161390099
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |        |       |   154 (100)|          |
|   1 |  CONCATENATION               |                 |        |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T               |   1655 |   164K|    80   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_T_OBJECT_TYPE |   1655 |       |     5   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T               |   2379 |   236K|    74   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | I_T_OWNER       |   2431 |       |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1_1 / T@SEL$1
   3 - SEL$1_1 / T@SEL$1
   4 - SEL$1_2 / T@SEL$1_2
   5 - SEL$1_2 / T@SEL$1_2
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_TYPE"='PACKAGE1')
   4 - filter(LNNVL("OBJECT_TYPE"='PACKAGE1'))
   5 - access("OWNER"='SYS1')

--这样的提示才生效。按照杨老师的说法：

在建立索引以前，Oracle对T的访问路径只存在一种，即全表扫描。由于Oracle只存在一种执行计划，因此Oracle忽略了USE_CONCAT提示
。当建立索引之后，Oracle存在着多种访问路径，包括对T全表扫描和对T的IND_T_OWNER索引扫描以及对T的IND_T_OBJECT_TYPE索引扫描
。由于存在多种访问途径，因此优化器按照提示生成相应的计划，优化器根据提示生成执行计划的同时，对于提示中没有给出的部分，优
化器会根据代价的大小来确定。而在这个查询中，全表扫描肯定比通过索引扫描代价要小，因此，优化器生成了对T表执行两次全表扫描
，然后进行CONCATENATION的执行计划。

--但是我存在一个困惑，首先我建立第1个索引时，以及存在多个执行路径，使用提示应该有效！但是依旧不行。

5.继续测试:

SCOTT@test> select /*+ use_concat */ * from t where owner = 'SYS' or OBJECT_TYPE = 'PACKAGE';

--注意我删除了1.这样输出很多。。。。

SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  83ctsbj5md44w, child number 0
-------------------------------------
select /*+ use_concat */ * from t where owner = 'SYS' or OBJECT_TYPE ='PACKAGE'
Plan hash value: 161390099
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |       |   154 (100)|          |  33662 |00:00:00.21 |    1428 |
|   1 |  CONCATENATION               |                 |      1 |        |       |            |          |  33662 |00:00:00.21 |    1428 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T               |      1 |   1655 |   164K|    80   (0)| 00:00:01 |   1336 |00:00:00.01 |     153 |
|*  3 |    INDEX RANGE SCAN          | I_T_OBJECT_TYPE |      1 |   1655 |       |     5   (0)| 00:00:01 |   1336 |00:00:00.01 |      13 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T               |      1 |   2379 |   236K|    74   (0)| 00:00:01 |  32326 |00:00:00.14 |    1275 |
|*  5 |    INDEX RANGE SCAN          | I_T_OWNER       |      1 |   2431 |       |     6   (0)| 00:00:01 |  32963 |00:00:00.05 |     233 |
------------------------------------------------------------------------------------------------------------------------------------------
--可以发现因为没有直方图，两个执行路径都选择索引。

--建立直方图看看：
SCOTT@test> exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',No_Invalidate => false)
PL/SQL procedure successfully completed.

SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0vdtd86uwfxv5, child number 0
-------------------------------------
select /*+ use_concat */ * from t where owner = 'SYS' or OBJECT_TYPE='PACKAGE'
Plan hash value: 2759760844
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |       |   396 (100)|          |  33662 |00:00:00.13 |    1475 |
|   1 |  CONCATENATION               |                 |      1 |        |       |            |          |  33662 |00:00:00.13 |    1475 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T               |      1 |   1461 |   145K|    71   (0)| 00:00:01 |   1336 |00:00:00.01 |     153 |
|*  3 |    INDEX RANGE SCAN          | I_T_OBJECT_TYPE |      1 |   1461 |       |     4   (0)| 00:00:01 |   1336 |00:00:00.01 |      13 |
|*  4 |   TABLE ACCESS FULL          | T               |      1 |  31957 |  3183K|   325   (1)| 00:00:01 |  32326 |00:00:00.05 |    1322 |
------------------------------------------------------------------------------------------------------------------------------------------

--也就是要在两个索引都存在的情况下，提示才有效。如果删除一个索引提示就无效。真的吗？

6.换一种，我们开发的奇特写法：

select /*+ use_concat */ * from t where (:a = 0 and owner = 'SYS1') or (:a =1 or OBJECT_TYPE = 'PACKAGE1');

--注意这种写法与上面的不等效，是排他的。

SCOTT@test> variable a number ;
SCOTT@test> exec :a := 1
PL/SQL procedure successfully completed.

SCOTT@test> select /*+ use_concat */ * from t where (:a = 0 and owner = 'SYS1') or (:a =1 and OBJECT_TYPE = 'PACKAGE1');
no rows selected

SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fpumw3136jmdf, child number 0
-------------------------------------
select /*+ use_concat */ * from t where (:a = 0 and owner = 'SYS1') or
(:a =1 and OBJECT_TYPE = 'PACKAGE1')
Plan hash value: 3659371398
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |       |     4 (100)|          |      0 |00:00:00.01 |       2 |
|   1 |  CONCATENATION                |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       2 |
|*  2 |   FILTER                      |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T               |      1 |      7 |   714 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  4 |     INDEX RANGE SCAN          | I_T_OBJECT_TYPE |      1 |      7 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  5 |   FILTER                      |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T               |      0 |      7 |   714 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  7 |     INDEX RANGE SCAN          | I_T_OWNER       |      0 |      7 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1_1 / T@SEL$1
   4 - SEL$1_1 / T@SEL$1
   6 - SEL$1_2 / T@SEL$1_2
   7 - SEL$1_2 / T@SEL$1_2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((4 3) (3 4) (7 6) (6 7)))
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."OBJECT_TYPE"))
      INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."OWNER"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:A=1)
   4 - access("OBJECT_TYPE"='PACKAGE1')
   5 - filter(:A=0)
   6 - filter((LNNVL("OBJECT_TYPE"='PACKAGE1') OR LNNVL(:A=1)))
   7 - access("OWNER"='SYS1')

--id=6,7 starts=0，并没有执行。因为  5 - filter(:A=0)为假。可以很好的使用提示！另外可以发现oracle的执行从最后条件开始判
--断的,先走I_T_OBJECT_TYPE索引。

7.删除其中1个索引看看。这样与我实际情况一样，因为分支太多(测试仅仅2个)，我那里有1堆。许多分支对应的索引没有建立。

SCOTT@test> drop index i_t_owner ;
Index dropped.

SCOTT@test> select /*+ use_concat */ * from t where (:a = 0 and owner = 'SYS1') or (:a =1 and OBJECT_TYPE = 'PACKAGE1');
no rows selected

SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fpumw3136jmdf, child number 0
-------------------------------------
select /*+ use_concat */ * from t where (:a = 0 and owner = 'SYS1') or
(:a =1 and OBJECT_TYPE = 'PACKAGE1')
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |   325 (100)|          |      0 |00:00:00.02 |    1161 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |   102 |   325   (1)| 00:00:01 |      0 |00:00:00.02 |    1161 |
--------------------------------------------------------------------------------------------------------------------

--提示再次失效。而使用上面的提示/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */

SCOTT@test> select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t where (:a = 0 and owner = 'SYS1') or (:a =1 and OBJECT_TYPE = 'PACKAGE1');
no rows selected

SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gmzdjg23dds1b, child number 0
-------------------------------------
select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t where
(:a = 0 and owner = 'SYS1') or (:a =1 and OBJECT_TYPE = 'PACKAGE1')
Plan hash value: 2488649795
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |       |   327 (100)|          |      0 |00:00:00.01 |       2 |
|   1 |  CONCATENATION                |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       2 |
|*  2 |   FILTER                      |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       2 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T               |      1 |      7 |   714 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  4 |     INDEX RANGE SCAN          | I_T_OBJECT_TYPE |      1 |      7 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  5 |   FILTER                      |                 |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|*  6 |    TABLE ACCESS FULL          | T               |      0 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------------------------------

--说明1点，提示还是有效的，必须加入这些参数。有这这些信息(我的测试必须要有OR_PREDICATES(1)))，回头看看开始我执行的语句，加入如下提示：

SCOTT@test> select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1';
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8nx2svs96s1rj, child number 0
-------------------------------------
select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t where
owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1'
Plan hash value: 2759760844
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |       |   327 (100)|          |      0 |00:00:00.01 |    1163 |
|   1 |  CONCATENATION               |                 |      1 |        |       |            |          |      0 |00:00:00.01 |    1163 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T               |      1 |      7 |   714 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN          | I_T_OBJECT_TYPE |      1 |      7 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS FULL          | T               |      1 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |    1161 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1_1 / T@SEL$1
   3 - SEL$1_1 / T@SEL$1
   4 - SEL$1_2 / T@SEL$1_2
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OBJECT_TYPE"='PACKAGE1')
   4 - filter(("OWNER"='SYS1' AND LNNVL("OBJECT_TYPE"='PACKAGE1')))

--继续删除索引：
SCOTT@test> drop index i_t_object_type ;
Index dropped.

SCOTT@test> select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1';
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8nx2svs96s1rj, child number 0
-------------------------------------
select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t where
owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1'
Plan hash value: 44128673
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   649 (100)|          |      0 |00:00:00.02 |    2322 |
|   1 |  CONCATENATION     |      |      1 |        |       |            |          |      0 |00:00:00.02 |    2322 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |    1161 |
|*  3 |   TABLE ACCESS FULL| T    |      1 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |    1161 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1_1 / T@SEL$1
   3 - SEL$1_2 / T@SEL$1_2
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_TYPE"='PACKAGE1')
   3 - filter(("OWNER"='SYS1' AND LNNVL("OBJECT_TYPE"='PACKAGE1')))

--可以发现并不像杨老师那样，实际上加入更多的提示，还是可以选择这样的执行计划的。有了这样的执行计划，看Predicate
--Information可以容易确定建立那些索引(不过还是很烦，看看我的链接就明白了http://blog.itpub.net/267265/viewspace-1771727/)。

8.视图呢？
SCOTT@test> create view v_t as select * from t;
View created.

SCOTT@test> select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from v_t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1';
no rows selected

SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8m5qs8kz1h52q, child number 0
-------------------------------------
select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from v_t where
owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1'
Plan hash value: 44128673
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   649 (100)|          |      0 |00:00:00.02 |    2322 |
|   1 |  CONCATENATION     |      |      1 |        |       |            |          |      0 |00:00:00.02 |    2322 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |    1161 |
|*  3 |   TABLE ACCESS FULL| T    |      1 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |    1161 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1_1 / T@SEL$2
   3 - SEL$F5BB74E1_2 / T@SEL$F5BB74E1_2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1_1")
      USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(1))
      OUTLINE_LEAF(@"SEL$F5BB74E1_2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      FULL(@"SEL$F5BB74E1_1" "T"@"SEL$2")
      FULL(@"SEL$F5BB74E1_2" "T"@"SEL$F5BB74E1_2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_TYPE"='PACKAGE1')
   3 - filter(("OWNER"='SYS1' AND LNNVL("OBJECT_TYPE"='PACKAGE1')))

-- 有效。可以提示并不是太严谨，实际看Outline Data是 USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(1))。

--好了根据以上特点换1种写法：

SCOTT@test> select /*+ USE_CONCAT(  OR_PREDICATES(1)) */ * from v_t where owner = 'SYS1' or OBJECT_TYPE = 'PACKAGE1';
no rows selected

SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  anq0zbzczqwq4, child number 0
-------------------------------------
select /*+ USE_CONCAT( OR_PREDICATES(1)) */ * from v_t where owner =
'SYS1' or OBJECT_TYPE = 'PACKAGE1'
Plan hash value: 44128673
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   649 (100)|          |      0 |00:00:00.02 |    2322 |
|   1 |  CONCATENATION     |      |      1 |        |       |            |          |      0 |00:00:00.02 |    2322 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |    1161 |
|*  3 |   TABLE ACCESS FULL| T    |      1 |      7 |   714 |   325   (1)| 00:00:01 |      0 |00:00:00.01 |    1161 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1_1 / T@SEL$2
   3 - SEL$F5BB74E1_2 / T@SEL$F5BB74E1_2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1_1")
      USE_CONCAT(@"SEL$F5BB74E1" OR_PREDICATES(1))
      OUTLINE_LEAF(@"SEL$F5BB74E1_2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      FULL(@"SEL$F5BB74E1_1" "T"@"SEL$2")
      FULL(@"SEL$F5BB74E1_2" "T"@"SEL$F5BB74E1_2")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_TYPE"='PACKAGE1')
   3 - filter(("OWNER"='SYS1' AND LNNVL("OBJECT_TYPE"='PACKAGE1')))

文章标签：
索引
SQL
关系型数据库
Oracle
Perl
lfreeali
目录
相关文章
爱你三千遍斯塔克
|
6月前
rep-wc-管道符 grep [-n ] 关键字 文件路径,grep -n “code“ test.txt,grep -n “code“ test.txt 显示行号的写法，wc test.txt全
rep-wc-管道符 grep [-n ] 关键字 文件路径,grep -n “code“ test.txt,grep -n “code“ test.txt 显示行号的写法，wc test.txt全
爱你三千遍斯塔克
39 0
魏红斌
|
8月前
|
弹性计算 运维 Shell
批量将txt 文件修改为doc 文件
【4月更文挑战第29天】
魏红斌
69 1
lfreeali
|
SQL Oracle 关系型数据库
[20180310]12c exp 无法dirct的情况.txt
[20180310]12c exp 无法dirct的情况.txt --//前一阵子测试.exp 无法dirct的情况的链接: http://blog.itpub.net/267265/viewspace-2151290/ --//12c 改进增加字段与缺省值的情况,允许不要加not null修改表块.
lfreeali
1498 0
lfreeali
|
SQL Oracle 关系型数据库
[20180224]exp参数RECORDLENGTH.txt
[20180224]exp参数RECORDLENGTH.txt --//虽然已经很少使用exp导致,如果加入direct=y参数,设置RECORDLENGTH参数能加快数据导出.
lfreeali
1594 0
lfreeali
|
SQL Oracle 关系型数据库
[20180226]exp 无法dirct的情况.txt
[20180226]exp 无法dirct的情况.txt http://blog.csdn.net/leshami/article/details/9146023 传统路径导出 VS 直接路径导出(oracle exp direct=y) 1、两者的差异 a、 Conventional path Export     传统路径模式使用SQL SELECT语句抽取表数据。
lfreeali
1234 0
lfreeali
|
关系型数据库 数据库管理 Oracle
[20180208]ezconnect语法.txt
[20180208]ezconnect语法.txt --昨天看书Oracle Database11g DBA Handbook.pdf,Using Easy Connect Naming P561.
lfreeali
1078 0
lfreeali
|
存储 Oracle 关系型数据库
[20171218]varchar2(4000)如何保存.txt
varchar2(4000)如何保存
lfreeali
1292 0
lfreeali
|
SQL Oracle 关系型数据库
[20170918]exp 直接路径导出.txt
[20170918]exp 直接路径导出.txt --//昨天看链接blogs.oracle.com/database4cn/%e5%af%b9%e4%ba%8e%e4%b8%80%e4%b8%aa%e9%9d%9e%e7%a9%ba%e5%ad%97%e6%ae%...
lfreeali
1480 0
lfreeali
|
SQL Oracle 关系型数据库
[20170112]为什么提示不一样.txt
[20170112]为什么提示不一样.txt --问题链接:http://www.itpub.net/thread-2074643-1-1.html --自己也测试看看: 1.
lfreeali
865 0
lfreeali
|
Oracle 关系型数据库 索引
[20160118]提示index_join.txt
[20160118]提示index_join.txt --生产系统优化，遇到1例使用index_join提示的异常情况，通过例子来说明： 1.环境： 1.环境： SCOTT@book> @ &r/ver1 PORT_STRING               ...
lfreeali
928 0

热门文章

最新文章

  • 1
    如何优雅打印nginx header和body
  • 2
    Swin Transformer实战：使用 Swin Transformer实现图像分类
  • 3
    5G中的调制技术：从QPSK到256QAM，赋能高速率通信
  • 4
    数据包络分析（DEA）
  • 5
    mysql 8.0 使用 XtraBackup增量备份及恢复
  • 6
    MFC中将编辑框文本转换成整数，从而实现两个整数相加。
  • 7
    现在到底还该不该买房？
  • 8
    为企业用户分析PR策略，TrendKite获1630万美元融资
  • 9
    运维平台的建设思考-元数据管理（二）
  • 10
    uploadify2.1.4上传组件应用实例
  • 1
    操作系统智能助手OS Copilot新功能上线，快来体验吧
    50
  • 2
    超好看的404提示页面HTML源码
    106
  • 3
    【05】flutter完成注册页面完善样式bug-增加自定义可复用组件widgets-严格规划文件和目录结构-规范入口文件-开发完整的社交APP-前端客户端开发+数据联调|以优雅草商业项目为例做开发-flutter开发-全流程-商业应用级实战开发-优雅草央千澈
    100
  • 4
    [Git] 深入理解 Git 的客户端与服务器角色
    37
  • 5
    [Git] 如何理解及基础操作
    42
  • 6
    [Linux工具] Makefile
    37
  • 7
    为什么C/C++编译腰要先完成汇编
    36
  • 8
    深入理解GCC 和 G++ 编译器
    36
  • 9
    os-copilot安装与多项功能测评，帮助开发人员PHP环境排查问题
    70
  • 10
    Python3虚拟环境venv
    53

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    阿里云oss简介和如何对接使用