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

[20150901]提示USE_CONCAT.txt

2015-09-01 798
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [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
目录
相关文章
lfreeali
|
SQL Oracle 关系型数据库
[20180224]exp参数RECORDLENGTH.txt
[20180224]exp参数RECORDLENGTH.txt --//虽然已经很少使用exp导致,如果加入direct=y参数,设置RECORDLENGTH参数能加快数据导出.
lfreeali
1561 0
lfreeali
|
Oracle 关系型数据库 OLAP
[20180224]expdp query 写法问题.txt
[20180224]expdp query 写法问题.txt --//如果使用expdp/impdp导入导出,如果参数复杂,最好的建议使用参数文件.避免各种问题.通过简单的例子说明问题.
lfreeali
1185 0
lfreeali
|
关系型数据库 数据库管理 Oracle
[20180208]ezconnect语法.txt
[20180208]ezconnect语法.txt --昨天看书Oracle Database11g DBA Handbook.pdf,Using Easy Connect Naming P561.
lfreeali
1055 0
lfreeali
|
存储 Oracle 关系型数据库
[20171218]varchar2(4000)如何保存.txt
varchar2(4000)如何保存
lfreeali
1257 0
lfreeali
|
关系型数据库 Oracle Linux
[20171206]SQLTUNE_CATEGORY参数.txt
[20171206]SQLTUNE_CATEGORY参数.txt --//今天提示别人使用sql profile优化语句,使用DBMS_SQLTUNE.import_sql_profile的引入替换功能.
lfreeali
1278 0
lfreeali
|
SQL Oracle 关系型数据库
[20170112]为什么提示不一样.txt
[20170112]为什么提示不一样.txt --问题链接:http://www.itpub.net/thread-2074643-1-1.html --自己也测试看看: 1.
lfreeali
835 0
lfreeali
|
关系型数据库 Oracle
[20160806]12c 与 WMSYS.WM_CONCAT.txt
[20160806]12c 与 WMSYS.WM_CONCAT.txt --前几天有人问临时表空间消耗的问题,链接: http://www.itpub.net/thread-2065053-1-1.
lfreeali
942 0
lfreeali
|
Oracle 关系型数据库 索引
[20160118]提示index_join.txt
[20160118]提示index_join.txt --生产系统优化，遇到1例使用index_join提示的异常情况，通过例子来说明： 1.环境： 1.环境： SCOTT@book> @ &r/ver1 PORT_STRING               ...
lfreeali
913 0
lfreeali
[20160111]使用提示no_unnest.txt
[20160111]使用提示no_unnest.txt --今天被人问及这个提示,我记忆使用这个提示,就会出现filter过滤条件.我以前遇到这种语句,一般两者提示都使用看看那个效果好.
lfreeali
750 0
lfreeali
|
索引
[20160112]提示NUM_INDEX_KEY.txt
[20160112]提示NUM_INDEX_KEY.txt --如果我们查询,假设建立的索引是id1,id2的复合索引. select * from t where id1=:x and id2 in(1,100); --一般执行计划通过索引access id1=:X,然后再过滤id2等于1和100的值.
lfreeali
967 0

热门文章

最新文章

  • 1
    企业应用架构实践（复杂性应对之道）
  • 2
    SolidWorks2021中文版【Solid Works 附补丁】完美破解版下载 安装教程
  • 3
    3+1保障：高可用系统稳定性是如何炼成的？
  • 4
    大数据上手实战！《Elasticsearch 实战进阶营》第二季限时免费报名啦
  • 5
    OLAP与OLTP介绍
  • 6
    浅显易懂的机器学习（八）—— 随机森林分类
  • 7
    Windows 2008 AD 备份
  • 8
    《Ext JS权威指南》——2.9节为本书示例准备一个模板
  • 9
    NS2原理
  • 10
    Asp.net MVC2.0系列文章-显示列表和详细页面操作
  • 1
    Java中数组详解
    40
  • 2
    智能评估时代：SurveyKing开源问卷系统YYDS
    62
  • 3
    LabVIEW编程LabVIEW开发高级数据采集技术 操作数字IO 例程与相关资料
    37
  • 4
    【大模型】描述与 LLM 相关的个人项目或感兴趣的领域
    37
  • 5
    【大模型】使用哪些资源来了解 LLM 的最新进展？
    47
  • 6
    LabVIEW编程LabVIEW开发高级数据采集技术 计数器定时器的操作 例程与相关资料
    24
  • 7
    LabVIEW编程LabVIEW开发高级数据采集技术 模拟波形的生成 例程与相关资料
    22
  • 8
    Linux：进程等待 & 进程替换
    27
  • 9
    LabVIEW编程LabVIEW开发高级数据采集技术 同步 例程与相关资料
    19
  • 10
    LabVIEW编程LabVIEW开发高级数据采集技术定时与触发 例程与相关资料
    17

    • 相关电子书

    更多
  • 成功案例-Par...[幸运杰哥].1512720360.pdf
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）