列定义是否为空对COUNT（*）操作索引选择的影响

最新推荐文章于 2024-09-11 17:50:36 发布

最新推荐文章于 2024-09-11 17:50:36 发布 · 174 阅读

文章标签：

#SQL #Access

oracle SQL 专栏收录该内容

30 篇文章

订阅专栏

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

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 |
----------------------------------------------------------------------------
===================================================