ITPub博客

首页 > 数据库 > Oracle > [20150901]提示USE_CONCAT.txt

[20150901]提示USE_CONCAT.txt

原创 Oracle 作者:lfree 时间:2015-09-01 16:03:15 0 删除 编辑

[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')))

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1788598/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2008-01-03

  • 博文量
    2470
  • 访问量
    6277389