代码之家 › 专栏 › 技术社区 › parkr

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

indexing oracle performance database

parkr · 技术社区 · 15 年前

我有一个进程,由于在特定表上进行了完整的表扫描,它的性能很差。我已经计算了统计数据,重新构建了现有的索引,并尝试为此表添加新的索引,但这并没有解决这个问题。

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

编辑:

SQL语句:

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

自然关键字1的基数很高,但存在类型转换。
自然键的其他部分的基数较低,并且未启用位图索引。
表大小约为1000000条记录。

Java代码(不容易修改):

ps.setLong(1, oid);

这与列数据类型varchar2冲突

4 回复 | 直到 15 年前

Nick Pierpoint 15 年前

可以使用基于函数的索引。

您的查询是:

select
    unique_key 
from
    src_table
where
    natural_key1 = :1

在您的案例中,索引没有被使用,因为 natural_key1 是一个 varchar2 和 :1

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

所以…为添加索引 to_number(natural_key1)

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

ix_src_table_fnk1

Vincent Malgrat 15 年前

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

SQL> select * from a where id = 100;

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

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

id to_number(id)

select * from a where id = '100';

documentation

"WHERE column=variable"

, 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.

隐式转换的算法会随着软件版本和Oracle产品的变化而变化。显式转换的行为更容易预测。

Quassnoi 15 年前

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

,因为它不能使用索引。Oracle无法反转 TRUNC()

这是好的:

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

SELECT  *
FROM    mytable
WHERE   guid = '794AB5396AE5473DA75A9BF8C4AA1F74'

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

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

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

Steve Broberg 15 年前

如果使用围绕参数的显式转换运行查询(例如,根据需要转换为_char(:1)或_number(:1),会发生什么情况?如果这样做使查询运行得很快,您就得到了答案。

但是,如果查询在显式转换中仍然运行缓慢,则可能还有另一个问题。您没有提到正在运行的Oracle版本,如果您的高基数列(natural_key1)的值的分布非常不均匀,则您可能正在使用在首次运行查询时生成的查询计划,该计划对:1使用了不利的值。

例如,如果100万行的表中有40万行的自然关键字1=1234,其余60万行是唯一的(或几乎是唯一的),那么如果查询被约束在自然关键字1=1234,优化器将不会选择索引。由于您使用的是绑定变量,如果这是第一次运行查询,那么优化器将为所有后续运行选择该计划。

alter system flush shared_pool;

这将从优化器的大脑中删除所有查询计划,因此下一次运行的语句将被重新优化。或者,您可以将该语句作为带有文本的直接SQL来运行,而不需要绑定变量。如果这两种情况都运行良好,你就会知道你的问题是由于计划的腐败。

如果是这种情况,您不希望在生产环境中使用alter system命令-如果定期运行它,可能会破坏系统的其余性能,但您可以使用动态SQL而不是绑定变量来绕过它,或者如果可以提前确定:1是非选择性的,请使用稍有不同的查询。对于非选择性的情况(例如重新排序WHERE子句中的条件,这将导致优化器使用不同的计划)。

  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;

我不太喜欢索引提示——它们是一种非常脆弱的编程方法。如果索引上的名称发生了变化,在查询开始执行得不好之前,您永远不会知道它。另外,如果服务器升级或数据分发发生了变化,导致优化器能够选择更好的计划,则可能会让您陷入困境。