-3

We are wondering what the best way is to rewrite the following SQL so it can perform better in Oracle database.

As you see, the query was to filter from two tables (Period and Account) based on the data based on the keys. I believe this can be tweaked very well, may be replacing <> with != would give any benefits etc. 

SELECT 
    p.key, p.period 
FROM 
    Period p 
WHERE
    p.version = 0 
    AND p.balance <> 0 
    AND EXISTS (SELECT 1 
                FROM Period p2 
                WHERE p2.jointKey <> 0 
                  AND p.key = p2.jointKey 
                  AND p.period = p2.period 
                  AND EXISTS (SELECT 1 FROM Account a 
                              WHERE a.customerKey = :B1 AND a.key = p.jointKey) );
APC
  • 144,005
  • 19
  • 170
  • 281
user1595858
  • 3,700
  • 15
  • 66
  • 109
  • Define better, what performance are you getting? What performance do you desire? – Dale K Jul 16 '19 at 04:53
  • Please read [this excellent explanation](https://stackoverflow.com/a/34975420/146325) of how to ask Oracle tuning questions on this site. Not only will it show you the information we need to answer such questions it may give you the steer you need to investigate this for yourself. – APC Jul 16 '19 at 10:51

2 Answers2

0

One of the rewrites will be like following

SELECT p1.key, p1.period 
FROM Period p1 
join Period p2 on p1.key = p2.jointKey AND p1.period = p2.period and p2.jointKey <> 0
join Account a on a.key = p.jointKey
WHERE p.version = 0
and a.customerKey = :B1
Gro
  • 1,613
  • 1
  • 13
  • 19
0

Please verify index of the tables and query decide to part.

(SELECT 1 FROM Account a WHERE a.customerKey = :B1 AND a.key = p.jointKey)

(SELECT 1 FROM Period p2 WHERE p2.jointKey <> 0 AND p.key = p2.jointKey AND p.period = p2.period AND EXISTS  );


SELECT p.key, p.period FROM Period p WHERE p.version = 0 AND p.balance <> 0 AND EXISTS
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
Ashish Panchal
  • 1