OR扩展
来源：互联网 发布：卡通农场 淘宝买金币 编辑：程序博客网 时间：2024/05/16 19:18
<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 processedCONCATENATION作用：进行扩展就是进行了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 ConversionsOne of the optimizer’s strategies is to range scan B-tree indexes to acquire lists of rowids, convertthe lists of rowids into the equivalent bitmaps, and perform bitwise operations to identify asmall set of rows. Effectively, the optimizer can take sets of rowids from index range scans andconvert them to bitmap indexes on the fly before doing an index_combine on the resulting优化器的其中一个策略是 Index range scan 得到需要的rowid，转为rowid列表到等效的视图，执行按位运算来确定笑的行集。实际上,优化器可以取出行集转换为 bitmap indexesbitmap indexes.In 8i, only tables with existing bitmap indexes could be subject to this treatment, unlessthe parameter _b_tree_bitmap_plans had been set to relax the requirement for a preexistingbitmap index.In 9i, the default value for this parameter changed from false to true—so you may seeexecution plans involving bitmap conversions after you’ve upgraded, even though you don’thave a single bitmap index in your database. Unfortunately, because of the implicit packingassumption 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_blockoption 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此时走了全表扫描 0 0
