Select statement doesn't use index.

最新推荐文章于 2021-05-07 20:09:08 发布

原创最新推荐文章于 2021-05-07 20:09:08 发布 · 871 阅读

0 ·

CC 4.0 BY-SA版权

ORACLE 专栏收录该内容

113 篇文章

订阅专栏

本文探讨了在特定SQL查询中由于类型不匹配而导致索引无法被利用的问题。通过实例说明了当进行表连接操作时，如果连接条件涉及不同类型的数据列，将导致查询优化器无法使用已有的索引。并给出了修改列类型的解决方案。

Symptoms

A table stg_customer has 1,500,000 data with a index "IDX_PROFILE_ID" on column profile_id, but when select on this column, it doesn't use index:

select count(*)
FROM stg_wishlist A
where updated_at>= to_date('2010-01-01','yyyy-mm-dd')
and exists (select 1 from stg_customer B where A.customer_id=B.profile_id );

Even when use a simple sql and a hint, it also not use index:

select /*+INDEX(B IDX_PROFILE_ID)*/ * from stg_customer B where B.profile_id=1;

Cause

With the first sql:

The column "customer_id" of table A is number, but the column "profile_id" of table B is varchar2, so when we use "A.customer_id=B.profile_id", there will be a type change, so it can't index.

With the second sql:

Because B.profile_id is varchar2, when we use "where B.profile_id=1", it also have a type change, so we need to use " B.profile_id='1' ";