oracle 由于类型转换而未使用索引？

Index not used due to type conversion?

提问by parkr

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.

隐式类型转换可以停止使用索引吗？其他原因呢？全表扫描的成本比索引查找的成本高 1000 左右。

EDIT:

编辑：

SQL statement:

SQL语句：

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.

• natural_key1 的基数高，但是有类型转换。
• 自然键的其他部分为低基数，不启用位图索引。
• 表大小约为 1,000,000 条记录。

Java code (not easily modifiable):

Java 代码（不易修改）：

ps.setLong(1, oid);

This conflicts with the column datatype: varchar2

这与列数据类型冲突：varchar2

采纳答案by Nick Pierpoint

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_key1is a varchar2and :1is a number. Oracle is converting your query to:

在您的情况下，索引没有被使用，因为natural_key1是一个varchar2并且:1是一个数字。Oracle 正在将您的查询转换为：

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

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

所以...为 建立一个索引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_fnk1index.

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

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

当然，最好首先让您的 Java 程序员正确地完成它。

回答by Vincent Malgrat

an implicitconversion can prevent an index from being used by the optimizer. Consider:

的隐式转换可以防止使用由优化的指标。考虑：

SQL> CREATE TABLE a (ID VARCHAR2(10) PRIMARY KEY);

Table created

SQL> insert into a select rownum from dual connect by rownum <= 1e6;

1000000 rows inserted

This is a simple table but the datatype is not 'right', i-e if you query it like this it will full scan:

这是一个简单的表，但数据类型不“正确”，即如果您像这样查询它，它将进行全面扫描：

SQL> select * from a where id = 100;

ID
----------
100

This query is in fact equivalent to:

这个查询实际上相当于：

select * from a where to_number(id) = 100;

It cannot use the index since we indexed idand not to_number(id). If we want to use the index we will have to be explicit:

它不能使用索引，因为我们索引了id而不是to_number(id)。如果我们想使用索引，我们必须明确：

select * from a where id = '100';

In reply to pakr's comment:There are lots of rules concerning implicit conversions. One good place to start is the documentation. Among other things, we learn that:

回复 pakr 的评论：关于隐式转换有很多规则。一个好的起点是 文档。除其他外，我们了解到：

During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.

在 SELECT FROM 操作期间，Oracle 将列中的数据转换为目标变量的类型。

It means that when implicit conversion occurs during a "WHERE column=variable"clause, Oracle will convert the datatype of the column and NOT of the variable, therefore preventing an index from being used. This is why you should always use the right kind of datatypes or explicitly converting the variable.

这意味着当"WHERE column=variable"子句中发生隐式转换时，Oracle 将转换列的数据类型和变量的 NOT 数据类型，从而阻止使用索引。这就是为什么您应该始终使用正确类型的数据类型或显式转换变量的原因。

From the Oracle doc:

来自 Oracle 文档：

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

• SQL statements are easier to understand when you use explicit datatype conversion functions.
• Implicit datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.
• Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.
• Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

Oracle 建议您指定显式转换，而不是依赖隐式或自动转换，原因如下：

• 使用显式数据类型转换函数时，SQL 语句更容易理解。
• 隐式数据类型转换会对性能产生负面影响，尤其是在将列值的数据类型转换为常量而不是相反时。
• 隐式转换取决于它发生的上下文，并且可能不会在每种情况下都以相同的方式工作。例如，从日期时间值到 VARCHAR2 值的隐式转换可能会返回意外的年份，具体取决于 NLS_DATE_FORMAT 参数的值。
• 隐式转换算法可能会因软件版本和 Oracle 产品而异。显式转换的行为更可预测。

回答by Quassnoi

Make you condition sargable, that is compare the field itself to a constant condition.

让你有条件sargable，即将字段本身与恒定条件进行比较。

This is bad:

这不好：

SELECT  *
FROM    mytable
WHERE   TRUNC(date) = TO_DATE('2009.07.21')

, since it cannot use the index. Oracle cannot reverse the TRUNC()function to get the range bounds.

，因为它不能使用索引。Oracle 无法反转该TRUNC()函数以获取范围边界。

This is good:

这很好：

SELECT  *
FROM    mytable
WHERE   date >= TO_DATE('2009.07.21')
        AND date < TO_DATE('2009.07.22')

To get rid of implicit conversion, well, use explicit conversion:

要摆脱隐式转换，请使用显式转换：

This is bad:

这不好：

SELECT  *
FROM    mytable
WHERE   guid = '794AB5396AE5473DA75A9BF8C4AA1F74'

