Oracle as a processing engine: query optimization
Published on: Author: Team Communication Category: OracleSenior Oracle Developer Patrick Barel specializes in optimizing the Oracle Database. For his client, Alliander, it took a query more than twenty seconds to produce the desired result. This caused a time-out on an important web application. Patrick was able to optimize the query. The result: the same desired result in less than a second.
Patrick explains: “The original query had ten tables; three of which produced the actual result. The other tables were merely needed as an information source for the result. Alliander did join the tables together. This produced a result set of several million records. Filters on the set ensured that only ten records remained.”
A new perspective
“As an outsider, I looked at the challenge with new eyes. I quickly concluded that the filters were actually only applied to three of the ten tables. Instead of using all the tables, I tied these three together and applied the filters. This gave me quick access to the final records. Just not yet with all the required data. I was still able to provide the required information by joining the remaining tables to the relatively small number of records.”
The result
The original query needed more than twenty seconds to achieve the desired result. Patrick: “Far too long for an important web application. Using query optimization, the query now arrives at the desired result in less than a second. As a result, the loading speed has been reduced to a minimum.”
More information
Curious about the possibilities of Oracle as a processing engine? Please contact sales@qualogy.com or 070 319 5000.
