Home » RDBMS Server » Performance Tuning » How to improve this query (oracle 10g)
How to improve this query [message #325122] Thu, 05 June 2008 01:33 Go to next message
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
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 

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

Re: How to improve this query [message #325127 is a reply to message #325122] Thu, 05 June 2008 01:52 Go to previous message
Michel Cadot
Messages: 63917
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Previous Topic: query is taking index sacn,even though it is taking long time and it is not completing
Next Topic: How to perforamnce this query? Help
Goto Forum:

Current Time: Tue Oct 25 00:22:05 CDT 2016

Total time taken to generate the page: 0.12860 seconds