allthingsoracle.com allthingsoracle.com

Defragmentation Can Degrade Query Performance

As data in tables is updated and / or deleted, pockets of empty space can be created, leading to fragmented free space. This free space is wasted space as it is insufficient to hold new data. Moreover, it can impact performance of the database as a higher number of sparsely-populated blocks need to be visited. Oracle 11g introduced online segment shrink functionality to defragment and reclaim this space. During this operation, the...

Optimize MySQL Query Performance with Explicitly Named Fields

It’s common to see queries like these: SELECT (*) FROM airport; SELECT COUNT(*) FROM airport; These queries use the asterisk (*) wildcard for convenience. However, this convenience comes at a price: The * wildcard forces MySQL to read every field or record in the table, adding to the overall query processing time. To avoid this, explicitly name the output fields you wish to see in the result set, as...

Optimize MySQL Performance with Session Variables and Temporary Tables

Session-based server variables can also come in handy if you want to avoid nesting queries within each other. Therefore, while the following query will list all flights where the current price is above average: SELECT FlightID FROM stats   WHERE CurrPrice >   (SELECT AVG(CurrPrice) FROM stats); you can accomplish the same thing by splitting the task into two queries and using a server-side MySQL variable to connect...