Search code examples
sqloracleperformancequery-optimizationoracle12c

Rewrite SQL for best performance (oracle)


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) );

Solution

  • 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