sql - 调整复杂查询
问题描述
处理报告提取和复杂查询需要 3 个小时。需要调整建议
有一个包含 3M 记录的主表，由于多个连接，迭代需要很长时间
表：
- 员工
- 部门
- 地址
- 薪水单
- 税
Employee 表有 3M 条记录，当我运行以下查询时，大约需要 3 小时才能获取数据。需要单独获取从昨天到今天的数据发生变化的员工的数据
select
emp.employee_id || '|' || emp.int_acct_id || '|' || dpt.department_desc
|| '|' || dpt.start_date || '|' || dpt.end_date
|| '|' || dpt.productivity_percent || '|' || addr.street_addr
|| '|' || addr.apt || '|' || addr.city || '|' || addr.state
|| '|' || addr.country || '|' || addr.zip || '|' || pay_for_year
|| '|' || total_paid || '|' || pay_for_current_year || '|' || w2_issue_flg
from employee emp
inner join (
select int_acct_id from (
select int_acct_id from employee
where last_updated_time between '03-nov-19' and '04-nov-19'
union
select int_acct_id from department
where last_updated_time between '03-nov-19' and '04-nov-19'
) dual
) a1 on emp.int_acct_id = a1.int_acct_id
left outer join (
select department_desc, start_date, end_date, int_acct_id,
productivity_percent from department
) dpt on emp.int_acct_id = dpt.int_acct_id
left outer join (
select addr_acct_id, employee_id, street_addr, apt, city, state, country, zip
from address
) addr on emp.employee_id = addr.employee_id
left outer join (
select a.pay_for_year, a.four_not_one as total_paid,
b.total_pay as pay_for_curren_year, a.int_acct_id from tax a, (
select sum(total_pay) as total_pay, int_acct_id
from paystubs where pay_year = (select cur_year from year_dat)
group by int_acct_id
) b
where a.int_acct_id = b.int_acct_id(+)
) on a.int_acct_id = emp.int_acct_id
有没有其他方法或方法我们可以用来首先过滤掉时间戳中修改过的记录，然后检查其他值。（对我来说，光标不是一个选项）数据是从 Oracle DB 加载到 AIX 服务器，因此需要时间。提示也无济于事，我尝试了领导/订购了两者，但没有运气。