-- This uses implicit conversion. In fact this is RAWTOHEX(guid) = '794AB5396AE5473DA75A9BF8C4AA1F74'

This is good:

这很好：

SELECT  *
FROM    mytable
WHERE   guid = HEXTORAW('794AB5396AE5473DA75A9BF8C4AA1F74')

Update:

更新：

This query:

这个查询：

SELECT  unique_key
FROM    src_table
WHERE   natural_key1 = :1
        AND natural_key2 = :2
        AND natural_key3 = :3

heavily depends on the type of your fields.

很大程度上取决于您的字段类型。

Explicitly cast your variables to the field type, as if from string.

将变量显式转换为字段类型，就像从字符串一样。

回答by Steve Broberg

What happens to your query if you run it with an explicit conversion around the argument (e.g., to_char(:1) or to_number(:1) as appropriate)? If doing so makes your query run fast, you have your answer.

如果您使用围绕参数的显式转换（例如，to_char(:1) 或 to_number(:1) 视情况而定）运行它，您的查询会发生什么情况？如果这样做可以使您的查询运行得更快，那么您就有了答案。

However, if your query still runs slow with the explicit conversion, there may be another issue. You don't mention what version of Oracle you're running, if your high-cardinality column (natural_key1) has values that have a very skewed distribution, you may be using a query plan generated when the query was first run, which used an unfavorable value for :1.

但是，如果您的查询在显式转换时仍然运行缓慢，则可能存在另一个问题。您没有提及您正在运行的 Oracle 版本，如果您的高基数列 (natural_key1) 的值分布非常倾斜，则您可能正在使用第一次运行查询时生成的查询计划，该计划使用了不利的价值：1。

For example, if your table of 1 million rows had 400,000 rows with natural_key1 = 1234, and the remaining 600,000 were unique (or nearly so), the optimizer would not choose the index if your query constrained on natural_key1 = 1234. Since you're using bind variables, if that was the first time you ran the query, the optimizer would choose that plan for all subsequent runs.

例如，如果您的 100 万行表有 400,000 行且 natural_key1 = 1234，而剩下的 600,000 是唯一的（或几乎唯一），如果您的查询限制在 natural_key1 = 1234 上，优化器将不会选择索引。因为您是使用绑定变量，如果这是您第一次运行查询，优化器将为所有后续运行选择该计划。

One way to test this theory would be to run this command before running your test statement:

测试此理论的一种方法是在运行测试语句之前运行此命令：

alter system flush shared_pool;

This will remove all query plans from the optimizer's brain, so the next statement run will be optimized fresh. Alternatively, you could run the statement as straight SQL with literals, no bind variables. If it ran well in either case, you'd know your problem was due to plan corruption.

这将从优化器的大脑中删除所有查询计划，因此下一个语句运行将被重新优化。或者，您可以将语句作为带有文字的直接 SQL 运行，没有绑定变量。如果它在任何一种情况下都运行良好，您就会知道您的问题是由于计划损坏造成的。

If that is the case, you don't want to use that alter system command in production - you'll probably ruin the rest of your system's performance if you run it regularly, but you could get around it by using dynamic sql instead of bind variables, or if it is possible to determine ahead of time that :1 is non-selective, use a slightly different query for the nonselective cases (such as re-ordering the conditions in the WHERE clause, which will cause the optimizer to use a different plan).

如果是这种情况，您不想在生产中使用该 alter system 命令 - 如果您定期运行它，您可能会破坏系统的其余部分性能，但是您可以通过使用动态 sql 而不是 bind 来解决它变量，或者如果可以提前确定 :1 是非选择性的，则对非选择性情况使用稍微不同的查询（例如重新排序 WHERE 子句中的条件，这将导致优化器使用不同的计划）。

Finally, you can try adding an index hint to your query, e.g.:

最后，您可以尝试向查询添加索引提示，例如：

  SELECT /*+ INDEX(src_table,<name of index for natural_key1>) */
         unique_key
    FROM src_table
   WHERE natural_key1 = :1
     AND natural_key2 = :2
     AND natural_key3 = :3;

I'm not a big fan of index hints - they're a pretty fragile method of programming. If the name changed on the index down the road, you'd never know it until your query started to perform poorly, plus you're potentially shooting yourself in the foot if server upgrades or data distribution changes result in the optimizer being able to choose an even better plan.

我不是索引提示的忠实粉丝——它们是一种非常脆弱的编程方法。如果以后索引上的名称发生了变化，您将永远不会知道它，直到您的查询开始表现不佳，而且如果服务器升级或数据分布更改导致优化器能够选择，您可能会受到打击一个更好的计划。