Select statement doesn't use index.

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' ";

Solution

Change the type of "profile_id" on table B from varchar2 to number.