Hi,
Can we increase the performance of the query.
This query retrives the latest dept information from the table.
This table contains million of records.
table structure:-
Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
DEPT_SEQ_I SYSIBM INTEGER 4 0 No
EFF_D SYSIBM DATE 4 0 No
DIV_SEQ_I SYSIBM INTEGER 4 0 No
ENT_CO_SEQ_I SYSIBM INTEGER 4 0 No
DEPT_I SYSIBM INTEGER 4 0 No
DEPT_N SYSIBM CHARACTER 35 0 No
DEPT_TYPE_C SYSIBM CHARACTER 6 0 No
GROC_DEPT_F SYSIBM CHARACTER 1 0 No
HIER_CHG_F SYSIBM CHARACTER 1 0 No
SRC_SYS_C SYSIBM CHARACTER 6 0 No
CRTE_D SYSIBM DATE 4 0 No
CRTE_TI SYSIBM TIME 3 0 No
Query:-
select dept.div_seq_i,dept.dept_i
from adw.dept dept
join(select dept_i,max(eff_d) dp_eff_d
from adw.dept group by dept_i) curr_dept_i
on dept.dept_i=curr_dept_i.dept_i and dept.eff_d=curr_dept_i.dp_eff_d
Thanks in advance
[Updated on: Thu, 05 June 2008 01:51] by Moderator
Report message to a moderator