列定义是否为空对COUNT（*）操作索引选择的影响-优快云博客

本文介绍了Oracle数据库中表结构的修改方法，包括字段约束的添加，并对比了不同查询计划的成本与执行效率。通过示例展示了如何通过设置字段为非空来创建索引，进而优化查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> desc test;
Name        Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME         VARCHAR2(128)
OBJECT_ID         NUMBER

=====================================

SQL> select count(*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id | Operation    | Name | Rows | Cost (%CPU)| Time   |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 1 | 5   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |   | 1 |        |   |
|   2 |   TABLE ACCESS FULL| TEST | 2305 | 5   (0)| 00:00:01 |
-------------------------------------------------------------------

=========SQL> alter table test modify(object_id not null);

Table altered.

SQL> desc test;
Name        Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME         VARCHAR2(128)
OBJECT_ID       NOT NULL NUMBER

SQL> select count(*) from test;

COUNT(*)
----------
2305

Execution Plan
----------------------------------------------------------
Plan hash value: 911472365

----------------------------------------------------------------------------
| Id | Operation       | Name    | Rows | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |     | 1 | 3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE       |     | 1 |  |    |
|   2 |   INDEX FAST FULL SCAN| INX_OBJ_ID | 2305 | 3   (0)| 00:00:01 |
----------------------------------------------------------------------------
===================================================