Oracle SQL Performance issue - How to make a query stop running and return when finding the first instance

0
votes

I have a table in Oracle with lots of data of employees and dates (and many many more..) and i need to query it many times, since it's part of a big program.

the only thing that I'm looking for in this table is whether an employee at a specific date appears in the table or not, and I don't care how many times or any other data.

At the moment my query is:

select distinct(EMP_ID) from EMPLOYEES where TRUNC(DATE = TO_DATE('2020-11-21', 'yyyy-mm-dd') )AND EMP_ID = '123456789'

The thing is that the query Performs poorly - about 1.5 minutes for each query, and this is something that isn't tolerable, because it consumes server resources.

Is there a way to make the query stop the moment it finds that the employee does appear at a specific date and return something (without continue running)??

Thank you very much!!

1
sqloracle
I am not sure but try to use EXISTS as follows: SELECT 1 AS EMP_EXISTS FROM DUAL WHERE EXISTS ( SELECT 1 FROM EMPLOYEES WHERE DATE >= TO_DATE('2020-11-21', 'yyyy-mm-dd') AND DATE < TO_DATE('2020-11-22', 'yyyy-mm-dd') AND EMP_ID = '123456789' ) and yes, Index on DATE and EMP_ID column will be quite useful here. - Popeye
You were write - Adding an index did help! thank you!! - Gilad_Tz

1 Answers

0
votes

You can filter on the pseudo column rownum so that it doesn’t search for every row that matches your filters: where rownum=1.

But for this query, it looks like you probably want an index on empid, and you want to make sure you’re using the correct data types in your query (is it really a string?). Is your date filter correct?