O'Reilly logo

Oracle SQL Revealed: Executing Business Logic in the Database Engine by Alex Reprintsev

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

© Alex Reprintsev 2018
Alex ReprintsevOracle SQL Revealedhttps://doi.org/10.1007/978-1-4842-3372-6_2

2. Query Transformations

Alex Reprintsev
(1)
London, UK
 
The same logic can be implemented using various but semantically equivalent queries that look quite different but have the same plans and performance. This is achieved as a result of query transformations – original queries transform into the same final query.
For instance, the queries from Listing 2-1 have the same performance and plans. The last one has minor difference though – the join method is HASH JOIN ANTI NA while for all other queries it is HASH JOIN ANTI, so the result set for the last query will be empty if t2.id has null values.
select t1.* from t1 left join t2 on t1.id = t2.id where ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required