[20221111]CBO and Partial indexing.txt

[20221111]CBO and Partial indexing.txt

--//重複測試:

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的軌跡，我們可
以發現它在這樣簡單的情況下繞過了表展開轉換：