由于类型转换，索引未使用？ [英] Index not used due to type conversion?

问题描述

由于特定表格上的全表扫描，我有一个进程执行不正常。我已经计算统计，重建现有的索引，并尝试为这个表添加新的索引，但这还没有解决这个问题。

一个隐式类型转换可以停止索引被使用？其他原因怎么办？全表扫描的成本大于索引查找的大约1000.

编辑：

SQL语句：

 从src_table中选择unique_key 
 
其中natural_key1 =：1 
和natural_key2 =：2 
 and natural_key3 =：3; 
 





• natural_key1的基数高，但有一个类型转换。 / li>
  
• 自然键的其他部分都是低基数，并且未启用位图索引。


• 表大小约为1,000,000条记录。
  

Java代码（不容易修改）：

  ps.setLong（1，oid）; 
  

这与列数据类型冲突：varchar2

您的查询是：

  select 
 unique_key 
 from 
 src_table 
其中
 natural_key1 =：1 
  

在您的情况下，不使用索引，因为 natural_key1 code> varchar2 和：1 是一个数字。 Oracle正在将您的查询转换为：

  select 
 unique_key 
从
 src_table 
其中
 to_number（natural_key1）=：1 
  

to_number（natural_key1）：

  create index ix_src_table_fnk1 on src_table （natural_key1））; 
  

您的查询现在将使用 ix_src_table_fnk1 索引。

当然，最好让你的Java程序员在第一时间正确地做。

I have a process that is performing badly due to full table scans on a particular table. I have computed statistics, rebuilt existing indices and tried adding new indices for this table but this hasn't solved the issue.

Can an implicit type conversion stop an index being used? What about other reasons? The cost of a full table scan is around 1000 greater than the index lookup should be.

EDIT:

SQL statement:

select unique_key 
from src_table 
where natural_key1 = :1 
and natural_key2 = :2 
and natural_key3 = :3;

• Cardinality of natural_key1 is high, but there is a type conversion.
• The other parts of the natural key are low cardinality, and bitmap indices are not enabled.
• Table size is around 1,000,000 records.

Java code (not easily modifiable):

ps.setLong(1, oid);

This conflicts with the column datatype: varchar2

解决方案

You could use a function-based index.

Your query is:

select
    unique_key 
from
    src_table
where
    natural_key1 = :1

In your case the index isn't being used because natural_key1 is a varchar2 and :1 is a number. Oracle is converting your query to:

select
    unique_key 
from
    src_table
where
    to_number(natural_key1) = :1

So... put on an index for to_number(natural_key1):

create index ix_src_table_fnk1 on src_table(to_number(natural_key1));

Your query will now use the ix_src_table_fnk1 index.

Of course, better to get your Java programmers to do it properly in the first place.

这篇关于由于类型转换，索引未使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！