Oracle分区表创建方式不同导致执行计划异常问题咨询

我正在研究Oracle表分区对性能的影响，现有一张包含45000行数据的普通表CLIENTS，尝试创建结构和数据完全一致的分区表用于实验时，发现两种创建方式对应的执行计划存在明显差异：

方式一：使用CTAS语句创建分区表pClients #

通过CREATE TABLE...AS SELECT语句直接创建分区表并导入数据：

create table pClients
tablespace TS1
partition by list (pays)(
    Partition PAUST values('Australia') tablespace TS2,
    Partition Pamer values('Chile', 'Brazil', 'USA', 'Canada', 'Mexico') tablespace TS2,
    Partition pafri values('South_africa', 'Cameroun') tablespace TS1,
    Partition peur values('Netherlands', 'Germany', 'Spain', 'Italy', 'France', 'Portugal', 'Switzeland') tablespace TS1,
    Partition poth values (DEFAULT) tablespace TS3
)
as (select * from clients);

对该表执行SELECT *查询，执行计划符合预期：

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          | 45000 |  4130K|   195   (1)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|          | 45000 |  4130K|   195   (1)| 00:00:01 |     1 |     5 |
|   2 |   TABLE ACCESS FULL| PCLIENTS | 45000 |  4130K|   195   (1)| 00:00:01 |     1 |     5 |
-----------------------------------------------------------------------------------------------

方式二：先创建分区表再插入数据 #

先手动创建与CLIENTS结构一致的分区表p0Clients，再插入原表数据：

create table p0Clients (
    noclient  number primary key,
    nom  varchar2(50) not null,
    prenom varchar2(50),
    adresse1 varchar2(100),
    adresse2 varchar2(100),
    codepostal varchar2(10),
    ville varchar2(50),
    pays varchar2(15),
    tel varchar2(20),
    email varchar2(50)
)
tablespace TS1
partition by list (pays)(
    Partition P0AUST values('Australia') tablespace TS2,
    Partition P0amer values('Chile', 'Brazil', 'USA', 'Canada', 'Mexico') tablespace TS2,
    Partition p0afri values('South_africa', 'Cameroun') tablespace TS1,
    Partition p0eur values('Netherlands', 'Germany', 'Spain', 'Italy', 'France', 'Portugal', 'Switzeland') tablespace TS1,
    Partition p0oth values (DEFAULT) tablespace TS3
);

insert into p0Clients select * from Clients;

执行相同的SELECT *查询，执行计划中的行数和成本与方式一差异明显：

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           | 55501 |    12M|  1368   (1)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|           | 55501 |    12M|  1368   (1)| 00:00:01 |     1 |     5 |
|   2 |   TABLE ACCESS FULL| P0CLIENTS | 55501 |    12M|  1368   (1)| 00:00:01 |     1 |     5 |
------------------------------------------------------------------------------------------------

问题原因 #

核心差异来自统计信息的收集机制：

• CTAS创建表时，Oracle会自动收集新表（包括分区）的完整统计信息，因此执行计划中的行数估算（45000）和成本计算完全匹配实际数据量。
• 先建表再插入数据的方式，默认情况下Oracle不会自动触发统计信息收集（除非开启了自动统计信息收集任务且满足触发条件）。此时优化器只能通过动态采样或默认估算规则猜测表的行数，导致估算值（55501）与实际不符，进而使成本计算出现偏差。

另外需要注意：方式二中手动指定了主键约束，而CTAS语句不会自动继承原表的约束，但这一点不会直接影响全表扫描的行数估算，不是执行计划差异的核心原因。

验证方法 #

对p0Clients手动收集统计信息后，执行计划会恢复正常：

EXEC DBMS_STATS.GATHER_TABLE_STATS('你的用户名', 'P0CLIENTS');

内容的提问来源于stack exchange，提问作者joudia