[20221111]CBO and Partial indexing.txt

[20221111]CBO and Partial indexing.txt --//http://orasql.org/2022/11/02/cbo-and-partial-indexing/,重复测试: 1.环境: TTT@192.168.2.7:1521/orcl> @ ver1 TTT@192.168.2.7:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试: create table t2 (   pkey int not null,   val  int,   padding varchar2(100) ) partition by range(pkey) (   partition p1_on  values less than (2),   partition p2_on  values less than (3),   partition p3_off values less than (4) indexing off,   partition p4_off values less than (5) indexing off,   partition p5_on  values less than (6),   partition p6_off values less than (7) indexing off ); insert into t2    with pkeys as (select level pkey from dual connect by level<=6)        ,gen as (select level n from dual connect by level<=1000)    select pkey,n,rpad('x',100,'x')    from pkeys,gen; create index ix_t2 on t2(pkey,val) local indexing partial; TTT@192.168.2.7:1521/orcl> select partition_name as pname,indexing from user_tab_partitions p where table_name='T2'; PNAME  INDEXING ------ -------- P1_ON  ON P2_ON  ON P3_OFF OFF P4_OFF OFF P5_ON  ON P6_OFF OFF 6 rows selected. TTT@192.168.2.7:1521/orcl> select count(*) from t2 where pkey in (1,2,6) and val=5;   COUNT(*) ----------          3 TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  awkucugb6kwdy, child number 0 ------------------------------------- select count(*) from t2 where pkey in (1,2,6) and val=5 Plan hash value: 3293077569 --------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | --------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |        |       |   276 (100)|          |       |       | |   1 |  SORT AGGREGATE              |         |      1 |     7 |            |          |       |       | |   2 |   VIEW                       | VW_TE_2 |      3 |       |   276   (0)| 00:00:01 |       |       | |   3 |    UNION-ALL                 |         |        |       |            |          |       |       | |   4 |     INLIST ITERATOR          |         |        |       |            |          |       |       | |   5 |      PARTITION RANGE ITERATOR|         |      2 |    14 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) | |*  6 |       INDEX RANGE SCAN       | IX_T2   |      2 |    14 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) | |   7 |     PARTITION RANGE SINGLE   |         |      1 |     7 |   274   (0)| 00:00:01 |     6 |     6 | |*  8 |      TABLE ACCESS FULL       | T2      |      1 |     7 |   274   (0)| 00:00:01 |     6 |     6 | --------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$AE9E49E8    2 - SET$A73639E0   / VW_TE_2@SEL$AE9E49E8    3 - SET$A73639E0    4 - SET$A73639E0_1    6 - SET$A73639E0_1 / T2@SEL$1    7 - SET$A73639E0_2    8 - SET$A73639E0_2 / T2@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    6 - access((("PKEY"=1 OR "PKEY"=2)) AND "VAL"=5)    8 - filter(("VAL"=5 AND "PKEY"=6)) Note -----    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level 43 rows selected. TTT@192.168.2.7:1521/orcl> select count(*) from t2 where pkey in (1,2) and val=5;   COUNT(*) ----------          2 TTT@192.168.2.7:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  9yr5kf110djzr, child number 0 ------------------------------------- select count(*) from t2 where pkey in (1,2) and val=5 Plan hash value: 37798356 ------------------------------------------------------------------------------------------------- | Id  | Operation               | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |      |        |       |   547 (100)|          |       |       | |   1 |  SORT AGGREGATE         |      |      1 |     7 |            |          |       |       | |   2 |   PARTITION RANGE INLIST|      |      2 |    14 |   547   (0)| 00:00:01 |KEY(I) |KEY(I) | |*  3 |    TABLE ACCESS FULL    | T2   |      2 |    14 |   547   (0)| 00:00:01 |KEY(I) |KEY(I) | ------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    3 - SEL$1 / T2@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    3 - filter(("VAL"=5 AND INTERNAL_FUNCTION("PKEY"))) Note -----    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level This query gets a worse plan with 2 FTS, though it's easier and has to scan 1 partition less than the first query. And if we compare CBO traces, we can find that it bypasses TABLE EXPANSION transformation in such simple cases: 这个查询使用2个FTS得到了一个更糟糕的计划，尽管它更容易，而且必须比第一个查询少扫描1个分区。如果我们比较CBO的轨迹，我们可 以发现它在这样简单的情况下绕过了表展开转换：