5 ms·

If you're willing to pay Oracle, you can get a SQL database that runs queries using the same execution plan every time. I don't remember exactly how to do it, b

If you're willing to pay Oracle, you can get a SQL database that runs queries using the same execution plan every time. I don't remember exactly how to do it, but basically you run the query wrapped in a command that says "fix the execution plan of this query." The execution plan gets saved in some table keyed by the query text. Whenever Oracle sees that query, it commits itself to the same execution plan. This feature is like a decade old. A lot of the NoSQL arguments are really "No-MySQL" or "NoMoney" arguments. If you've never used Oracle (or DB2, or even SQL Server), you really don't have a full understanding of the capabilities and limitations of SQL databases. AFAICT, there's almost nothing in MySQL that wasn't old and boring in Oracle over a decade ago.

rythie 17y ago
Isn't that the same as doing a "STRAIGHT JOIN" with a "USE INDEX()" in MySQL?
- coffeemug 17y ago
  Well, USE INDEX is just a hint, and MySQL is free to ignore it. You probably mean FORCE INDEX, but I've still encountered situations where the index was ignored because the optimizer (incorrectly) thought there was no way to use it. (That was probably a bug, though)
  - deleted 17y ago
    [deleted]
- briansmith 17y ago
  The Oracle fixed execution plan feature fixes the entire execution plan, not just which indexes are used. Think about a query where there's ten (indexed) tables involved. Saying "use this index for this table, this index for that table, etc." isn't the same as saying "First, filter the third table down to the fields that match the criteria given for that table using index Foo, then filter the 7th table given its criteria using index Bar, then join them together using nested loops, then join them to the result of ...". Think about an Oracle execution plain as your declarative SQL query rewritten as a procedural program as you would write it in C/Java/Python/Ruby with hashtables, nested loops, if statements, etc.

rythie 17y ago

Isn't that the same as doing a "STRAIGHT JOIN" with a "USE INDEX()" in MySQL?

coffeemug 17y ago
Well, USE INDEX is just a hint, and MySQL is free to ignore it. You probably mean FORCE INDEX, but I've still encountered situations where the index was ignored because the optimizer (incorrectly) thought there was no way to use it. (That was probably a bug, though)
- deleted 17y ago
  [deleted]
briansmith 17y ago
The Oracle fixed execution plan feature fixes the entire execution plan, not just which indexes are used. Think about a query where there's ten (indexed) tables involved. Saying "use this index for this table, this index for that table, etc." isn't the same as saying "First, filter the third table down to the fields that match the criteria given for that table using index Foo, then filter the 7th table given its criteria using index Bar, then join them together using nested loops, then join them to the result of ...". Think about an Oracle execution plain as your declarative SQL query rewritten as a procedural program as you would write it in C/Java/Python/Ruby with hashtables, nested loops, if statements, etc.