主页 / dba / 问题 / 48109
Accepted
haki
Asked: 2013-08-14 23:01:35 +0800 CST

为什么 oracle 不使用索引进行不同的查询？

  • 772

这是场景

SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true)

PL/SQL procedure successfully completed.

SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / 1024 MB , BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('TM', 'TM_LD_IX');

SEGMENT_NAME            SEGMENT_TYPE        MB        BLOCKS
------------------------------------------ ---------- ----------
TM                      TABLE                 296      37888
TM_LD_IX                INDEX                  46       5888

SQL> select index_name , column_name from user_ind_columns where index_name = 'TM_LD_IX';

INDEX_NAME   COLUMN_NAME
------------ ------------------------------
TM_LD_IX     LD

SQL> explain plan for select distinct LD from TM;

Explained.

SQL> @ex

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4241255022

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 |
--------------------------------------------------------------------------------------

9 rows selected.

SQL> explain plan for select /*+ index(x , TM_LD_IX) */ distinct LD from TM x;

Explained.

SQL> @ex

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4241255022

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 |
--------------------------------------------------------------------------------------

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

如您所见，oracle 没有使用索引，LD而是选择了全表扫描。我什至不能让他使用带有历史记录的索引。

在上面的简单查询中，我希望对TM_LD_IX. mydb_file_multiblock_read_count设置为 32，所以我预计成本约为 5888 / 32 = 184（使用索引我还可以节省哈希唯一的成本）。

那么，我在这里缺少什么？

oracle optimization

1 个回答

  1. Best Answer

    这种行为的原因是在索引中找不到 LD 为 NULL 的行。因此 Oracle 必须扫描整个表。如果表是使用 LD 作为 NOT NULL 列创建的，则优化器使用此信息并执行 INDEX FAST FULL SCAN。如果您向未为列 LD 定义 NOT NULL 的表添加“CHECK(LD is not null)”约束，则优化器不会使用约束提供的信息并再次进行全表扫描，即使您给了他一个提示。乔纳森刘易斯写了关于这种行为的文章。

    以下脚本演示了 Oracle 11.2.0.3.0 的这种行为

    *create_table.sql* 向表中插入数据并创建索引和统计信息

    关闭自动跟踪
删除表对象
/
创建表对象（
  object_id 编号，
  所有者 varchar2(30),
  对象名 varchar2(128),
  object_type varchar2(19)
)
/

插入对象（
  object_id,
  所有者，
  对象名称，
  对象类型
)
选择
  object_id,
  所有者，
  对象名称，
  对象类型
来自 dba_objects
/


在对象（object_id）上创建索引 idx_object_id；

exec dbms_stats.gather_table_stats(user,'objects', cascade=>true)

    现在运行以下脚本：

    阀芯输出
关闭反馈
设置 linesize 300
设置修剪
设置修剪阀

@create_table
设置自动跟踪 traceonly 解释

迅速的
提示1.无约束的查询计划：
选择不同的 object_id
来自物体；
rem------------------------------------------------ --

@create_table
alter table objects 添加约束 nn_object_id check(object_id is not null) validate;
设置自动跟踪 traceonly 解释

迅速的
prompt 2. plan for query with check constraint
选择不同的 object_id
来自物体；
rem------------------------------------------------ --

@create_table
alter table objects 修改 object_id 不为空；
设置自动跟踪 traceonly 解释

迅速的
prompt 3.plan for query with NOT NULL 列
选择不同的 object_id
来自物体；
rem------------------------------------------------ --

@create_table
在对象上创建位图索引 bidx_object_type(object_type)
/
设置自动跟踪 traceonly 解释

迅速的
prompt 4.plan for query with bitmap index
选择不同的 object_type
来自物体；
rem------------------------------------------------ --


脱线

    这给出了以下输出

    1. 无约束查询计划：

执行计划
-------------------------------------------------- --------
计划哈希值：4077265613

-------------------------------------------------- ----------------------------
| 身份证 | 操作 | 姓名 | 行 | 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- ----------------------------
| 0 | 选择声明 | | 59063 | 288K| 139 (3)| 00:00:02 |
| 1 | 哈希唯一 | | 59063 | 288K| 139 (3)| 00:00:02 |
| 2 | 表访问已满| 对象 | 59063 | 288K| 136 (0)| 00:00:02 |
-------------------------------------------------- ----------------------------


2. 带检查约束的查询计划

执行计划
-------------------------------------------------- --------
计划哈希值：4077265613

-------------------------------------------------- ----------------------------
| 身份证 | 操作 | 姓名 | 行 | 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- ----------------------------
| 0 | 选择声明 | | 59063 | 288K| 139 (3)| 00:00:02 |
| 1 | 哈希唯一 | | 59063 | 288K| 139 (3)| 00:00:02 |
| 2 | 表访问已满| 对象 | 59063 | 288K| 136 (0)| 00:00:02 |
-------------------------------------------------- ----------------------------


3. 使用 NOT NULL 列进行查询计划

执行计划
-------------------------------------------------- --------
计划哈希值：4172758181

-------------------------------------------------- -------------------------------------
| 身份证 | 操作 | 姓名 | 行 | 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- -------------------------------------
| 0 | 选择声明 | | 59063 | 288K| 40 (8)| 00:00:01 |
| 1 | 哈希唯一 | | 59063 | 288K| 40 (8)| 00:00:01 |
| 2 | 索引快速全扫描| IDX_OBJECT_ID | 59063 | 288K| 37 (0)| 00:00:01 |
-------------------------------------------------- -------------------------------------


4.plan for query with bitmap index

执行计划
-------------------------------------------------- --------
计划哈希值：2970019208

-------------------------------------------------- -----------------------------------------------------------
| 身份证 | 操作 | 姓名 | 行 | 字节 | 成本 (%CPU)| 时间 |
-------------------------------------------------- -----------------------------------------------------------
| 0 | 选择声明 | | 43 | 387 | 6 (34)| 00:00:01 |
| 1 | 哈希唯一 | | 43 | 387 | 6 (34)| 00:00:01 |
| 2 | 位图索引快速全扫描| BIDX_OBJECT_TYPE | 59063 | 519K| 4 (0)| 00:00:01 |
-------------------------------------------------- -----------------------------------------------------------

    概括

    如果列上有一个正常的 B*-tree 索引 NULL 值在列中是可能的，那么优化器不能仅仅依靠索引的信息来做'select distinc'并使一个 TABLE ACCESS FULL 。

    如果在列上有一个正常的 B*-tree 索引和一个 NOT-NULL 检查约束，则优化器也不依赖索引的信息，并使 TABLE ACCESS FULL 成为可能。

    如果有一个正常的 B*-tree 索引并且该列定义为 NOT NULL，那么优化器依赖于索引的信息并执行 INDEX FAS FULL SCAN。

    如果列上有位图索引，则优化器知道所有信息都在索引中并执行 BITMAP INDEX FAST FULL SCAN

    • 10

相关问题