Home » Other » General » Query performance (oracle 10g)
Query performance [message #361060] Mon, 24 November 2008 23:53 Go to next message
piscean_n
Messages: 36
Registered: December 2007
Member
SELECT COUNT(*) FROM(SELECT distinct ra.emp_id FROM EMPLOYEE ra WHERE  
ra.emp_id in
  (
  select rs.emp_id from employee_details rs WHERE rs.chg_date = '20080218' And 
  (rs.status in('ab','ac')
   OR (ra.dept_id in ('S','L')and rs.status ='E') 
  )
  ) 
  OR  ra.request_id IN 
  (
  SELECT r.emp_id from employee r  where TRUNC(r.CHG_DATE) = TO_DATE('18/02/2008','DD/MM/YYYY') 
  And (r.status in('ab','ac')
   OR (ra.dept_id in ('S','L')and r.status ='E') 
  ) 
  ) 
)

eXECUTION PLAN IS given below.
SELECT STATEMENT, GOAL = ALL_ROWS 6 1
SORT AGGREGATE 1
VIEW MNM 6 101
FILTER
PARTITION RANGE ALL 6 101 707
TABLE ACCESS FULL MNM EMPLOYEE 6 101 707
FILTER
PARTITION RANGE SINGLE 5 1 17
TABLE ACCESS BY LOCAL INDEX ROWID MNM EMPLOYEE_DETAILS 5 1 17
INDEX RANGE SCAN MNM IX_EMPLOYEE_DETAILS_1 2 12
FILTER
TABLE ACCESS BY GLOBAL INDEX ROWID MNM EMPLOYEE 2 1 15
INDEX UNIQUE SCAN MNM PK_EMPLOYEE 1 1

Can you please suggest to improve the query.
Thanks in advance

[Updated on: Mon, 24 November 2008 23:54]

Report message to a moderator

Re: Query performance [message #361068 is a reply to message #361060] Tue, 25 November 2008 00:06 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


1. Avoid outer SELECT Query and use count(*) instead of distinct ra.emp_id in inner query.

2. Are you sure thats rs.chg_date is VARCHAR and r.CHG_DATE is Date ?

3. Change the entire code to use WHERE EXISTS and check to see any performance.

Smile
Rajuvan.
Re: Query performance [message #361150 is a reply to message #361068] Tue, 25 November 2008 03:28 Go to previous messageGo to next message
piscean_n
Messages: 36
Registered: December 2007
Member
Thanks but it didn't improve any performance and using exists i'am getting wrong results
Re: Query performance [message #361157 is a reply to message #361060] Tue, 25 November 2008 04:13 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:
using exists i'am getting wrong results


Problem is not that Result is wrong , but the query is wrong.
You can get the same result with WHERE EXISTS. Paste here what you tried .

Smile
Rajuvan.
Previous Topic: Need to create new
Next Topic: Performance
Goto Forum:
  


Current Time: Thu Mar 28 19:20:18 CDT 2024