求助！为什么总不用相应的索引？

白河小子

表xxx的两个索引
idx_3col 字段 email_status,birthdate,email
idx_5col 字段 email_status,city,gender,birthdate,interests_value
两个sql
这个总在用idx_3col
select count(*)  from xxx where
email_status='N'
and (city is null or city is not null )
and ( gender is null or gender is not null )
and birthdate between add_months(sysdate,-35*12)
and add_months(sysdate,-25*12)
and ( floor(mod(interest_value/power(2,,2))=1
or floor(mod(interest_value/power(2,12),2))=1 );

这个用的idx_5col
select count(*)  from XXX where
email_status='N'
and city in ('51_hefei','51_beijing','51_chongqing','51_fuzhou')
and ( gender is null or gender is not null )
and birthdate between add_months(sysdate,-35*12)
and add_months(sysdate,-25*12)
and ( floor(mod(interest_value/power(2,,2))=1
or floor(mod(interest_value/power(2,12),2))=1 )

我希望两个都用idx_5col,而且也应该是这样的。为什么会这样呢？

doni

and (city is null or city is not null )
这句不是等于没写？
而且 city is null 是不会用index的，一定是FTS(full table scan)
这一点，好象在书上看到过，时间长了，不太确定。

szhang

Could you try this SQL as below:

select count(*) from xxx
where email_status='N'
and city is null
and gender is null
and birthdate between add_months(sysdate,-35*12) and add_months(sysdate,-25*12)
and ( floor(mod(interest_value/power(2,,2))=1 or floor(mod(interest_value/power(2,12),2))=1 )
union
select count(*) from xxx
where email_status='N'
and city is not null
and gender is not null
and birthdate between add_months(sysdate,-35*12) and add_months(sysdate,-25*12)
and ( floor(mod(interest_value/power(2,,2))=1 or floor(mod(interest_value/power(2,12),2))=1 );

白河小子

原帖由 "szhang" 发表：
Could you try this SQL as below:

select count(*) from xxx
where email_status='N'
and city is null
and gender is null
and birthdate between add_months(sysdate,-35*12) and add_months(sysdate,-..........

结果是两个索引都用了