| Slow query [message #303405] |
Fri, 29 February 2008 03:44  |
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  |
 |
BlackSwan
Messages: 26766
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
