[20190524]使用use_concat or_expand提示优化.txt

来源：这里教程网时间：2026-03-03 13:47:06 作者：

[20190524]使用use_concat or_expand提示优化.txt --//上午看了链接https://connor-mcdonald.com/2019/05/22/being-generous-to-the-optimizer，突然想起我们生产系统类似语句。 --//现在想想觉得开发的想象力太丰富，写这些语句是否考虑长期运行导致的结果。对方例子相对简单，我优化的例子简直就是变态。 --//链接:[20150814]使用use_concat提示.txt => http://blog.itpub.net/267265/viewspace-1771727/ --//实际上看了马上想到使用use_concat or_expand提示优化sql语句.同时看了链接 --//https://jonathanlewis.wordpress.com/2019/05/22/danger-hints/，一起测试看看。 1.环境： SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 create table address ( street int, suburb int, post_code int, data char(100)); insert into address select mod(rownum,1e4), mod(rownum,10), mod(rownum,1e2), rownum from dual connect by level <= 1e5; commit; exec dbms_stats.gather_table_stats('','ADDRESS') create index i_address_stress on address ( street ); create index i_address_suburb on address ( suburb ); create index i_address_post_code on address ( post_code ); 2.测试: variable val number = 6 variable choice number = 1 alter session set statistics_level = all; SCOTT@test01p> select data from address where ( :choice = 1 and street = :val ) or ( :choice = 2 and suburb = :val ); DATA ----- 6 10006 20006 30006 40006 50006 60006 70006 80006 90006 10 rows selected. Plan hash value: 3645838471 ----------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 445 (100)| | 10 |00:00:00.02 | 1636 | |* 1 | TABLE ACCESS FULL| ADDRESS | 1 | 100 | 10800 | 445 (1)| 00:00:01 | 10 |00:00:00.02 | 1636 | ----------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / ADDRESS@SEL$1 Peeked Binds (identified by position): -------------------------------------- 2 - :2 (NUMBER): 6 4 - :2 (NUMBER, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(((:CHOICE=2 AND "SUBURB"=:VAL) OR ("STREET"=:VAL AND :CHOICE=1))) --//选择全表扫描.加入提示:/*+ or_expand(@sel$1) */ select /*+ or_expand(@sel$1) */ data from address where ( :choice = 1 and street = :val ) or ( :choice = 2 and suburb = :val ); SCOTT@test01p> @ dpc '' outline Plan hash value: 1427591975 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 456 (100)| | 10 |00:00:00.06 | 13 | 4 | | 1 | VIEW | VW_ORE_B7380F92 | 1 | 10010 | 997K| 456 (1)| 00:00:01 | 10 |00:00:00.06 | 13 | 4 | | 2 | UNION-ALL | | 1 | | | | | 10 |00:00:00.06 | 13 | 4 | |* 3 | FILTER | | 1 | | | | | 10 |00:00:00.06 | 13 | 4 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS | 1 | 10 | 1050 | 11 (0)| 00:00:01 | 10 |00:00:00.06 | 13 | 4 | |* 5 | INDEX RANGE SCAN | I_ADDRESS_STRESS | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.06 | 3 | 4 | |* 6 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 | |* 7 | TABLE ACCESS FULL | ADDRESS | 0 | 10000 | 1054K| 445 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$9162BF3C / VW_ORE_B7380F92@SEL$B7380F92 2 - SET$9162BF3C 3 - SET$9162BF3C_1 4 - SET$9162BF3C_1 / ADDRESS@SEL$1 5 - SET$9162BF3C_1 / ADDRESS@SEL$1 6 - SET$9162BF3C_2 7 - SET$9162BF3C_2 / ADDRESS@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SET$9162BF3C_2") OUTLINE_LEAF(@"SET$9162BF3C_1") OUTLINE_LEAF(@"SET$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) OUTLINE_LEAF(@"SEL$B7380F92") OUTLINE(@"SET$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$B7380F92" "VW_ORE_B7380F92"@"SEL$B7380F92") INDEX_RS_ASC(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1") FULL(@"SET$9162BF3C_2" "ADDRESS"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 2 - :2 (NUMBER): 6 4 - :2 (NUMBER, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:CHOICE=1) 5 - access("STREET"=:VAL) 6 - filter(:CHOICE=2) 7 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL)))) --//注意看下划线. --//但是使用下划线提示,改动代码的情况下如何呢? SELECT /*+ or_expand(@sel$1 (1) (2) ) */ data FROM address WHERE ( :choice = 1 AND street = :val ) OR ( :choice = 2 AND suburb = :val ) OR ( :choice = 3 AND post_code = :val); SCOTT@test01p> @ dpc '' outline ... Plan hash value: 1427591975 ----------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 456 (100)| | 10 |00:00:00.01 | 13 | | 1 | VIEW | VW_ORE_B7380F92 | 1 | 10010 | 997K| 456 (1)| 00:00:01 | 10 |00:00:00.01 | 13 | | 2 | UNION-ALL | | 1 | | | | | 10 |00:00:00.01 | 13 | |* 3 | FILTER | | 1 | | | | | 10 |00:00:00.01 | 13 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS | 1 | 10 | 1050 | 11 (0)| 00:00:01 | 10 |00:00:00.01 | 13 | |* 5 | INDEX RANGE SCAN | I_ADDRESS_STRESS | 1 | 10 | | 1 (0)| 00:00:01 | 10 |00:00:00.01 | 3 | |* 6 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 7 | TABLE ACCESS FULL | ADDRESS | 0 | 10000 | 1054K| 445 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$9162BF3C / VW_ORE_B7380F92@SEL$B7380F92 2 - SET$9162BF3C 3 - SET$9162BF3C_1 4 - SET$9162BF3C_1 / ADDRESS@SEL$1 5 - SET$9162BF3C_1 / ADDRESS@SEL$1 6 - SET$9162BF3C_2 7 - SET$9162BF3C_2 / ADDRESS@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SET$9162BF3C_2") OUTLINE_LEAF(@"SET$9162BF3C_1") OUTLINE_LEAF(@"SET$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) OUTLINE_LEAF(@"SEL$B7380F92") OUTLINE(@"SET$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$B7380F92" "VW_ORE_B7380F92"@"SEL$B7380F92") INDEX_RS_ASC(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "ADDRESS"@"SEL$1") FULL(@"SET$9162BF3C_2" "ADDRESS"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 2 - :2 (NUMBER): 6 4 - :2 (NUMBER, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:CHOICE=1) 5 - access("STREET"=:VAL) 6 - filter(:CHOICE=2) 7 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL)))) --//正像链接讲的那样如果增加1个或条件,导致执行计划变得不合理.实际上跟严重的是查询发生了错误. --//如果仔细看Predicate Information 就很容易发现没有:CHOICE=1的filter.如果查询: SCOTT@test01p> variable choice number = 3 SELECT /*+ or_expand(@sel$1 (1) (2) ) */ data FROM address WHERE ( :choice = 1 AND street = :val ) OR ( :choice = 2 AND suburb = :val ) OR ( :choice = 3 AND post_code = :val); no rows selected. --//取消提示: SELECT data FROM address WHERE ( :choice = 1 AND street = :val ) OR ( :choice = 2 AND suburb = :val ) OR ( :choice = 3 AND post_code = :val); ... 1000 rows selected. --//两者的结果集不一样.明显这个是一个bug. 修改如下: SELECT /*+ or_expand(@sel$1 (1) (2) (3) ) */ data FROM address WHERE ( :choice = 1 AND street = :val ) OR ( :choice = 2 AND suburb = :val ) OR ( :choice = 3 AND post_code = :val); ... --//注使用提示/*+ or_expand(@sel$1 ) */结果是正确的. SCOTT@test01p> @ dpc '' outline Plan hash value: 3525475520 ----------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 900 (100)| | 1000 |00:00:00.02 | 1640 | | 1 | VIEW | VW_ORE_B7380F92 | 1 | 11009 | 1096K| 900 (1)| 00:00:01 | 1000 |00:00:00.02 | 1640 | | 2 | UNION-ALL | | 1 | | | | | 1000 |00:00:00.02 | 1640 | |* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS | 0 | 10 | 1050 | 11 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 5 | INDEX RANGE SCAN | I_ADDRESS_STRESS | 0 | 10 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 6 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 7 | TABLE ACCESS FULL | ADDRESS | 0 | 10000 | 1054K| 445 (1)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 8 | FILTER | | 1 | | | | | 1000 |00:00:00.02 | 1640 | |* 9 | TABLE ACCESS FULL | ADDRESS | 1 | 999 | 108K| 445 (1)| 00:00:01 | 1000 |00:00:00.02 | 1640 | ----------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SET$49E1C21B / VW_ORE_B7380F92@SEL$B7380F92 2 - SET$49E1C21B 3 - SET$49E1C21B_1 4 - SET$49E1C21B_1 / ADDRESS@SEL$1 5 - SET$49E1C21B_1 / ADDRESS@SEL$1 6 - SET$49E1C21B_2 7 - SET$49E1C21B_2 / ADDRESS@SEL$1 8 - SET$49E1C21B_3 9 - SET$49E1C21B_3 / ADDRESS@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SET$49E1C21B_3") OUTLINE_LEAF(@"SET$49E1C21B_2") OUTLINE_LEAF(@"SET$49E1C21B_1") OUTLINE_LEAF(@"SET$49E1C21B") OR_EXPAND(@"SEL$1" (1) (2) (3)) OUTLINE_LEAF(@"SEL$B7380F92") OUTLINE(@"SET$49E1C21B") OR_EXPAND(@"SEL$1" (1) (2) (3)) OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$B7380F92" "VW_ORE_B7380F92"@"SEL$B7380F92") INDEX_RS_ASC(@"SET$49E1C21B_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$49E1C21B_1" "ADDRESS"@"SEL$1") FULL(@"SET$49E1C21B_2" "ADDRESS"@"SEL$1") FULL(@"SET$49E1C21B_3" "ADDRESS"@"SEL$1") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 2 - :2 (NUMBER): 6 4 - :2 (NUMBER, Primary=2) 6 - :2 (NUMBER, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:CHOICE=1) 5 - access("STREET"=:VAL) 6 - filter(:CHOICE=2) 7 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL)))) 8 - filter(:CHOICE=3) 9 - filter(("POST_CODE"=:VAL AND (LNNVL(:CHOICE=1) OR LNNVL("STREET"=:VAL)) AND (LNNVL(:CHOICE=2) OR LNNVL("SUBURB"=:VAL)))) 3.测试使用use_concate看看: set linesize 100 SELECT /*+ use_concat */ data FROM address WHERE ( :choice = 1 AND street = :val ) OR ( :choice = 2 AND suburb = :val ) OR ( :choice = 3 AND post_code = :val); SCOTT@test01p> @ dpc '' outline ... Plan hash value: 2048882018 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 900 (100)| | 1000 |00:00:00.01 | 1640 | | 1 | CONCATENATION | | 1 | | | | | 1000 |00:00:00.01 | 1640 | |* 2 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| ADDRESS | 0 | 10 | 1110 | 11 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 4 | INDEX RANGE SCAN | I_ADDRESS_STRESS | 0 | 10 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 5 | FILTER | | 1 | | | | | 1000 |00:00:00.01 | 1640 | |* 6 | TABLE ACCESS FULL | ADDRESS | 1 | 1000 | 108K| 445 (1)| 00:00:01 | 1000 |00:00:00.01 | 1640 | |* 7 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 8 | TABLE ACCESS FULL | ADDRESS | 0 | 9999 | 1083K| 445 (1)| 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 / ADDRESS@SEL$1 4 - SEL$1_1 / ADDRESS@SEL$1 6 - SEL$1_2 / ADDRESS@SEL$1_2 8 - SEL$1_3 / ADDRESS@SEL$1_3 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"SEL$1_1") USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((5 2) (6 3) (7 4) (8 5) (9 6) (10 7) (2 8) (4 9) (3 10))) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//始终不明白里面那一串表示什么? OUTLINE_LEAF(@"SEL$1_2") OUTLINE_LEAF(@"SEL$1_3") OUTLINE(@"SEL$1") INDEX_RS_ASC(@"SEL$1_1" "ADDRESS"@"SEL$1" ("ADDRESS"."STREET")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_1" "ADDRESS"@"SEL$1") FULL(@"SEL$1_2" "ADDRESS"@"SEL$1_2") FULL(@"SEL$1_3" "ADDRESS"@"SEL$1_3") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 2 - :2 (NUMBER): 6 4 - :2 (NUMBER, Primary=2) 6 - :2 (NUMBER, Primary=2) Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:CHOICE=1) 4 - access("STREET"=:VAL) 5 - filter(:CHOICE=3) 6 - filter(("POST_CODE"=:VAL AND (LNNVL("STREET"=:VAL) OR LNNVL(:CHOICE=1)))) 7 - filter(:CHOICE=2) 8 - filter(("SUBURB"=:VAL AND (LNNVL(:CHOICE=3) OR LNNVL("POST_CODE"=:VAL)) AND (LNNVL("STREET"=:VAL) OR LNNVL(:CHOICE=1))))

编辑推荐：

返回列表

[20190524]使用use_concat or_expand提示优化.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推