Oracle 三表关联统计信息异常

我来答

2024-06-11

Oracle 三表关联统计信息异常

两表关联时，统计信息正常

select count(1)
  2  from lcpol b  
  3   inner join lcgrpcont g
  4      on g.grpcontno = b.grpcontno
  5     and g.grpcontno = '2017081000014618';

Execution Plan
----------------------------------------------------------
Plan hash value: 3988393551

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                     |     1 |    35 |  2770   (1)| 00:00:34 |
|   1 |  SORT AGGREGATE     |                     |     1 |    35 |            |          |
|   2 |   NESTED LOOPS      |                     |   535K|    17M|  2770   (1)| 00:00:34 |
|*  3 |    INDEX UNIQUE SCAN| PK_LCGRPCONT        |     1 |    17 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX_LCPOL_GRPCONTNO |   535K|  9419K|  2769   (1)| 00:00:34 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("G"."GRPCONTNO"='2017081000014618')
   4 - access("B"."GRPCONTNO"='2017081000014618')

增加了一个a表的关联，统计信息就错了

select count(1)
  2  from lcpol b  
  3   inner join lcgrpcont g
  4      on g.grpcontno = b.grpcontno
  5     and g.grpcontno = '2017081000014618'
  6   inner join lcinsured a
  7      on a.insuredno = b.insuredno
  8     and a.contno = b.contno
  9     and b.grpcontno = '2017081000014618';

Execution Plan
----------------------------------------------------------
Plan hash value: 2873904152

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     1 |    91 |    91   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE                |                     |     1 |    91 |            |          |
|   2 |   NESTED LOOPS                 |                     |    41 |  3731 |    91   (0)| 00:00:02 |
|   3 |    NESTED LOOPS                |                     |    41 |  2583 |    50   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN          | PK_LCGRPCONT        |     1 |    17 |     1   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| LCPOL               |    41 |  1886 |    49   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | IDX_LCPOL_GRPCONTNO |    70 |       |    13   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN           | PK_LCINSURED        |     1 |    28 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("G"."GRPCONTNO"='2017081000014618')
   6 - access("G"."GRPCONTNO"="B"."GRPCONTNO")
       filter("B"."GRPCONTNO"='2017081000014618')
   7 - access("A"."CONTNO"="B"."CONTNO" AND "A"."INSUREDNO"="B"."INSUREDNO")

是怎么回事？

我来答

添加附件

问题补充

3条回答

默认

最新

陈年酒诗

继续分析发现，问题不在a表，在于
g.grpcontno = ‘2017081000014618’
b.grpcontno = ‘2017081000014618’;
两个条件不能同时出现，什么原理？
我看了两个字段类型都是VARCHAR2(20)

SQL> select count(1)
  2  from lcpol b  
  3   inner join lcgrpcont g
  4      on g.grpcontno = b.grpcontno
  5  where g.grpcontno = '2017081000014618'
  6  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3988393551

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                     |     1 |    35 |  2770   (1)| 00:00:34 |
|   1 |  SORT AGGREGATE     |                     |     1 |    35 |            |          |
|   2 |   NESTED LOOPS      |                     |   535K|    17M|  2770   (1)| 00:00:34 |
|*  3 |    INDEX UNIQUE SCAN| PK_LCGRPCONT        |     1 |    17 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX_LCPOL_GRPCONTNO |   535K|  9419K|  2769   (1)| 00:00:34 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("G"."GRPCONTNO"='2017081000014618')
   4 - access("B"."GRPCONTNO"='2017081000014618')

SQL> select count(1)
  2  from lcpol b  
  3   inner join lcgrpcont g
  4      on g.grpcontno = b.grpcontno
  5  where g.grpcontno = '2017081000014618'
  6  and b.grpcontno = '2017081000014618';

Execution Plan
----------------------------------------------------------
Plan hash value: 3988393551

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                     |     1 |    35 |    14   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |                     |     1 |    35 |            |          |
|   2 |   NESTED LOOPS      |                     |    41 |  1435 |    14   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN| PK_LCGRPCONT        |     1 |    17 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX_LCPOL_GRPCONTNO |    41 |   738 |    13   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("G"."GRPCONTNO"='2017081000014618')
   4 - access("G"."GRPCONTNO"="B"."GRPCONTNO")
       filter("B"."GRPCONTNO"='2017081000014618')
SQL> select count(1)
  2  from lcpol b  
  3   inner join lcgrpcont g
  4      on g.grpcontno = b.grpcontno
  5  where  b.grpcontno = '2017081000014618';

Execution Plan
----------------------------------------------------------
Plan hash value: 3988393551

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                     |     1 |    35 |  2770   (1)| 00:00:34 |
|   1 |  SORT AGGREGATE     |                     |     1 |    35 |            |          |
|   2 |   NESTED LOOPS      |                     |   535K|    17M|  2770   (1)| 00:00:34 |
|*  3 |    INDEX UNIQUE SCAN| PK_LCGRPCONT        |     1 |    17 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX_LCPOL_GRPCONTNO |   535K|  9419K|  2769   (1)| 00:00:34 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("G"."GRPCONTNO"='2017081000014618')
   4 - access("B"."GRPCONTNO"='2017081000014618')

有用 0

吾亦可往

从提供的执行计划和查询来看，问题可能出在索引的使用和条件的组合上。

当单独使用 g.grpcontno = '2017081000014618' 作为条件时，索引可以正常被使用进行查询（如第一个执行计划中的 INDEX UNIQUE SCAN 和 INDEX RANGE SCAN）。

而当同时使用 g.grpcontno = '2017081000014618' 和 b.grpcontno = '2017081000014618' 时，执行计划发生了变化（第二个执行计划），可能是因为这种条件组合导致了索引的使用方式改变，或者是数据库在这种情况下对查询进行了不同的优化处理。

比如，可能由于同时对两个表的相同字段进行精确匹配条件的限制，使得数据库在执行时需要更复杂的索引操作或数据关联策略，从而导致了不同的执行计划和性能表现。

有用 0

Thomas

三表关联时，最后一行and b.grpcontno = '2017081000014618'，感觉有点多余。三表都是内连接，第一次内连接已经可确保输出的b表的grpcontno必然是'2017081000014618'。

有用 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