sql - 试图在 oracle 中完全删除表访问

Question

我有一个简单的查询

select round(sum(a.wt)) as a_wt 
from db.abc a 
where a.date is null 
and a.col_no is not null 
and a.pod_cd = '367' 
and a.fant != a.rce

我想删除完整的表访问。在以下列组合中有 3 个索引类似于这些

1. col_no
2. col_no,date,fant,pyc
3. wagno,batno

可以做些什么来删除表访问已满。

标签： sqloraclequery-performance

Answer 1

一种选择是创建一个Function Based Index：

create index idx_date_col_pod on ABC (nvl("date",date'1900-01-01'), nvl(col_no,0), pod_cd);

并将查询转换为：

select round(sum(wt)) as a_wt
  from abc
 where nvl("date",date'1900-01-01') = date'1900-01-01' -- matching means "date" column is null assuming there exists no records with this ancient date.
   and nvl(col_no,0) != 0 -- non-matching means "col_no" column is not null
   and pod_cd = 367
   and fant != rce