OR扩展

最新推荐文章于 2020-12-24 14:03:49 发布
weixin_30682415
最新推荐文章于 2020-12-24 14:03:49 发布
阅读量109
点赞数
文章标签：数据库开发工具
原文链接：http://www.cnblogs.com/zhaoyangjian724/p/3797867.html
版权
<pre name="code" class="sql">SQL> select substr(xx.acct_no,1,5) agent_org, xx.vou_kind,sum( xx.trans_amt) trans_amt
                              from (
                                     select * from dwf.F_EVT_SAVD_LIST 
                                     where trans_date >= to_date('2013-10-10', 'YYYY-MM-DD')
                                     AND trans_date <= to_date('2014-03-31', 'YYYY-MM-DD')
                                   ) xx
                              where  ( xx.vou_kind in ('3', '4')
                                       or
                             ( xx.vou_kind ='188' and xx.trans_code in ('100201','105301') )
                                     )
                           and xx.dc_flag = '1'
  group by substr(xx.acct_no,1,5), xx.vou_kind,xx.vou_no,xx.trans_date ;  2    3    4    5    6    7    8    9   10   11   12  

19781 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3813767548

-------------------------------------------------------------------------------------------------------
| Id  | Operation		       | Name		      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |		      | 38474 |  2893K|  1663	(1)| 00:00:20 |
|   1 |  HASH GROUP BY		       |		      | 38474 |  2893K| 	   |	      |
|   2 |   CONCATENATION 	       |		      |       |       | 	   |	      |
|   3 |    INLIST ITERATOR	       |		      |       |       | 	   |	      |
|*  4 |     TABLE ACCESS BY INDEX ROWID| F_EVT_SAVD_LIST      |     1 |    77 |     7	(0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN	       | F_EVT_SAVD_LIST_IDX1 |     7 |       |     5	(0)| 00:00:01 |
|   6 |    INLIST ITERATOR	       |		      |       |       | 	   |	      |
|*  7 |     TABLE ACCESS BY INDEX ROWID| F_EVT_SAVD_LIST      | 38473 |  2892K|   961	(1)| 00:00:12 |
|*  8 |      INDEX RANGE SCAN	       | F_EVT_SAVD_LIST_IDX1 |  1781 |       |   673	(1)| 00:00:09 |
-------------------------------------------------------------------------------------------------------

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

   4 - filter("F_EVT_SAVD_LIST"."DC_FLAG"='1')
   5 - access("F_EVT_SAVD_LIST"."VOU_KIND"='188' AND ("F_EVT_SAVD_LIST"."TRANS_CODE"='100201'
	      OR "F_EVT_SAVD_LIST"."TRANS_CODE"='105301') AND "TRANS_DATE">=TO_DATE(' 2013-10-10 00:00:00',
	      'syyyy-mm-dd hh24:mi:ss') AND "TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd
	      hh24:mi:ss'))
   7 - filter("F_EVT_SAVD_LIST"."DC_FLAG"='1')
   8 - access(("F_EVT_SAVD_LIST"."VOU_KIND"='3' OR "F_EVT_SAVD_LIST"."VOU_KIND"='4') AND
	      "TRANS_DATE">=TO_DATE(' 2013-10-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
	      "TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("TRANS_DATE">=TO_DATE(' 2013-10-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
	      "TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
	      (LNNVL("F_EVT_SAVD_LIST"."VOU_KIND"='188') OR LNNVL("F_EVT_SAVD_LIST"."TRANS_CODE"='100201')
	      AND LNNVL("F_EVT_SAVD_LIST"."TRANS_CODE"='105301')))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
      20833  consistent gets
	  0  physical reads
	  0  redo size
     526389  bytes sent via SQL*Net to client
      15021  bytes received via SQL*Net from client
       1320  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      19781  rows processed



CONCATENATION作用：进行扩展就是进行了union改写


使用NO_EXPAND:
阻止扩展

SQL>  select /*+ NO_EXPAND*/  substr(xx.acct_no,1,5) agent_org, xx.vou_kind,sum( xx.trans_amt) trans_amt
                              from (
                                     select * from dwf.F_EVT_SAVD_LIST 
                                     where trans_date >= to_date('2013-10-10', 'YYYY-MM-DD')
                                     AND trans_date <= to_date('2014-03-31', 'YYYY-MM-DD')
                                   ) xx
                              where  ( xx.vou_kind in ('3', '4')
                                       or
                             ( xx.vou_kind ='188' and xx.trans_code in ('100201','105301') )
                                     )
                           and xx.dc_flag = '1'
  group by substr(xx.acct_no,1,5), xx.vou_kind,xx.vou_no,xx.trans_date ;  2    3    4    5    6    7    8    9   10   11   12  

19781 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1587974759

------------------------------------------------------------------------------------------------------------------
| Id  | Operation			  | Name		 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		  |			 | 38473 |  2892K|	 |   261K  (1)| 00:52:21 |
|   1 |  HASH GROUP BY			  |			 | 38473 |  2892K|	 |   261K  (1)| 00:52:21 |
|*  2 |   TABLE ACCESS BY INDEX ROWID	  | F_EVT_SAVD_LIST	 | 38473 |  2892K|	 |   261K  (1)| 00:52:21 |
|   3 |    BITMAP CONVERSION TO ROWIDS	  |			 |	 |	 |	 |	      | 	 |
|   4 |     BITMAP OR			  |			 |	 |	 |	 |	      | 	 |
|   5 |      BITMAP CONVERSION FROM ROWIDS|			 |	 |	 |	 |	      | 	 |
|   6 |       SORT ORDER BY		  |			 |	 |	 |    15M|	      | 	 |
|*  7 |        INDEX RANGE SCAN 	  | F_EVT_SAVD_LIST_IDX1 |	 |	 |	 |   676   (1)| 00:00:09 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|			 |	 |	 |	 |	      | 	 |
|   9 |       SORT ORDER BY		  |			 |	 |	 |    15M|	      | 	 |
|* 10 |        INDEX RANGE SCAN 	  | F_EVT_SAVD_LIST_IDX1 |	 |	 |	 |   676   (1)| 00:00:09 |
|  11 |      BITMAP CONVERSION FROM ROWIDS|			 |	 |	 |	 |	      | 	 |
|  12 |       SORT ORDER BY		  |			 |	 |	 |    15M|	      | 	 |
|* 13 |        INDEX RANGE SCAN 	  | F_EVT_SAVD_LIST_IDX1 |	 |	 |	 |   676   (1)| 00:00:09 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("F_EVT_SAVD_LIST"."DC_FLAG"='1' AND "TRANS_DATE">=TO_DATE(' 2013-10-10 00:00:00',
	      'syyyy-mm-dd hh24:mi:ss') AND "TRANS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("F_EVT_SAVD_LIST"."VOU_KIND"='3')
       filter("F_EVT_SAVD_LIST"."VOU_KIND"='3')
  10 - access("F_EVT_SAVD_LIST"."VOU_KIND"='4')
       filter("F_EVT_SAVD_LIST"."VOU_KIND"='4')
  13 - access("F_EVT_SAVD_LIST"."VOU_KIND"='188')
       filter(("F_EVT_SAVD_LIST"."TRANS_CODE"='100201' OR "F_EVT_SAVD_LIST"."TRANS_CODE"='105301') AND
	      "F_EVT_SAVD_LIST"."VOU_KIND"='188')

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
	  4  recursive calls
	  0  db block gets
     210065  consistent gets
	  0  physical reads
	  0  redo size
     548250  bytes sent via SQL*Net to client
      15021  bytes received via SQL*Net from client
       1320  SQL*Net roundtrips to/from client
	  3  sorts (memory)
	  0  sorts (disk)
      19781  rows processed

现在走了 BITMAP CONVERSION FROM ROWIDS逻辑读变为210065 ,反而增大 ,静止BITMAP CONVERSION FROM ROWIDS呢？

B-tree to Bitmap Conversions
One of the optimizer’s strategies is to range scan B-tree indexes to acquire lists of rowids, convert
the lists of rowids into the equivalent bitmaps, and perform bitwise operations to identify a
small set of rows. Effectively, the optimizer can take sets of rowids from index range scans and
convert them to bitmap indexes on the fly before doing an index_combine on the resulting

优化器的其中一个策略是 Index range scan 得到需要的rowid，转为rowid列表到等效的视图，执行按位运算来确定笑的行集。

实际上,优化器可以取出行集转换为 bitmap indexes

bitmap indexes.
In 8i, only tables with existing bitmap indexes could be subject to this treatment, unless
the parameter _b_tree_bitmap_plans had been set to relax the requirement for a preexisting
bitmap index.

In 9i, the default value for this parameter changed from false to true—so you may see
execution plans involving bitmap conversions after you’ve upgraded, even though you don’t
have a single bitmap index in your database. Unfortunately, because of the implicit packing
assumption that the optimizer uses for bitmap indexes, this will sometimes be a very bad idea.
As a related issue, this change can make it worth using the minimize_records_per_block
option on all your important tables.




SQL> alter session set "_b_tree_bitmap_plans"=false;

Session altered.

SQL> select  /*+ NO_EXPAND*/ substr(xx.acct_no,1,5) agent_org, xx.vou_kind,sum( xx.trans_amt) trans_amt
                              from (
                                     select * from dwf.F_EVT_SAVD_LIST 
                                     where trans_date >= to_date('2013-10-10', 'YYYY-MM-DD')
                                     AND trans_date <= to_date('2014-03-31', 'YYYY-MM-DD')
                                   ) xx
                              where  ( xx.vou_kind in ('3', '4')
                                       or
                             ( xx.vou_kind ='188' and xx.trans_code in ('100201','105301') )
                                     )
                           and xx.dc_flag = '1'
  group by substr(xx.acct_no,1,5), xx.vou_kind,xx.vou_no,xx.trans_date ;  2    3    4    5    6    7    8    9   10   11   12  

19781 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2812292261

--------------------------------------------------------------------------------------
| Id  | Operation	   | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		     | 38473 |	2892K|	 268K  (2)| 00:53:47 |
|   1 |  HASH GROUP BY	   |		     | 38473 |	2892K|	 268K  (2)| 00:53:47 |
|*  2 |   TABLE ACCESS FULL| F_EVT_SAVD_LIST | 38473 |	2892K|	 268K  (2)| 00:53:47 |
--------------------------------------------------------------------------------------

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

   2 - filter("F_EVT_SAVD_LIST"."DC_FLAG"='1' AND "TRANS_DATE">=TO_DATE('
	      2013-10-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRANS_DATE"<=TO_DATE('
	      2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
	      (("F_EVT_SAVD_LIST"."VOU_KIND"='3' OR "F_EVT_SAVD_LIST"."VOU_KIND"='4') OR
	      "F_EVT_SAVD_LIST"."VOU_KIND"='188' AND
	      ("F_EVT_SAVD_LIST"."TRANS_CODE"='100201' OR
	      "F_EVT_SAVD_LIST"."TRANS_CODE"='105301')))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
	  5  recursive calls
	  0  db block gets
    1210345  consistent gets
    1210244  physical reads
	  0  redo size
     548290  bytes sent via SQL*Net to client
      15021  bytes received via SQL*Net from client
       1320  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      19781  rows processed

此时走了全表扫描
转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797867.html