CODEWITHSUNDEEP
sqloracle-databasesubqueryquery-optimization
Crash Bandicoot
Crash Bandicoot

Reputation: 61

Oracle SQL In Subquery taking too much time as compared to hard coded values

I have a simple query that is taking approx 2 minutes to run as compared to 8 seconds when executed with a hard coded value.

SELECT * 
FROM TABLE_A
WHERE TRANSACTION_DATE = (SELECT PREV_WORKING_DAY 
                          FROM TABLE_B )

SELECT PREV_WORKING_DAY FROM TABLE_B returns '20-JUN-2019'.

Both TRANSACTION_DATE & PREV_WORKING_DAY are of Date format.

When we hardcode date in the query output is 100 milli seconds

SELECT * 
FROM TABLE_A
WHERE TRANSACTION_DATE = '20-JUN-2019'

Total records = 82,000

Any idea what is causing the latency when using a subquery and how can we optimise same.

I know I can convert same in PL/SQL and assign the value to a variable but still I want to understand what is causing the latency. thanks

Upvotes: 0

Views: 731

Answers (2)

APC
APC

Reputation: 146219

My guess would be that you have no or very stale statistics for TABLE_B, so Oracle doesn't know it's only got one row. Consequently it chooses an inefficient plan. The Explain Plan will tell you the cardinality of the subquery.

If that is it the solution is: 

dbms_stats.gather_table_stats(user, 'TABLE_B');

You should read the documentation on gathering statistics.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

What happens if you switch to a JOIN?

SELECT A.*
FROM TABLE_A A JOIN
     TABLE_B B
     ON A.TRANSACTION_DATE B.PREV_WORKING_DAY;

Upvotes: 0

Related Questions