Home » RDBMS Server » Performance Tuning » Slow query
Slow query [message #303405] Fri, 29 February 2008 03:44 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hello,
When i execute the following query it takes 7 minutes to execute. EMP table contains 100.000 records.
And output is of 9000 rows.
I am using oracle 10g.

SELECT E1.emp_id,E1.First_name,E1.Last_name
FROM EMP E1 inner join EMP E2 
		ON E1.first_name=E2.first_name 
		AND E2.department_id=110 
		AND E2.Designation='Dev' 
		AND E2.educated='Y'
WHERE E1.department_id=500;


But if i will remove the E2.Designation condition then it takes 20 Sec to execute.

I seen the Designation column. It doenst have any Index. And many column values are null.
And it has only 2 values i.e. Dev/Non-Dev.

Then do i need to create index on the Designation column.
If yes then i think BITMAP index will be useful. Am i right?

If no need to create index then any other solution please.
Thanks in advance.

[Updated on: Fri, 29 February 2008 03:56]

Report message to a moderator

Re: Slow query [message #303469 is a reply to message #303405] Fri, 29 February 2008 08:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT E1.emp_id,E1.First_name,E1.Last_name
FROM EMP E1 
WHERE E1.department_id=500
 AND  EXISTS ( SELECT NULL FROM EMP E2
               WHERE E1.first_name=E2.first_name 
                AND  E2.department_id=110 
		AND  E2.Designation='Dev' 
		AND  E2.educated='Y');

[Updated on: Fri, 29 February 2008 08:14] by Moderator

Report message to a moderator

Re: Slow query [message #303581 is a reply to message #303469] Fri, 29 February 2008 21:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post Explain Plans for both queries with and without that predicate.

No a Bitmap index wont help. Read the Oracle Concepts manual for an explanation of when Bitmap Indexes should be used.

Ross Leishman
Previous Topic: SQL statement in the SGA
Next Topic: Sizing tablespaces
Goto Forum:
  


Current Time: Sun Dec 11 00:23:07 CST 2016

Total time taken to generate the page: 0.25555 seconds