[20181219]不能使用USE_CONCAT优化例子.txt

来源：这里教程网时间：2026-03-03 12:46:14 作者：

[20181219]不能使用USE_CONCAT优化例子.txt --//链接http://www.itpub.net/thread-2107240-2-1.html,http://www.itpub.net/thread-2107231-2-1.html的讨论. SQL_ID 6qsvy74cbrm6x, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics USE_CONCAT */ * FROM ljapay WHERE incomeno IN (SELECT contno FROM lccont WHERE prtno = '1300000000617430') OR otherno = '1300000000617430' Plan hash value: 2966270370 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 549 (100)| | 0 |00:00:00.33 | 233K| |* 1 | FILTER | | 1 | | | | | 0 |00:00:00.33 | 233K| | 2 | TABLE ACCESS FULL | LJAPAY | 1 | 67404 | 13M| 549 (1)| 00:00:07 | 67404 |00:00:00.04 | 2015 | |* 3 | TABLE ACCESS BY INDEX ROWID| LCCONT | 67376 | 1 | 37 | 3 (0)| 00:00:01 | 0 |00:00:00.25 | 231K| |* 4 | INDEX UNIQUE SCAN | PK_LCCONT | 67376 | 1 | | 2 (0)| 00:00:01 | 63370 |00:00:00.12 | 168K| ------------------------------------------------------------------------------------------------------------------------------------ --//加入USE_CONCAT并没有用,走的是filter,更要命的是id=3,4的starts=67376,大部分逻辑读都在这里. --//作者执行的是修改,我换成了select语句. --//我自己写一个测试例子验证看看. 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table t1 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000; create table t2 as select rownum id1 ,rownum id2 ,lpad('x',100,'x') name from dual connect by level<=6000; create index i_t1_id1 on t1(id1); create index i_t1_id2 on t1(id2); create index i_t2_id1 on t2(id1); --//分析略. 2.测试: SCOTT@book> alter session set statistics_level = all; Session altered. SCOTT@book> select * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 ); ID1 ID2 NAME ---------- ---------- ---------------------------------------------------------------------------------------------------- 10 10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 11 11 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID gz5pqkg6svm7k, child number 0 ------------------------------------- select * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 ) Plan hash value: 1962644737 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 30 (100)| | 2 |00:00:00.01 | 102 | |* 1 | FILTER | | 1 | | | | | 2 |00:00:00.01 | 102 | | 2 | TABLE ACCESS FULL| T1 | 1 | 6000 | 638K| 30 (0)| 00:00:01 | 6000 |00:00:00.01 | 100 | |* 3 | FILTER | | 5999 | | | | | 1 |00:00:00.01 | 2 | |* 4 | INDEX RANGE SCAN| I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | ------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T1@SEL$1 3 - SEL$2 4 - SEL$2 / T2@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("T1"."ID2"=10 OR IS NOT NULL)) 3 - filter(11=:B1) 4 - access("T2"."ID1"=:B1) 32 rows selected. --//我的版本与作者看到的不同,仔细看id=4的starts就明白了,实际上这里应该循环5999次. --//我的版本11.2.0.4(没有打任何补丁),而他使用版本是11.2.0.4.180717. --//也就是我使用的版本最后的buffers计算错误. --//加入提示: select /*+ USE_CONCAT */ * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 ); --//执行效果一样.使用如下提示: select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 ); --//执行计划如下: SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 9m6dksysdc87a, child number 0 ------------------------------------- select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in (select t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 ) Plan hash value: 1154250921 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 33 (100)| | 2 |00:00:00.01 | 105 | | 1 | CONCATENATION | | 1 | | | | | 2 |00:00:00.01 | 105 | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 3 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 4 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 101 | |* 5 | TABLE ACCESS FULL | T1 | 1 | 5999 | 638K| 30 (0)| 00:00:01 | 5999 |00:00:00.01 | 99 | |* 6 | FILTER | | 5999 | | | | | 1 |00:00:00.01 | 2 | |* 7 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1_1 / T1@SEL$1 3 - SEL$1_1 / T1@SEL$1 5 - SEL$1_2 / T1@SEL$1_2 6 - SEL$2 7 - SEL$2 / T2@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."ID2"=10) 4 - filter( IS NOT NULL) 5 - filter(LNNVL("T1"."ID2"=10)) 6 - filter(11=:B1) 7 - access("T2"."ID1"=:B1) --//提示起作用.但是id=4,走的是filter,一样效率低下.改写如下: select /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 ); --//也是一样unnest没用.看了开发版的链接:http://www.itpub.net/thread-2107231-1-1.html,改用OR_EXPANSION问题依旧. select /*+ OR_EXPAND */ * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) or (t1.id2=10 ); --//这样使用union all: select * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) and lnnvl(t1.id2=10) union all select * from t1 where (t1.id2=10 ); --//或者使用union: select * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) union select * from t1 where (t1.id2=10 ); --//感觉使用union all 效果更好.贴出使用union all的执行计划: SQL_ID 3619bvczapunb, child number 0 ------------------------------------- select * from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) and lnnvl(t1.id2=10) union all select * from t1 where (t1.id2=10 ) Plan hash value: 3919113390 ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 2 |00:00:00.01 | 9 | | 1 | UNION-ALL | | 1 | | | | | 2 |00:00:00.01 | 9 | | 2 | NESTED LOOPS SEMI | | 1 | 1 | 113 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | |* 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 4 | INDEX RANGE SCAN | I_T1_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 5 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 109 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 7 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | ------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$1 2 - SEL$5DA710D3 3 - SEL$5DA710D3 / T1@SEL$1 4 - SEL$5DA710D3 / T1@SEL$1 5 - SEL$5DA710D3 / T2@SEL$2 6 - SEL$3 / T1@SEL$3 7 - SEL$3 / T1@SEL$3 Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(LNNVL("T1"."ID2"=10)) 4 - access("T1"."ID1"=11) 5 - access("T2"."ID1"=11) filter("T1"."ID1"="T2"."ID1") 7 - access("T1"."ID2"=10) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - STRDEF[22], STRDEF[22], STRDEF[100] 2 - "T1"."ID1"[NUMBER,22], "T1"."ID2"[NUMBER,22], "T1"."NAME"[VARCHAR2,100] 3 - "T1"."ID1"[NUMBER,22], "T1"."ID2"[NUMBER,22], "T1"."NAME"[VARCHAR2,100] 4 - "T1".ROWID[ROWID,10], "T1"."ID1"[NUMBER,22] 6 - "T1"."ID1"[NUMBER,22], "T1"."ID2"[NUMBER,22], "T1"."NAME"[VARCHAR2,100] 7 - "T1".ROWID[ROWID,10], "T1"."ID2"[NUMBER,22] 3.继续: --//不过对方执行的是修改操作,使用union all或者union,不能基于结果集合的修改. --//会报错误 ORA-01732: data manipulation operation not legal on this view. --//关于结果集修改看链接:http://blog.itpub.net/267265/viewspace-2139049/ --// 修改条件使用主键或者rowid就ok了.贴出使用rowid的改写: update t1 set name='zzz' where rowid in ( select rowid from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) and lnnvl(t1.id2=10) union all select rowid from t1 where (t1.id2=10 ) ); SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID g6608508whaar, child number 0 ------------------------------------- update t1 set name='zzz' where rowid in ( select rowid from t1 where t1.id1 in (select /*+ unnest */ t2.id1 from t2 where t2.id1=11 ) and lnnvl(t1.id2=10) union all select rowid from t1 where (t1.id2=10 ) ) Plan hash value: 3348292872 ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 10 | | | | | 1 | UPDATE | T1 | 1 | | | | | 0 |00:00:00.01 | 10 | | | | | 2 | NESTED LOOPS | | 1 | 120 | 14520 | 6 (0)| 00:00:01 | 2 |00:00:00.01 | 8 | | | | | 3 | VIEW | VW_NSO_1 | 1 | 2 | 24 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | | | | | 4 | SORT UNIQUE | | 1 | 2 | 40 | 4 (0)| 00:00:01 | 2 |00:00:00.01 | 7 | 73728 | 73728 | | | 5 | UNION-ALL | | 1 | | | | | 2 |00:00:00.01 | 7 | | | | | 6 | NESTED LOOPS SEMI | | 1 | 1 | 24 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 5 | | | | |* 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 20 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | | |* 8 | INDEX RANGE SCAN | I_T1_ID1 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | | |* 9 | INDEX RANGE SCAN | I_T2_ID1 | 1 | 1 | 4 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | | |* 10 | INDEX RANGE SCAN | I_T1_ID2 | 1 | 1 | 16 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | | | 11 | TABLE ACCESS BY USER ROWID | T1 | 2 | 60 | 6540 | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 1 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$9C09E64D 3 - SET$FCA7A018 / VW_NSO_1@SEL$9C09E64D 4 - SET$FCA7A018 6 - SEL$5DA710D3 7 - SEL$5DA710D3 / T1@SEL$1 8 - SEL$5DA710D3 / T1@SEL$1 9 - SEL$5DA710D3 / T2@SEL$2 10 - SEL$3 / T1@SEL$3 11 - SEL$9C09E64D / T1@UPD$1 Predicate Information (identified by operation id): --------------------------------------------------- 7 - filter(LNNVL("T1"."ID2"=10)) 8 - access("T1"."ID1"=11) 9 - access("T2"."ID1"=11) filter("T1"."ID1"="T2"."ID1") 10 - access("T1"."ID2"=10) --//id=4 SORT UNIQUE,依旧需要去除重复的记录.另外链接也提供了使用merge的例子,我个人很少使用它.贴出对方改写的merge的例子: MERGE INTO ljapay t1 USING (SELECT a.payno, a.currency FROM ljapay a JOIN lccont b ON a.incomeno = b.contno WHERE b.prtno = 'Q99180000000110' UNION SELECT a.payno, a.currency FROM ljapay a WHERE otherno = 'Q99180000000110') t2 ON (t1.payno = t2.payno AND t1.currency = t2.currency) WHEN MATCHED THEN UPDATE SET t1.Operstate = '0' ,t1.modifydate = DATE '2018-12-17' ,t1.modifytime = '16:20:44'

编辑推荐：

返回列表

[20181219]不能使用USE_CONCAT优化例子.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推