Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
How to re-write query for better performance.

Hi all,
This query seems not written optimally but I'm not able to quickly re-write it and little confused.
SELECT DISTINCT TB1.DMDUNIT, TB1.DMDGROUP, TB1.LOC
FROM BASECOST TB1
WHERE EXISTS
(SELECT 1
FROM BASECOST TB2
WHERE TB1.DMDUNIT = TB2.DMDUNIT
AND TB1.DMDGROUP = TB2.DMDGROUP
AND TB1.LOC = TB2.LOC
AND TB2.STARTDATE > TB1.STARTDATE
AND TB2.STARTDATE < (TB1.STARTDATE + (TB1.DUR / 1440))
ANDTB2.STARTDATE =
TB1.STARTDATE + (TB1.DUR / 1440))
OR (TB1.STARTDATE + (TB1.DUR / 1440) < SYSDATE
AND NOT EXISTS
(SELECT 1
FROM BASECOST TB2
WHERE TB1.DMDUNIT = TB2.DMDUNIT
AND TB1.DMDGROUP = TB2.DMDGROUP
AND TB1.LOC = TB2.LOC
AND TB2.STARTDATE =
TB1.STARTDATE + (TB1.DUR / 1440)));
Any inputs please without changing the functionality of query?
Answers
-
Hi,
is the query slow? What does explain plan display?