[20250109]11g下测试使用or_expand提示.txt
--//上午测试19c使用or_expand提示，很好地展开or，以前11g使用usr_concat提示，仅仅能优化1路，下午在11g下测试看看。
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
SCOTT@book> @ sqlhint or_expand
SCOTT@book> @ pr
==============================
NAME : OR_EXPAND
SQL_FEATURE : QKSFM_OR_EXPAND
CLASS : OR_EXPAND
INVERSE :
TARGET_LEVEL : 4
PROPERTY : 272
VERSION : 8.1.7
VERSION_OUTLINE :
PL/SQL procedure successfully completed.
SCOTT@book> @ pr use_concat
==============================
NAME : USE_CONCAT
SQL_FEATURE : QKSFM_USE_CONCAT
CLASS : USE_CONCAT
INVERSE : NO_EXPAND
TARGET_LEVEL : 2
PROPERTY : 16
VERSION : 8.1.0
VERSION_OUTLINE : 8.1.7
PL/SQL procedure successfully completed.
2.建立测试环境：
SCOTT@book> create table t as select * from all_objects;
Table created.
SCOTT@book> create index i_t_object_id on t(object_id);
Index created.
SCOTT@book> create index i_t_object_name on t(object_name);
Index created.
--//分析略。
$ cat f3.txt
set term off
variable v_id number ;
variable v_name varchar2(32) ;
exec :v_id := 76191;
exec :v_name := NULL;
set term on
select /*+ &&1 */ object_name,object_type from t where
( ( :v_id = '' or :v_id is null) or t.object_id = :v_id)
and ( ( :v_name = '' or :v_name is null) or t.object_name = :v_name)
;
3.测试使用or_expand提示：
SCOTT@book> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book> @ f3.txt or_expand
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
BUFFER SYNONYM
SCOTT@book> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 32dn17b91frjg, child number 0
-------------------------------------
select /*+ or_expand */ object_name,object_type from t where ( ( :v_id
= '' or :v_id is null) or t.object_id = :v_id) and ( ( :v_name = '' or
:v_name is null) or t.object_name = :v_name)
Plan hash value: 1601196873
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 338 (100)| | 1 |00:00:00.01 | 1213 | 1210 |
|* 1 | TABLE ACCESS FULL| T | 1 | 212 | 8268 | 338 (1)| 00:00:05 | 1 |00:00:00.01 | 1213 | 1210 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
3 - (NUMBER, Primary=1)
6 - (VARCHAR2(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(((:V_NAME IS NULL OR "T"."OBJECT_NAME"=:V_NAME) AND (:V_ID IS NULL OR "T"."OBJECT_ID"=:V_ID)))
45 rows selected.
--//带入参数 or_expand(@"SEL$1" (1) (2))，or_expand(@"SEL$1" (1) (2) (3) (4))也一样，说明在11g不行使用or_expand。
4.测试使用use_concat提示：
SCOTT@book> @ f3.txt 'use_concat(@"SEL$1" 8 OR_PREDICATES(1)) '
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
BUFFER SYNONYM
SCOTT@book> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3srub78kdans2, child number 0
-------------------------------------
select /*+ use_concat(@"SEL$1" 8 OR_PREDICATES(1)) */
object_name,object_type from t where ( ( :v_id = '' or :v_id is null)
or t.object_id = :v_id) and ( ( :v_name = '' or :v_name is null) or
t.object_name = :v_name)
Plan hash value: 3649061108
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 340 (100)| | 1 |00:00:00.01 | 4 |
| 1 | CONCATENATION | | 1 | | | | | 1 |00:00:00.01 | 4 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 39 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | I_T_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 4 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL | T | 0 | 4238 | 161K| 338 (1)| 00:00:05 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------
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
5 - SEL$1_2 / T@SEL$1_2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_1")
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
OUTLINE_LEAF(@"SEL$1_2")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."OBJECT_ID"))
FULL(@"SEL$1_2" "T"@"SEL$1_2")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
3 - (NUMBER, Primary=1)
6 - (VARCHAR2(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((:V_NAME IS NULL OR "T"."OBJECT_NAME"=:V_NAME))
3 - access("T"."OBJECT_ID"=:V_ID)
4 - filter(:V_ID IS NULL)
5 - filter(((:V_NAME IS NULL OR "T"."OBJECT_NAME"=:V_NAME) AND LNNVL("T"."OBJECT_ID"=:V_ID)))
61 rows selected.
--//可以发现仅仅解决1路，另外1路选择全表扫描。
--//如果带入的条件如下：
$ cat f3.txt
set term off
variable v_id number ;
variable v_name varchar2(32) ;
--exec :v_id := 76191;
--exec :v_name := NULL;
exec :v_id := NULL;
exec :v_name := 'DEPT';
set term on
select /*+ &&1 */ object_name,object_type from t where
( ( :v_id = '' or :v_id is null) or t.object_id = :v_id)
and ( ( :v_name = '' or :v_name is null) or t.object_name = :v_name)
;
SCOTT@book> @ f3.txt 'use_concat(@"SEL$1" 8 OR_PREDICATES(1)) '
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEPT TABLE
--//执行计划走另外1路：
Plan hash value: 3649061108
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 340 (100)| | 1 |00:00:00.01 | 1213 | 1210 |
| 1 | CONCATENATION | | 1 | | | | | 1 |00:00:00.01 | 1213 | 1210 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 39 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 3 | INDEX RANGE SCAN | I_T_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 4 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 1213 | 1210 |
|* 5 | TABLE ACCESS FULL | T | 1 | 4238 | 161K| 338 (1)| 00:00:05 | 1 |00:00:00.01 | 1213 | 1210 |
-------------------------------------------------------------------------------------------------------------------------------------------------
--//选择全表扫描。
--//如果提示是use_concat(@"SEL$1" 8 OR_PREDICATES(4)) 才可能使用object_name的索引，里面的数字与or的位置相关。
SCOTT@book> @ f3.txt 'use_concat(@"SEL$1" 8 OR_PREDICATES(4)) '
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEPT TABLE
--//执行计划如下：
Plan hash value: 1103451338
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 342 (100)| | 1 |00:00:00.01 | 5 |
| 1 | CONCATENATION | | 1 | | | | | 1 |00:00:00.01 | 5 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 39 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | I_T_OBJECT_NAME | 1 | 2 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 4 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 |
|* 5 | TABLE ACCESS FULL | T | 0 | 4238 | 161K| 338 (1)| 00:00:05 | 0 |00:00:00.01 | 0 |
------------------------------------------------------------------------------------------------------------------------------------------
5.总结：
--//在11g下使用or_expand提示无效。
--//使用use_concat提示仅仅能解决1路，如果输入条件相反，执行计划选择另外1路全表扫描。
--//总之开发尽量在生产系统中少写这类编写sql语句的技巧。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3062796/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
