[20250109]11g下测试使用or_expand提示.txt

[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语句的技巧。