我该如何优化这个oracle查询?它目前运行时间不到2分钟,当我在.net MVC3中使用ODP执行时需要更长的时间 . . .
SELECT id FROM (
SELECT p.id
FROM account A
INNER JOIN person p on p.id = a.id
WHERE a.account LIKE '1%' AND sysdate between a.start_date and a.end_date AND
a.type = 99 AND a.type2 = 88
UNION ALL
SELECT d.id
FROM account A
INNER JOIN dept d on d.account = a.account
WHERE a.account like '2%' AND sysdate between d.start_date and d.end_date AND
a.type = 100 and a.type2 = 77
)Q GROUP BY id
账户表结构:
-
id:VARCHAR2(9 BYTE)
-
帐号:VARCHAR2(6 BYTE)
-
start_date:DATE
-
end_date:DATE
-
类型:VARCHAR2(2 BYTE)
-
type2:VARCHAR2(3 BYTE)
accountType和deptType上的索引(查询中使用的所有列都不是)
DeptTable结构:
-
id:VARCHAR2(9 BYTE)
-
帐号:VARCHAR2(6 BYTE)
-
start_date:DATE
-
end_date:DATE
索引
-
帐户(用于查询),
-
id(用于查询),
-
start_date(用于查询),
-
end_date(用于查询),
-
deptNum,
-
terminationDate