How to use EXPLAIN PLAN. As a contractor, I was once asked to improve the performance of a query that had a main table with many millions of rows. EXPLAIN PLAN

How to use EXPLAIN PLAN. As a contractor, I was once asked to improve the performance of a query that had a main table with many millions of rows. EXPLAIN PLAN revealed that the query planner was making some bad choices, despite the table statistics being up-to-date. I bullied[0] the optimiser into making some better choices, and got a 100x performance improvement. For large databases, the query plan can make the difference between a query taking a second and taking an hour. Unfortunately the format of query-plan output from EXPLAIN PLAN is unstandardised and vendor-dependent. [0] "bullied": This was Oracle; you could use "hints" to force the optimiser to change it's behaviour. If you don't have hints, then sometimes transforming a JOIN query into a query using an inner SELECT will expand the optimiser's consciousness. It all gets a bit voodoo, if the optimiser is stubborn and you don't have hints.