CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Ashish
Ashish

Reputation: 33

The simple query taking longer

I am running a simple query and its taking very long time to execute the result:

Select MTL_CONTENT_LPN from reporting.DWH_FACT_INV_TRANSACTIONS  
where to_char(MTL_TRANSACTION_DATE,'yyyymm')=(select to_char(Max(SYSDATE),'YYYYMM') from dual)

Upvotes: 0

Views: 43

Answers (1)

Popeye
Popeye

Reputation: 35910

Why you are using sysdate with dual.

You need to use it in following way.

Select MTL_CONTENT_LPN from 
reporting.DWH_FACT_INV_TRANSACTIONS  
where MTL_TRANSACTION_DATE between trunc(sysdate, 'MONTH') AND add_month(trunc(sysdate, 'MONTH'),1) - 1

By using this where clause, any index on MTL_TRANSACTION_DATE will be used to increase the performance.

Cheers!!

Upvotes: 2

Related Questions